附注:微软认证考试70-461范围
- Create Database Objects创建数据库对象 (24%)
- Work with Data数据处理 (27%)
- Modify Data数据修改 (24%)
- Troubleshoot & Optimize故障排解及SQL优化
(25%)
本文是第二节Work with Data 数据处理 。
第一部分直通车
第二部分直通车
第三部分直通车
第四部分直通车
第五部分:Query and manage XML data. May include but not limited to: understand xml datatypes and their schemas and interop w/, limitations & restrictions; implement XML schemas and handling of XML data; XML data: how to handle it in SQL Server
and when and when not to use it, including XML namespaces; import and export xml; xml indexing. XML数据的查询与管理。可能包含但不仅限于:理解XML类型,架构,交互,局限性和约束性;实现XML架构和XML数据的处理;了解什么时候使用XML比较恰当,包括XML命名空间;导入导出XML;XML索引。
FOR XML指定RAW,AUTO
FOR XML 模式可以是 RAW、AUTO、EXPLICIT 或 PATH。它确定产生的 XML 的形状。基本语法:
[ FOR { BROWSE | <XML> } ]
<XML> ::=
XML
{
{ RAW [ ('ElementName') ] | AUTO }
[
<CommonDirectives>
[ , { XMLDATA | XMLSCHEMA [ ('TargetNameSpaceURI') ]} ]
[ , ELEMENTS [ XSINIL | ABSENT ]
]
| EXPLICIT
[
<CommonDirectives>
[ , XMLDATA ]
]
| PATH [ ('ElementName') ]
[
<CommonDirectives>
[ , ELEMENTS [ XSINIL | ABSENT ] ]
]
}
<CommonDirectives> ::=
[ , BINARY BASE64 ]
[ , TYPE ]
[ , ROOT [ ('RootName') ] ]
FOR XML 指定RAW
with TestXml
as
(
select 1 as id,'乐可乐可的部落格' as name
union all
select 2,'微软认证考试70-461'
union all
select 3 ,'FOR XML'
)
select id,name from testxml for xml raw,type
结果:
<row id="1" name="乐可乐可的部落格" />
<row id="2" name="微软认证考试70-461" />
<row id="3" name="FOR XML" />
红色字体type可选,不会影响结果,只是影响数据类型。
指定 ELEMENTS:
with TestXml
as
(
select 1 as id,N'乐可乐可的部落格' as name
union all
select 2,N'微软认证考试70-461'
union all
select 3 ,null
)
select id,name from testxml for xml raw,elements
注意,第三行值改为NULL值进行测试。
结果:
<row>
<id>1</id>
<name>乐可乐可的部落格</name>
</row>
<row>
<id>2</id>
<name>微软认证考试70-461</name>
</row>
<row>
<id>3</id>
</row>
元素name在第三行没有出现,因为是NULL值。用XSINIL生成NULL值的name元素。
with TestXml
as
(
select 1 as id,N'乐可乐可的部落格' as name
union all
select 2,N'微软认证考试70-461'
union all
select 3 ,null
)
select id,name from testxml for xml raw,elements XSINIL
结果:
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<id>1</id>
<name>乐可乐可的部落格</name>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<id>2</id>
<name>微软认证考试70-461</name>
</row>
<row xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<id>3</id>
<name xsi:nil="true" />
</row>
使用XMLDATA和XMLSCHEMA
XMLDATA返回描述文档结构的 XML-DATA 架构
with TestXml
as
(
select 1 as id,N'乐可乐可的部落格' as name
union all
select 2,N'微软认证考试70-461'
union all
select 3 ,null
)
select id,name from testxml for xml raw,XMLDATA
结果:
<Schema name="Schema2" xmlns="urn:schemas-microsoft-com:xml-data" xmlns:dt="urn:schemas-microsoft-com:datatypes">
<ElementType name="row" content="empty" model="closed">
<AttributeType name="id" dt:type="i4" />
<AttributeType name="name" dt:type="string" />
<attribute type="id" />
<attribute type="name" />
</ElementType>
</Schema>
<row xmlns="x-schema:#Schema2" id="1" name="乐可乐可的部落格" />
<row xmlns="x-schema:#Schema2" id="2" name="微软认证考试70-461" />
<row xmlns="x-schema:#Schema2" id="3" />
通过指定 XMLSCHEMA 选项,您可以针对结果请求 XSD 架构:
with TestXml
as
(
select 1 as id,N'乐可乐可的部落格' as name
union all
select 2,N'微软认证考试70-461'
union all
select 3 ,null
)
select id,name from testxml for xml raw,XMLSCHEMA
结果:
<xsd:schema targetNamespace="urn:schemas-microsoft-com:sql:SqlRowSet2" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="row">
<xsd:complexType>
<xsd:attribute name="id" type="sqltypes:int" use="required" />
<xsd:attribute name="name">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="2052" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
<xsd:maxLength value="12" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" id="1" name="乐可乐可的部落格" />
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" id="2" name="微软认证考试70-461" />
<row xmlns="urn:schemas-microsoft-com:sql:SqlRowSet2" id="3" />
您可以将目标命名空间 URI 指定为 FOR XML 中 XMLSCHEMA 的可选参数。
with TestXml
as
(
select 1 as id,N'乐可乐可的部落格' as name
union all
select 2,N'微软认证考试70-461'
union all
select 3 ,null
)
select id,name from testxml for xml raw,XMLSCHEMA ('urn:http://blog.csdn.net/lihui_830501')
结果:
<xsd:schema targetNamespace="urn:http://blog.csdn.net/lihui_830501" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes" elementFormDefault="qualified">
<xsd:import namespace="http://schemas.microsoft.com/sqlserver/2004/sqltypes" schemaLocation="http://schemas.microsoft.com/sqlserver/2004/sqltypes/sqltypes.xsd" />
<xsd:element name="row">
<xsd:complexType>
<xsd:attribute name="id" type="sqltypes:int" use="required" />
<xsd:attribute name="name">
<xsd:simpleType>
<xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="2052" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth">
<xsd:maxLength value="12" />
</xsd:restriction>
</xsd:simpleType>
</xsd:attribute>
</xsd:complexType>
</xsd:element>
</xsd:schema>
<row xmlns="urn:http://blog.csdn.net/lihui_830501" id="1" name="乐可乐可的部落格" />
<row xmlns="urn:http://blog.csdn.net/lihui_830501" id="2" name="微软认证考试70-461" />
<row xmlns="urn:http://blog.csdn.net/lihui_830501" id="3" />
检索二进制数据
像XMLDATA一样,在SQL中指定BINARY BASE64。
重命名 <row> 元素
with TestXml
as
(
select 1 as id,N'乐可乐可的部落格' as name
union all
select 2,N'微软认证考试70-461'
union all
select 3 ,null
)
select id,name from testxml for xml raw ('myrow')
结果:
<myrow id="1" name="乐可乐可的部落格" />
<myrow id="2" name="微软认证考试70-461" />
<myrow id="3" />
指定ELEMENTS的情况类同。
为 FOR XML 生成的 XML 指定根元素
with TestXml
as
(
select 1 as id,N'乐可乐可的部落格' as name
union all
select 2,N'微软认证考试70-461'
union all
select 3 ,null
)
select id,name from testxml for xml raw,root('myroot')
结果:
<myroot>
<row id="1" name="乐可乐可的部落格" />
<row id="2" name="微软认证考试70-461" />
<row id="3" />
</myroot>
查询 XML 类型的列
declare @xml table(xid int,xname varchar(50),xmlcol xml);
insert into @xml select 1,'第一行','<myroot>
<row id="1" name="乐可乐可的部落格" />
<row id="2" name="微软认证考试70-461" />
<row id="3" />
</myroot>'
insert into @xml select 2,'第二行','<myroot>
<row id="4" name="XML数据" />
<row id="5" name="微软考试" />
</myroot>'
select xid,xname,xmlcol.query('/myroot') from @xml for xml raw
结果:
<row xid="1" xname="第一行">
<myroot>
<row id="1" name="乐可乐可的部落格" />
<row id="2" name="微软认证考试70-461" />
<row id="3" />
</myroot>
</row>
<row xid="2" xname="第二行">
<myroot>
<row id="4" name="XML数据" />
<row id="5" name="微软考试" />
</myroot>
</row>
FOR XML 指定AUTO
with TestXml
as
(
select 1 as id,N'乐可乐可的部落格' as name
union all
select 2,N'微软认证考试70-461'
union all
select 3 ,null
)
select id,name from testxml for xml auto
结果:
<testxml id="1" name="乐可乐可的部落格" />
<testxml id="2" name="微软认证考试70-461" />
<testxml id="3" />
用表名做元素名称,即替代RAW模式中的“row”。
下面看多表的查询
with [order]
as
(
select 122 as orderid, 1 as productid,10 as quantity
union all
select 123,1 as productid,100 as quantity
union all
select 124,2,20
union all
select 125,3 ,5
),
product
as
(
select 1 as id,N'乐可乐可的部落格' as name
union all
select 2,N'微软认证考试70-461'
)
select * from product,[order] where [order].productid=product.id for xml auto
结果:
<product id="1" name="乐可乐可的部落格">
<order orderid="122" productid="1" quantity="10" />
<order orderid="123" productid="1" quantity="100" />
</product>
<product id="2" name="微软认证考试70-461">
<order orderid="124" productid="2" quantity="20" />
</product>
粗体部分顺序敏感。如果把product和order换一下位置,结果:
<order orderid="122" productid="1" quantity="10">
<product id="1" name="乐可乐可的部落格" />
</order>
<order orderid="123" productid="1" quantity="100">
<product id="1" name="乐可乐可的部落格" />
</order>
<order orderid="124" productid="2" quantity="20">
<product id="2" name="微软认证考试70-461" />
</order>
当然,AUTO模式同样也可以指定ELEMENTS,BINARY BASE64,同RAW。
返回的 XML 成形过程中的 AUTO 模式试探方法
AUTO 模式根据查询决定返回的 XML 的形式。 在决定嵌套元素的方式时,AUTO 模式试探方法会比较相邻行中的列值。ntext、text、image 和xml
类型以外的所有类型的列都会进行比较。 (n)varchar(max) 和
varbinary(max) 类型的列也会进行比较。
上面的第一个指定AUTO的SQL语句结果集为:
id name orderid productid quantity
1 乐可乐可的部落格 122 1 10
1 乐可乐可的部落格 123 1 100
2 微软认证考试70-461 124 2 20
AUTO 模式试探方法将比较表 product 的所有值(Id 列和 Name 列)。因为前两行的 Id 列和 Name 列具有相同的值,所以向结果中添加了一个具有两个 <order> 子元素的 <product> 元素。
<product id="1" name="乐可乐可的部落格">
<order orderid="122" productid="1" quantity="10" />
<order orderid="123" productid="1" quantity="100" />
</product>
<product id="2" name="微软认证考试70-461">
<order orderid="124" productid="2" quantity="20" />
</product>
如果把Name 列改为 text 类型。
AUTO 模式试探方法不比较此类型的值, 而是认为这些值不相同。
declare @order table(orderid int,productid int,quantity int)
declare @product table(id int,name text)
insert into @order
select 122 as orderid, 1 as productid,10 as quantity
union all
select 123,1 as productid,100 as quantity
union all
select 124,2,20
union all
select 125,3 ,5
insert into @product
select 1 ,N'乐可乐可的部落格'
union all
select 2,N'微软认证考试70-461'
select * from @product as product,@order as [order] where [order].productid=product.id for xml auto
结果:
<product id="1" name="乐可乐可的部落格">
<order orderid="122" productid="1" quantity="10" />
</product>
<product id="1" name="乐可乐可的部落格">
<order orderid="123" productid="1" quantity="100" />
</product>
<product id="2" name="微软认证考试70-461">
<order orderid="124" productid="2" quantity="20" />
</product>
再看第一个指定AUTO的SQL语句,但是更改了orderid:
with [order]
as
(
select 122 as orderid, 1 as productid,10 as quantity
union all
select 125,1 as productid,100 as quantity
union all
select 123,2,20
union all
select 124,3 ,5
),
product
as
(
select 1 as id,N'乐可乐可的部落格' as name
union all
select 2,N'微软认证考试70-461'
)
select * from product,[order] where [order].productid=product.id
order by orderid
结果:
id name orderid productid quantity
1 乐可乐可的部落格 122 1 10
2 微软认证考试70-461 123 2 20
1 乐可乐可的部落格 125 1 100
这样相同id和name的product没有连在一起,AUTO模式试探将生成以下结果:
<product id="1" name="乐可乐可的部落格">
<order orderid="122" productid="1" quantity="10" />
</product>
<product id="2" name="微软认证考试70-461">
<order orderid="123" productid="2" quantity="20" />
</product>
<product id="1" name="乐可乐可的部落格">
<order orderid="125" productid="1" quantity="100" />
</product>
参考:http://msdn.microsoft.com/zh-cn/library/ms178107.aspx
第5.1部分
第5.3部分
第5.4部分
第5.5部分
分享到:
相关推荐
Barrier-free work with ARIS Publisher Exports
data science for business
Barrier-free work with ARIS Publisher Exports Version 9.8 – Service Release 5
Python: End-to-end Data Analysis by Phuong Vothihong English | 31 May 2017 | ASIN: B072M6868D | 1321 Pages | AZW3 | 27.07 MB Leverage the power of Python to clean, scrape, analyze, and visualize your...
Lightning Web组件和Salesforce数据:使用Lightning Data Service处理数据contactCreator.html: contactCreator.js: 从“ lwc”导入{LightningElement}; 从“ lightning / platformShowToastEvent”导入{...
Persist data for your web application with Active Record Work with forms Apply a test-driven development approach to your Rails-based web applications Discover many Rails secrets and tips
Energy-efficient work-stealing language runtimes 2014 asplos
You will find out how to work with the App Builder and Page Designer, use APEX themes (responsive and mobile included), templates and wizards, and design and deploy custom web apps. New and updated ...
R is one of the most popular, powerful data analytics languages and environments in use by data scientists. Actionable business data is often stored in Relational Database Management Systems (RDBMS), ...
This book, Data Manipulation with R, is aimed at giving intermediate-to-advanced level users of R (who have knowledge about datasets) an opportunity to use state-of-the-art approaches in data ...
High-dimensional data can be converted to low-dimensional codes by training a multilayer neural network with a small central layer to reconstruct high-dimensional input vectors. Gradient descent can ...
Laravel开发-laravel-teamwork 用于团队项目管理API的PHP包装器
70-642, 70-643, and MCITP 70-646, 70-647 . I’d love to say that this book was my idea, but the real credit goes to Scott Empson who originally developed the vision of this book with Cisco ...
Image processing Home work with matlab code inside pdf with detail explanation
The purpose of 2017 International Conference on Security with Intelligent Computing and Big-data Services (SICBS’17 for short) with joined workshops, Workshop on Information and Communication ...
sonatype-work\nexus.rar sonatype-work\nexus.rar sonatype-work\nexus.rar sonatype-work\nexus.rar
Data Access - Retreive and Process data with a SQL Data Reader Data Access - Sort and Filter with a DataView Data Access - Use ADO 2.6 Data Access - Use Stored Procedures Data Access - Using Typed ...
Now, if you look for the last trendy technologies (Big Data, NoSQL or JavaScript), you’ll find more in-depth articles explaining how they work. Are relational databases too old and too boring to be ...
Have a custom API that you aren't sure how to use with Ember Data? Interested in writing your own adapter or serializer? Want to just know more about how Ember Data works? This is the Ember Data book ...
Designing with Data: Improving the User Experience with A/B Testing by Rochelle King English | 29 Mar. 2017 | ASIN: B06XY9TTN8 | 370 Pages | AZW3 | 4.61 MB On the surface, design practices and data ...