Querying Microsoft SQL Server 2012 读书笔记:查询和管理XML数据 2 -使用XQuery 查询XML数据
XQuery 是一个浏览/返回XML实例的标准语言. 它比老的只能简单处理节点的XPath表达式更丰富. 你可以同XPath一样使用.或是遍历所有节点,塑造XML实例的返回等.
作为一个查询语言, 你需要一个查询处理引擎. SQL Server 数据库通过XML数据类型方法的T-SQL 语句来处理XQuery. SQL Server 并不支持所有的XQuery 特性.比如XQuery 的用户自定义函数就不支持,因为你可以用T-SQL和CLR函数 .此外, T-SQL 支持非标的XQuery扩展 ,叫做 XML DML, 你可以用来修改XML数据的元素和属性. 因为XML数据类型是一个很大的对象,如果你只通过一个方法,修改xml以后直接替换这个值这样会导致巨大的性能瓶颈.
本节主讲使用XQuery检索数据,如果你要学习更多的XML数据类型,可以看第三节 . 在这个章节中,你只用XML数据类型查询方法和XML数据类型变量. 查询方法接收一个XQuery字符串参数,然后返回你要的XML .
XQuery 在 SQL Server中的实现遵循W3C( World Wide Web Consortium)标准,并且补充了数据修改的扩展.要获取更多的W3C 信息请访问 http://www.w3.org/, 一些XQuery新闻和附加资源可以访问http://www.w3.org/XML/Query/.
XQuery 基础
XQuery 和 XML一样大小写敏感.如果你想手动的敲打列子中得代码,请务必和写的一模一样. 比如你把data()写成Data()就会报一个 “没有Data()函数”的错误.
XQuery 返回序列(sequences).序列包含原子值(atomic values)或复杂值(complex values(XML 节点)). 任何节点,比如元素,属性,文本,处理命令,注释,或者文档,都可以包含在序列里面.当然你可以, 你可以格式化序列来获得良好格式的XML. 下面示例了三个不同序列的XML查询.
DECLARE @x AS XML; SET @x=N' <root> <a>1<c>3</c><d>4</d></a> <b>2</b> </root>'; SELECT @x.query('*') AS Complete_Sequence, @x.query('data(*)') AS Complete_Data, @x.query('data(root/a/c)') AS Element_c_Data;
返回结果如下
Complete_Sequence Complete_Data Element_c_Data
--------------------------------------------- ------------- --------------
<root><a>1<c>3</c><d>4</d></a><b>2</b></root> 1342 3
第一个XQuery是最简单的路径表达式,直接选择XML实例的所有东西; 第二个使用 data()函数从文档中提取所有原子数据 ; 第三个使用 data()函数返回元素C的原子数据 .
XQuery 中每个标示符都是限定名(qualified name),以下简称QName.QName包含本地名称和一个可选的命名空间前缀. 在之前的列子中 root, a, b, c, 还有 d 都是QNames; 不过他们都没有命名空间前缀,下面的命名空间标准都已经在SQL Server预先定义:
■■ xs The namespace for an XML schema (the uniform resource identifier, or URI, is
http://www.w3.org/2001/XMLSchema)
■■ xsi The XML schema instance namespace, used to associate XML schemas with instance
documents (http://www.w3.org/2001/XMLSchema-instance)
■■ xdt The namespace for XPath and XQuery data types (http://www.w3.org/2004/07
/xpath-datatypes)
■■ fn The functions namespace (http://www.w3.org/2004/07/xpath-functions)
■■ sqltypes The namespace that provides mapping for SQL Server data types
(http://schemas.microsoft.com/sqlserver/2004/sqltypes)
■■ xml The default XML namespace (http://www.w3.org/XML/1998/namespace)
你可以直接使用这些命名空间,而无需重新定义. 在XQuery的开头(prolog)定义自己的数据类型,然后用分号进行与查询部分隔开. 此外,你可以使用WITH子句来声明命名使用在XQuery表达式中使用的命名空间,也可以在开头定义默认的命名空间.
在Xquery中你可以通过小括号与冒号把注释括起来: (: 这个是注释 :). 注意不要把这个和XML文档的注释混淆; 下面的例子用三种方法来声明命名空间和注释.用来检索XML实例中第一个用户的订单.
DECLARE @x AS XML; SET @x=' <CustomersOrders xmlns:co="TK461-CustomersOrders"> <co:Customer co:custid="1" co:companyname="Customer NRZBB"> <co:Order co:orderid="10692" co:orderdate="2007-10-03T00:00:00" /> <co:Order co:orderid="10702" co:orderdate="2007-10-13T00:00:00" /> <co:Order co:orderid="10952" co:orderdate="2008-03-16T00:00:00" /> </co:Customer> <co:Customer co:custid="2" co:companyname="Customer MLTDN"> <co:Order co:orderid="10308" co:orderdate="2006-09-18T00:00:00" /> <co:Order co:orderid="10926" co:orderdate="2008-03-04T00:00:00" /> </co:Customer> </CustomersOrders>'; -- 在Xquery开头定义命名空间 SELECT @x.query(' (: explicit namespace :) declare namespace co="TK461-CustomersOrders"; //co:Customer[1]/*') AS [Explicit namespace]; -- 定义默认命名空间 SELECT @x.query(' (: default namespace :) declare default element namespace "TK461-CustomersOrders"; //Customer[1]/*') AS [Default element namespace]; -- 使用 WITH 子句定义命名空间 WITH XMLNAMESPACES('TK461-CustomersOrders' AS co) SELECT @x.query(' (: namespace declared in T-SQL :) //co:Customer[1]/*') AS [Namespace in WITH clause];
下面是简略了的查询结果
Explicit namespace
--------------------------------------------------------------------------------
<co:Order xmlns:co="TK461-CustomersOrders" co:orderid="10692" co:orderd
Default element namespace
--------------------------------------------------------------------------------
<Order xmlns="TK461-CustomersOrders" xmlns:p1="TK461-Customers
Namespace in WITH clause
--------------------------------------------------------------------------------
<co:Order xmlns:co="TK461-CustomersOrders" co:orderid="10692" co:orderd
XQuery 数据类型
XQuery 使用了大约50个预定义的数据类型.另外在SQL Server里面dditionally, 有个sqltypes 命名空间,定义了SQL Server的类型,你已经知道SQL Server的类型,因此不用担心XQuery类型, 大部分都是用不到的.这个章节罗列的是一些主要类型.不会讲太多内容. XQuery 数据类型氛围节点类型和原子类型. 节点类型包括,属性(attribute),注释(comment), 元素(element),命名空间( namespace), 文本(text), 处理指令(processing-instruction), and document-node. 下面这些是你可能用到的主要的原子类型(atomic types) xs:boolean, xs:string,xs:QName, xs:date, xs:time, xs:datetime, xs:float, xs:double, xs:decimal, and xs:integer.
你应该先对这些类型做个简要的回顾,我们通常使用的类型,XQuery都有, 你也可以使用特定的函数来处理特定的类型. 我门会花点时间来讲解重要的XQuery函数.
XQuery 函数
正如有许多数据类型一样,XQuery 有几十个函数 .他们被分成多个类别 . 我们之前用的 data() 函数是数据访问函数. 下面是SQL Server支持的比较有用的 XQuery 函数:
■■ Numeric functions ceiling(), floor(), and round()
■■ String functions concat(), contains(), substring(), string-length(), lower-case(), and
upper-case()
■■ Boolean and Boolean constructor functions not(), true(), and false()
■■ Nodes functions local-name() and namespace-uri()
■■ Aggregate functions count(), min(), max(), avg(), and sum()
■■ Data accessor functions data() and string()
■■ SQL Server extension functions sql:column() and sql:variable()
通过Books Online的这篇文档 “XQuery Functions against the xml Data Type” http://msdn.microsoft.com/en-us/library/ms189254.aspx. 你可以快速的判别 哪个函数可以用那种数据类型.
下面是使用聚合函数count()和max()来检索 XML文档中每个客户的订单信息的例子.
DECLARE @x AS XML; SET @x=' <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>'; SELECT @x.query(' for $i in //Customer return <OrdersInfo> { $i/@companyname } <NumberOfOrders> { count($i/Order) } </NumberOfOrders> <LastOrder> { max($i/Order/@orderid) } </LastOrder> </OrdersInfo> ');
正如你所见, 这个 XQuery比前面一个列子更复杂. 查询使用了迭代,也就是 XQuery FLOWER 表达式. 同时在查询中设定XML的返回格式. FLWOR 表达式稍后会讲, 现在就当如何在XQuery中使用聚合函数的示例就行. 结果如下:
<OrdersInfo companyname="Customer NRZBB"> <NumberOfOrders>3</NumberOfOrders> <LastOrder>10952</LastOrder> </OrdersInfo> <OrdersInfo companyname="Customer MLTDN"> <NumberOfOrders>2</NumberOfOrders> <LastOrder>10926</LastOrder> </OrdersInfo>
导航
通过XQuery你有大量的方法去导航XML文档. 不过在这里肯定是将不全的,需要了解透彻的话得找其他资料 . 基础的方法是使用 XPath 表达式. 通过XQuery, 你可以指定当前节点的一个绝对路径或者相对路径. XQuery会确定当前文档中的位置; 也就是说你可以引用相对路径来导向前一个路径到当前节点的位置.每个路径都包含一系从左至右的层级. 一个完整的路径表现形式如下:
Node-name/child::element-name[@attribute-name=value]
层级用斜杠分割;因此,以上示例包含两级. 在第二级你可以看到一个层级的详细构造 .一个层级包含三个部分:
■■ 轴(Axis) 在上例中轴是 child::, 指明是前一级节点的子节点 .
■■ 节点测试(Node test) 节点测试指定选择节点的范围,在本例中,元素名是节点测试;它只选择了节点的元素名.
■■ 谓语(Predicate) 进一步缩小搜索范围. 在本例中,有一个谓语:[@attribute-name=value], 只选择属性名字attribute-name 并且值是value的节点. 比如 [@orderid=10952].
注意谓语的列子,那里其实有个attribute:: 轴; @是 attribute::轴的缩写. 这个看着有点混淆;不过在你导航XML文档的时候比较有用: up (in the hierarchy), down (in the hierarchy), here (in current node), and right (in the current context level, to find attributes).
表7-2 罗列了SQL Server中的 轴.
节点测试在轴之后,一个节点测试可以是简单的一个名字,你指定这个名字来作为节点.你也可以使用通配符 * (asterisk) 表示任意主要节点(principal node) . 一个主要节点适用于所有轴.如果轴是 attribute::,那么主要节点就是属性,除此以外对于其它轴来说就是一个元素. 你也可以缩小通配符的搜索范围. 比如你可以指定搜索的主节点的命名空间的前缀 ,用 prefix:* 这种格式 .如果你要所有命名空间主要节点的本地名字(local-name),写成 *:local-name 即可.
你也可以执行节点类型测试,用来查非主要节点(principal nodes)的节点 . 下面罗列了这些节点类型测试:
■■ comment() 允许你选择备注(comment)节点.
■■ node() 匹配任意节点. 不与通配符混淆 asterisk (*) wildcard; * ,前者表示所有节点,后着表示主要节点.
■■ processing-instruction() 要允许你检索处理指令节点.
■■ text() 允许你检索文本节点 ,或者没有tags的节点.
谓词
基本的谓词包括数值(numeric)和布尔(Boolean)谓词. 数值谓词很简单,就是选择节点的位置.它们被方括号(brackets)括起来. 例如, /x/y[1] 意思是每个x元素的第一个子元素y . 你也可以用小括号(parentheses) 把整个数值谓词括起来. 比如 , (/x/y)[1] 表示返回x/y所有节点的第一个元素
布尔谓词,布尔谓词会选择所有的计算结果为真的节点 . XQuery支持逻辑与和逻辑或运算.不过比较运算符的操作比较诡异.原子值和序列都可以比较.在序列中只要有一个原子值为真,那个整个表达式的计算结果也为真. 请查看下面例子.
DECLARE @x AS XML = N''; SELECT @x.query('(1, 2, 3) = (2, 4)'); -- true SELECT @x.query('(5, 6) < (2, 4)'); -- false SELECT @x.query('(1, 2, 3) = 1'); -- true SELECT @x.query('(1, 2, 3) != 1'); – true
第一个表达式为真是因为两个序列中都有2 . 第二个表达式为假,因为第一个序列中原子值都第二个序列大 第三个表达式为真因为左边有个原子值与右边原子值相同. 第四个表达式为真,因为左边有原子值不等于右边的原子值. 很有趣的结果,不是么? .序列 (1, 2, 3) 即等于又不等于原子值1 . 如果你感到迷茫,可以用值比较运算符(Value comparison operators ),(前面的例子是比较常见的符号运算符.在XQuery中叫做一般比较运算符 (general comparison operators)) . 值比较运算符无法处理序列,只能用于单个数字. 以下是值比较运算符的例子.
DECLARE @x AS XML = N''; SELECT @x.query('(5) lt (2)'); -- false SELECT @x.query('(1) eq 1'); -- true SELECT @x.query('(1) ne 1'); -- false GO DECLARE @x AS XML = N''; SELECT @x.query('(2, 2) eq (2, 2)'); -- error GO
注意最后一条查询,试图用值比较运算符来比较序列.发生了一个错误.
表7-3 列出了一般比较操作符和值比较操作符的对照表.
XQuery 同样支持 if..then..else 条件表达式,下面是语法.
if (<expression1>) then <expression2> else <expression3>
注意,if..then..else expression 并不能用来改变Xquery的查询程序流. 仅仅是用来处理逻辑表达式参数,并且返回值. 它更像是T-SQL 中的 CASE 表达式. 而不是T-SQL 中的IF语句.
下面是条件表达式的例子.
DECLARE @x AS XML = N' <Employee empid="2"> <FirstName>fname</FirstName> <LastName>lname</LastName> </Employee> '; DECLARE @v AS NVARCHAR(20) = N'FirstName'; SELECT @x.query(' if (sql:variable("@v")="FirstName") then /Employee/FirstName else /Employee/LastName ') AS FirstOrLastName; GO
在本例中, 结果是 fname , 如果你改变@v的变量值 ,结果就是 lanme.
FLWOR Expressions
XQurey 真正强大之处是 FLWOR 表达式. FLWOR 是 for, let, where, order by, 和 return的缩写. FLWOR 表达式主要用来做循环处理 .你用它可以通过XPath表达式来迭代序列.虽然你经常通过节点序列进行迭代,你可以使用FLWOR表达式遍历任何序列 . 你可以用谓语限定处理的节点,排序节点,格式化返回的XML. FLWOR 语句的组成如下:
■■ For 通过For子句, 你可以把迭代变量与输入的序列绑定.输入序列可以是节点序列,原子值序列.你可以通过迭代或函数建立原子值序列.
■■ Let 通过let 子句(可选), 你可以为一个迭代分配值或者变量.分配的表达式可以返回一个节点序列或者原子值序列.
■■ Where 通过where子句(可选), 你可以对迭代进行过滤.
■■ Order by 使用order by 子句, 通过输入序列的原子值来进行排序.
■■ Return 每个迭代return 子句都会进行一次计算, 然后把结果按照迭代顺序返回到客户端. 使用Return子句你需要格式化XML结果.
这里是使用所有FLWOR子句的例子.
DECLARE @x AS XML; SET @x = N' <CustomersOrders> <Customer custid="1"> <!-- Comment 111 --> <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"> <!-- Comment 222 --> <companyname>Customer MLTDN</companyname> <Order orderid="10308"> <orderdate>2006-09-18T00:00:00</orderdate> </Order> <Order orderid="10952"> <orderdate>2008-03-04T00:00:00</orderdate> </Order> </Customer> </CustomersOrders>'; SELECT @x.query('for $i in CustomersOrders/Customer/Order let $j := $i/orderdate where $i/@orderid < 10900 order by ($j)[1] return <Order-orderid-element> <orderid>{data($i/@orderid)}</orderid> {$j} </Order-orderid-element>') AS [Filtered, sorted and reformatted orders with let clause];
如你所见,for子句的查询迭代,通过一个迭代变量遍历所有订单(order)节点然后返回.在Xquery中迭代变量必须以$(dollar)符号开始. where子句把属性orderid 小于10900的Order节点都过滤了.
传递给order by 子句的值的类别必须与XQuery gt 操作符相兼容. 回想一下, gt 操作符需要原子值( atomic values). 这个查询通过orderdate 元素进行排序.虽然每个订单只有一个 orderdate 元素, 但XQuery不知道这些,还会把它当序列看待,而不是原子值. 数字谓语指定第一个orderdate 元素作为排序值. 没有这个数字谓语,会报错.
return 子句用来处理返回的格式. 例子中,它把orderid 属性转为元素(通过手动建立元素并且用data()函数提取orderid的值).也返回了 orderdate 元素. 然后把这两个都放在了 Order-orderid-element 元素中. 注意花括号中的用来提取orderid元素和orderdate 元素的表达式. XQuery只计算花括号中的表达式,花括号职位的都当做字符串直接返回.
let 子句分配一个名字给 $i/orderdate 表达式. 这个表达式重复了两次,一个是在ordery by 子句还有一个在return 子句. 例子中用了变量$j 作为这个表达式的名字 ,下面是查询结果.
<Order-orderid-element> <orderid>10308</orderid> <orderdate>2006-09-18T00:00:00</orderdate> </Order-orderid-element> <Order-orderid-element> <orderid>10692</orderid> <orderdate>2007-10-03T00:00:00</orderdate> </Order-orderid-element> <Order-orderid-element> <orderid>10702</orderid> <orderdate>2007-10-13T00:00:00</orderdate> </Order-orderid-element>
XQuery/XPath 导航实践
在这个练习中,你在XQuery里面用XPath表达式进行导航. 我们从一个简单的path 表达式开始 , 然后再用谓语练习复杂的path表达式.
练习 1 使用简单的 XPath 表达式
在本例中, 你使用简单的XPath 表达式返回一个XML数据的子集.
1. 建立一个XML.
DECLARE @x AS XML; SET @x = N' <CustomersOrders> <Customer custid="1"> <!-- Comment 111 --> <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"> <!-- Comment 222 --> <companyname>Customer MLTDN</companyname> <Order orderid="10308"> <orderdate>2006-09-18T00:00:00</orderdate> </Order> <Order orderid="10952"> <orderdate>2008-03-04T00:00:00</orderdate> </Order> </Customer> </CustomersOrders>';
2. 写一个查询,选择 Customer 节点和其子节点. 我们只选择主要节点结果类似下面略写的结果
1. Principal nodes
--------------------------------------------------------------------------------
<companyname>Customer NRZBB</companyname><Order orderid="10692"><orderdate>2007-
通过以下查询可以活的预期的结果
SELECT @x.query('CustomersOrders/Customer/*') AS [1. Principal nodes];
2. 现在返回所有节点,不单单是主要节点. 结果类似下面.
2. All nodes
--------------------------------------------------------------------------------
<!-- Comment 111 --><companyname>Customer NRZBB</companyname><Order orderid="106
使用下面语句可以实现
SELECT @x.query('CustomersOrders/Customer/node()') AS [2. All nodes];
3. 只返回注释节点(comment nodes) . 结果类似如下.
3. Comment nodes
--------------------------------------------------------------------------------
<!-- Comment 111 --><!-- Comment 222 –>
通过以下语句可以实现
SELECT @x.query('CustomersOrders/Customer/comment()') AS [3. Comment nodes];
练习 2 使用带谓语的XPath 表达式
在这个额例子中,你通过谓语过滤XML子集.
1. 我们使用上列中一样的XML
2. 返回所有customer 2的订单. 预计结果如下:
4. Customer 2 的订单
--------------------------------------------------------------------------------
<Order orderid="10308"><orderdate>2006-09-18T00:00:00</orderdate></Order><Order
使用以下语句可以实现
SELECT @x.query('//Customer[@custid=2]/Order') AS [4. Customer 2 orders];
3. 返回单号为10952的订单 ,预计结果如下.
5. Orders with orderid=10952
--------------------------------------------------------------------------------
<Order orderid="10952"><orderdate>2008-03-16T00:00:00</orderdate></Order><Order
使用以下查询可以实现
SELECT @x.query('//Order[@orderid=10952]') AS [5. Orders with orderid=10952];
4. 返回第二个客户的订单.预计结果如下.
6. 2nd Customer with at least one Order
--------------------------------------------------------------------------------
<Customer custid="2"><!-- Comment 222 --><companyname>Customer MLTDN</companyname
使用以下查询可以实现.
SELECT @x.query('(/CustomersOrders/Customer/ Order/parent::Customer)[2]') AS [6. 2nd Customer with at least one Order];
