首页 > 代码库 > Querying Microsoft SQL Server 2012 读书笔记:查询和管理XML数据 1 -使用FOR XML返回XML结果集
Querying Microsoft SQL Server 2012 读书笔记:查询和管理XML数据 1 -使用FOR XML返回XML结果集
XML 介绍
<CustomersOrders> <Customer custid="1" companyname="Customer NRZBB"> <Order orderid="10692" orderdate="2007-10-03T00:00:00" /> <Order orderid="10702" orderdate="2007-10-13T00:00:00" /> <Order orderid="10952" orderdate="2008-03-16T00:00:00" /> </Customer> <Customer custid="2" companyname="Customer MLTDN"> <Order orderid="10308" orderdate="2006-09-18T00:00:00" /> <Order orderid="10926" orderdate="2008-03-04T00:00:00" /> </Customer></CustomersOrders>
正如你所看到的, XML用标签(tags)来命名XML文档的部件(parts). 这些部件称为元素(elements). 每个元素都有起始标签比如 <Customer>, 同时需要有相对应的结束标签,比如上面的 </Customer>. 如果一个元素没有内嵌元素,那么钙元素可以简写成一个标签来表示一个元素的开始和结束,比如上面的 <Order … />.元素可以潜逃,但是标签不能交叉,父元素的结束标签必须在最后一个嵌套元素的后面. 如果每个起始标签都有相对应的结束标签,并且标签嵌套,那么这个XML的文档结构良好.
XML文档是有序的. 这不是说他们以某个元素值排序,而是指元素的位置. 比如上面例子中 orderid等于10702 在第一个Customer元素的下面.
XML 是大小写敏感的Unicode文本.另外有些字符在XML文档里面表示标记,有特别的含义. 比如 < . 如果你想在XML文档里面用这些字符,必须用&+特殊字符+; 来转义. 见下表.
此外你也可以指定XML CDATA 段, 书写格式为 <![CDATA[...]]>. 你可以把中间的省略号用任何不包含”]]>”字符串的字符代替,这样就不会被XML当成标记了.
处理指令是用来指示应用程序处理XML的, 书写格式和元素类似,字符在小于号和大于号之间,并且起尾有问好,比如 <?PItarget data?>. 当引擎处理XML文档的时候就会接收到这些指令.
除了元素和处理指令之外,XML还包含备注标记, 格式为 <!-- This is a comment –>.
最后,XML文档的开始部分有XML版本和编码的标注,如 <?xml version="1.0" encoding="ISO-8859-15"?>.
除了XML文档(XML documents), 还有XML片段(XML fragments). 唯一的区别就是 XML文档有个单一根节点 之前例子中的 <CustomersOrders> .如果你把这个删掉那么就是XML片段了.
<Customer custid="1" companyname="Customer NRZBB"> <Order orderid="10692" orderdate="2007-10-03T00:00:00" /> <Order orderid="10702" orderdate="2007-10-13T00:00:00" /> <Order orderid="10952" orderdate="2008-03-16T00:00:00" /></Customer><Customer custid="1" companyname="Customer NRZBB"> <Order orderid="10692" orderdate="2007-10-03T00:00:00" /> <Order orderid="10702" orderdate="2007-10-13T00:00:00" /> <Order orderid="10952" orderdate="2008-03-16T00:00:00" /></Customer>
如果你把第二个customer元素删掉的话.上面这段就变成XML文档了.因为只有一个customer,又是单一根节点了.
正如你前面看到的,元素可以拥有属性(attributes). 属性有自己的名字和用双引号包裹的值. 这个是以属性为中心的(attribute-centric)
表示形式. 当然你也可以写不同的XML,每个属性可以作为一个嵌套的元素; 这个成为以元素为中心的(element-centric)表示形式.
另外,元素的名字不需要唯一, 因为他们可以通过位置来引用; 当然你也可以通过命名空间来区分不同地区,部门或公司的元素. 你可以在XML文档的根元素里面申明命名空间. 你也可以为每个命名空间指定一个别名. 命名空间的别名作为元素的前缀. 下面是一个以使用命名空间,并元素为中心的XML.数据和前一节的示例是一样的.
<CustomersOrders xmlns:co="TK461-CustomersOrders"> <co:Customer> <co:custid>1</co:custid> <co:companyname>Customer NRZBB</co:companyname> <co:Order> <co:orderid>10692</co:orderid> <co:orderdate>2007-10-03T00:00:00</co:orderdate> </co:Order> <co:Order> <co:orderid>10702</co:orderid> <co:orderdate>2007-10-13T00:00:00</co:orderdate> </co:Order> <co:Order> <co:orderid>10952</co:orderid> <co:orderdate>2008-03-16T00:00:00</co:orderdate> </co:Order> </co:Customer> <co:Customer> <co:custid>2</co:custid> <co:companyname>Customer MLTDN</co:companyname> <co:Order> <co:orderid>10308</co:orderid> <co:orderdate>2006-09-18T00:00:00</co:orderdate> </co:Order> <co:Order> <co:orderid>10926</co:orderid> <co:orderdate>2008-03-04T00:00:00</co:orderdate> </co:Order> </co:Customer></CustomersOrders>
XML非常灵活.至今为止你所看到的只是一小部分的建立格式良好的XML文档的规则, 在一个XML文档中, 实际数据(actual data )与元数据(metadata) 混合, 比如元素名和属性名. 由于XML是文本,因此在不同系统平台进行数据交换非常方便. 然而,交换数据最重要的就是保证元数据的固定性. 如果你要导入先前例子中的用户订单,每几分钟执行一次导入. 想象一下,如果每次导入的时候元数据都变了,处理的时候会有多痛苦. 举例来说, Customer 元素的名字变成了 Client, 然后Order 元素的名字变成了 Purchase. 或者想象一下 orderdate 属性(或元素) 突然把它的数据类型从timestamp改为integer. 你需要把导入的XML文档模式固定化.
许多不同的标准定制了XML文档元数据描述.当前,使用最广泛的元数据描述是 XSD ( XML Schema Description)
文档. XSD 文档用来描述XML文档的元数据 . 一个XSD文档的模式是预定义的 .通过XSD 标准, 你可以指定元素名,数据类型,还有元素的数量,限制等.下面是一个使用XSD 模式描述的元素中心的用户和用户订单的例子.
<xsd:schema targetNamespace="TK461-CustomersOrders" xmlns:schema="TK461-CustomersOrders" 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="Customer"> <xsd:complexType> <xsd:sequence> <xsd:element name="custid" type="sqltypes:int" /> <xsd:element name="companyname"> <xsd:simpleType> <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="1033" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth" sqltypes:sqlSortId="52"> <xsd:maxLength value="40" /> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element ref="schema:Order" minOccurs="0" maxOccurs="unbounded" /> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:element name="Order"> <xsd:complexType> <xsd:sequence> <xsd:element name="orderid" type="sqltypes:int" /> <xsd:element name="orderdate" type="sqltypes:datetime" /> </xsd:sequence> </xsd:complexType> </xsd:element></xsd:schema>
When you check whether an XML document complies with a schema, you validate the
document. A document with a predefined schema is said to be a typed XML document. (这段不知道怎么翻译,直翻连不起来.)
从关系数据生成XML
通过T-SQL 选择语句,你可以生成本课程中所有展示的XML. 这个章节教你怎么通过 FOR XML 子句把一个查询结果转换为XML .你会学到大多有用的选项和指令. 更详细的内容可以查看 SQL Server 2012 Books Online上的文章 “FOR XML (SQL Server)” http://msdn.microsoft.com/en-us/library/ms178107.aspx.
FOR XML RAW
先来学一下 RAW 选项. XML的建立很接近关系数据的平面化表示 (The XML created is
quite close to the relational (tabular) presentation of the data) . 在 RAW 模式中, 记录集的( rowsets )的每一行转化为单个元素命名行,列被转为这个元素的属性. 下面是个FOR XML RAW 选项的XML文档例子
<row custid="1" companyname="Customer NRZBB" orderid="10692" orderdate="2007-10-03T00:00:00" /><row custid="1" companyname="Customer NRZBB" orderid="10702" orderdate="2007-10-13T00:00:00" /><row custid="1" companyname="Customer NRZBB" orderid="10952" orderdate="2008-03-16T00:00:00" /><row custid="2" companyname="Customer MLTDN" orderid="10308" orderdate="2006-09-18T00:00:00" /><row custid="2" companyname="Customer MLTDN" orderid="10926" orderdate="2008-03-04T00:00:00" />
你也可以在RAW 模式中重命名行元素,增加根元素,命名空间或者使XML返回元素为中心(element-centric). 下面是一个增强版的列子.
<CustomersOrders> <Order custid="1" companyname="Customer NRZBB" orderid="10692" orderdate="2007-10-03T00:00:00" /> <Order custid="1" companyname="Customer NRZBB" orderid="10702" orderdate="2007-10-13T00:00:00" /> <Order custid="1" companyname="Customer NRZBB" orderid="10952" orderdate="2008-03-16T00:00:00" /> <Order custid="2" companyname="Customer MLTDN" orderid="10308" orderdate="2006-09-18T00:00:00" /> <Order custid="2" companyname="Customer MLTDN" orderid="10926" orderdate="2008-03-04T00:00:00" /></CustomersOrders>
正如你所看到的,这个文档,看上去更像XML了. 然而,那它还不包括附加的嵌套层次. 例子中的 customer 的属性 custid 等于1的值重复了三次
, 如果采用嵌套元素,每个订单只出现一次,那看上去就更好了. 你可以使用 FOR XML AUTO 选项实现 .
FOR XML AUTO
FOR XML AUTO 选项可以生成一个嵌套元素的XML文档, 并且使用起来也不复杂. 在 AUTO 和RAW 模式中, 你可以使用关键字ELEMENTS来生成 element-centric XML再使用 WITH NAMESPACES 子句, 在 SELECT 部分之前定义好命名空间和别名 . 至今为止, 你看到的都是 XML 结果 .下面是一个直观的使用SELECT 语句查询然后通过FOR XML 来生成XML的例子.
WITH XMLNAMESPACES(‘TK461-CustomersOrders‘ AS co)SELECT [co:Customer].custid AS [co:custid],[co:Customer].companyname AS [co:companyname],[co:Order].orderid AS [co:orderid],[co:Order].orderdate AS [co:orderdate]FROM Sales.Customers AS [co:Customer]INNER JOIN Sales.Orders AS [co:Order]ON [co:Customer].custid = [co:Order].custidWHERE [co:Customer].custid <= 2AND [co:Order].orderid %2 = 0ORDER BY [co:Customer].custid, [co:Order].orderidFOR XML AUTO, ELEMENTS, ROOT(‘CustomersOrders‘);
其中 T-SQL 表格 and 和列别名用来生成元素名.表格别名的前缀作为命名空间. 其中冒号用来分割命名空间和元素名. WHERE 限制输出两个用户, 并且输出偶数订单. 该XML输出具有良好的以元素中心的XML文档格式.
<CustomersOrders xmlns:co="TK461-CustomersOrders"> <co:Customer> <co:custid>1</co:custid> <co:companyname>Customer NRZBB</co:companyname> <co:Order> <co:orderid>10692</co:orderid> <co:orderdate>2007-10-03T00:00:00</co:orderdate> </co:Order> <co:Order> <co:orderid>10702</co:orderid> <co:orderdate>2007-10-13T00:00:00</co:orderdate> </co:Order> <co:Order> <co:orderid>10952</co:orderid> <co:orderdate>2008-03-16T00:00:00</co:orderdate> </co:Order> </co:Customer> <co:Customer> <co:custid>2</co:custid> <co:companyname>Customer MLTDN</co:companyname> <co:Order> <co:orderid>10308</co:orderid> <co:orderdate>2006-09-18T00:00:00</co:orderdate> </co:Order> <co:Order> <co:orderid>10926</co:orderid> <co:orderdate>2008-03-04T00:00:00</co:orderdate> </co:Order> </co:Customer></CustomersOrders>
注意ORDER BY 子句非常重要 , 使用 SELECT 语句实际上你是在格式化返回的XML结果 . 如果没有ORDER BY子句,返回的订单行就不确定.可能会的到一个多次重复嵌套元素的怪异XML文档 . (额其实我很希望有个例子…..我去掉以后结果是一样的= = )
注: FOR XML 处理顺序在ORDER BY 子句之后.
除了 ORDER BY 子句很重要以外, 列的顺序也影响XML返回结果 . SQL Server 使用列顺序来决定元素的嵌套 . 列的的顺序应该遵守一对多的关系. 一个客户有许多订单;因此,你查询的时候应该让客户列在订单列的前面.
需要注意列的顺序,可能使你觉得很麻烦 ,相对的,行与列的顺序就不重要了, 然而你要知道 ,查询输出以后并没有’关系’,只是XML格式的文本 , 查询部分只是格式化这些文本.
在RAW and AUTO 中, 你同样可以返回你创建的文档的 XSD schema . 返回的schema 在XML数据之前 ;这称为内联的schema. 返回 XSD 可以使用XMLSCHEMA 指令. 这个指令接收一个变量来定义命名空间. 如果你只需要schema ,不要数据, 直接在WHERE 条件里面打写谓语使得没有行返回即可. 下面是前面查询语句的schema返回结果.
SELECT [Customer].custid AS [custid],[Customer].companyname AS [companyname],[Order].orderid AS [orderid],[Order].orderdate AS [orderdate]FROM Sales.Customers AS [Customer]INNER JOIN Sales.Orders AS [Order]ON [Customer].custid = [Order].custidWHERE 1 = 2FOR XML AUTO, ELEMENTS,XMLSCHEMA(‘TK461-CustomersOrders‘);
下面是返回结果 ,XSD 文档
<xsd:schema targetNamespace="TK461-CustomersOrders" xmlns:schema="TK461-CustomersOrders" 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="Customer"> <xsd:complexType> <xsd:sequence> <xsd:element name="custid" type="sqltypes:int" /> <xsd:element name="companyname"> <xsd:simpleType> <xsd:restriction base="sqltypes:nvarchar" sqltypes:localeId="2052" sqltypes:sqlCompareOptions="IgnoreCase IgnoreKanaType IgnoreWidth"> <xsd:maxLength value="40" /> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element ref="schema:Order" minOccurs="0" maxOccurs="unbounded" /> </xsd:sequence> </xsd:complexType> </xsd:element> <xsd:element name="Order"> <xsd:complexType> <xsd:sequence> <xsd:element name="orderid" type="sqltypes:int" /> <xsd:element name="orderdate" type="sqltypes:datetime" /> </xsd:sequence> </xsd:complexType> </xsd:element></xsd:schema>
FOR XML PATH
再来说最后两个子句 EXPLICIT 和 PATH 选项—通过这两个选项,你可以完全控制XML文档返回的内容. EXPLICIT 模式只是向后兼容而存在的 ,这个是之前语法. PATH 模式使用标准的 XML XPath 表达式定义元素和属性. 这个章节主要讲 PATH 模式;如果你想学习更多的EXPLICIT 细节,请参考联机手册里面SQL Server 2012 相关的文章 “Use EXPLICIT Mode with FOR XML” http://msdn.microsoft.com/en-us/library/ms189068.aspx.
在PATH 模式中, 列名和别名充当 XPath 表达式. XPath 表达式定义生成XML的元素路径,路径分层次表示.层级使用斜杠表示(/) 默认情况下没列都是元素,如果你希望生成属性为中心的XML,需要在别名前面加”at”(@)符号. 下面是个简单的XPATH查询
SELECT Customer.custid AS [@custid],Customer.companyname AS [companyname]FROM Sales.Customers AS CustomerWHERE Customer.custid <= 2ORDER BY Customer.custidFOR XML PATH (‘Customer‘), ROOT(‘Customers‘);
下面是输出结果
<Customers> <Customer custid="1"> <companyname>Customer NRZBB</companyname> </Customer> <Customer custid="2"> <companyname>Customer MLTDN</companyname> </Customer></Customers>
如果你希望在XML中创建嵌套子表元素, 在PATH模式中你需要在SELECT语句里用子查询. 子查询必须返回一个标量值 .然而,你知道父行可以有多个子行 ;一个用户可以有多个订单. 你通过子查询返回标量值然后返回XML. 然后返回的结果作为XML的一个标量值. 你通过FOR XML 语句格式化从子查询获得的嵌套XML , 就像你用外部查询格式化XML .此外,你必须使用FOR XML子句的 TYPE指令生成XML数据类型的值, 而不是外部查询无法处理的XML文本.
XML转表格
我们刚学了怎么从关系数据创建XML. 我们也可以把XML转换为表.把XML转为关系表格称为分解XML(shredding XML). 你可以用XML数据类型的节点方法做这个,你会在第三课中学到 “Using the XML Data Type.” 自SQL Server 2000开始你也可以通过OPENXML 行集功能实现.
OPENXML函数为内存中的XML文档提供了一个通过DOM表示的行集(Document Object Model ).在解析DOM之前, 你先要做一些准备. 准备XML的 DOM,你需要调用系统存储过程 sys.sp_xml_preparedocument.在分析文档之后,你必须使用存储过程sys.sp_xml_removedocument 移除DOM .
OPENXML 函数使用一下参数:
■ XML DOM 处理, 通过 sp_xml_preparedocument返回
■ XPath 表达式查找节点映射到行集返回的行.
■ 返回行集的描述
■ 映射XML 节点与 行集的列
文档句柄是一个整数,这个是最简单的参数.XPath 表达式指定rowpattern(用定义怎么样把XML节点转为行) . 路径的节点用作模式 ,节点下面选中的节点定义返回的行集.
你可以通过WITH子句的 OPENXML功能将XML 元素或属性映射到行或列.在这个子句中, 你可以指定一个存在的表格,用作行集返回的模板, 也可以用类似 CREATE TABLE的 SQL语法创建表格 .
OPENXML 功能第三个参数是可选的, 称为flags, 它允许你指定XML数据和关系数据值之间的映射 . 1 表示以属性为中心的映射,2表示袁术中心,3表示两者皆可,不过 值3不是XML 文档,最好不要使用. 值 8 可以联合 值1和2的安位或逻辑运算活的属性为中心和元素为中心的映射. 下面我们来举一个例子.例如 custid 属性 companyname 是元素. 这个稍显复杂的XML来向你介绍属性中心(attribute-centric) 和 元素中心(element-centric)映射的区别. 下面三段代码用了同一个XML来操作. 不同的映射使用 flags 参数:1, 2, 还有11 (8+1+2); 三个查询军用同样的行集描述 .
DECLARE @DocHandle AS INT;DECLARE @XmlDocument AS NVARCHAR(1000);SET @XmlDocument = N‘ <CustomersOrders> <Customer custid="1"> <companyname>Customer NRZBB</companyname> <Order orderid="10692"> <orderdate>2007-10-03T00:00:00</orderdate> </Order> <Order orderid="10702"> <orderdate>2007-10-13T00:00:00</orderdate> </Order> <Order orderid="10952"> <orderdate>2008-03-16T00:00:00</orderdate> </Order> </Customer> <Customer custid="2"> <companyname>Customer MLTDN</companyname> <Order orderid="10308"> <orderdate>2006-09-18T00:00:00</orderdate> </Order> <Order orderid="10926"> <orderdate>2008-03-04T00:00:00</orderdate> </Order> </Customer></CustomersOrders>‘;
-- 创建一个内部表示行集EXEC sys.sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument;-- 属性中心映射 Attribute-centric SELECT *FROM OPENXML (@DocHandle, ‘/CustomersOrders/Customer‘,1)WITH (custid INT,companyname NVARCHAR(40));-- 元素中心映射 Element-centric SELECT *FROM OPENXML (@DocHandle, ‘/CustomersOrders/Customer‘,2)WITH (custid INT,companyname NVARCHAR(40));-- Attribute- and element-centric-- Combining flag 8 with flags 1 and 2SELECT *FROM OPENXML (@DocHandle, ‘/CustomersOrders/Customer‘,11)WITH (custid INT,companyname NVARCHAR(40));-- Remove the DOMEXEC sys.sp_xml_removedocument @DocHandle;GOcustid companyname----------- ----------------------------------------1 NULL2 NULLcustid companyname----------- ----------------------------------------NULL Customer NRZBBNULL Customer MLTDNcustid companyname----------- ----------------------------------------1 Customer NRZBB2 Customer MLTDN
如果要全部显示的话,可以写成下面这样.
SELECT *FROM OPENXML (@DocHandle, ‘/CustomersOrders/Customer/Order‘,11)WITH (custid INT ‘../@custid‘,companyname NVARCHAR(40 ) ‘../companyname‘,orderid int,orderdate NVARCHAR(40) );
第一节结束….我感觉我是把书翻译了一边 = = ,不足之处还请大家指正.
参考资料: EXAM 70-461 Query Microsoft SQL Server 2012 Training Kit