例如,如果有如下数据：
declare @xml xml set @xml = '<Fruits><Apple /><Banana /><Orange /><Pear /></Fruits>'
被查询
select T.c.query('.')
from @xml.nodes('/Fruits/*') T(c)
将以文件顺序返回元素？如果order by被省略,则select已返回的行的顺序是未定义的.选择…从… .nodes()的情况,是否是例外？
declare @xml xml
set @xml = '<Data><Element OrderNo="1" /><Element OrderNo="2" />'
+ '<Element OrderNo="3" /><Element OrderNo="4" />'
+ '<Element OrderNo="5" /></Data>'
select * from (
select T.c.value('.','int') OrderNo1,row_number() over (order by @@spid) OrderNo2
from @xml.nodes('/Data/Element/@OrderNo') T(c)) sq
where OrderNo1 != OrderNo2
解决方法
Table-valued Function XML Reader inputs an XML BLOB as a parameter and
produces a row set representing XML nodes in XML document order. Other
input parameters may restrict XML nodes returned to a subset of XML
document.
但是没有订单的查询有一个未定义的顺序,所以没有保证.
解决这个问题的一种方法是使用row_number()over()子句中的表值函数生成的id,并按顺序使用生成的数字.
select X.q
from
(
select T.c.query('.') as q,row_number() over(order by T.c) as rn
from @xml.nodes('/Fruits/*') T(c)
) as X
order by X.rn
直接用T.c命令是不可能的.尝试会给你
Msg 493,Level 16,State 1,Line 19
The column ‘c’ that was returned from the nodes() method cannot be used directly. It can only be used with one of the four XML data type methods,exist(),nodes(),query(),and value(),or in IS NULL and IS NOT NULL checks.
该错误没有提到它应该与row_number一起工作,但它可以很好地是可能会被修复的错误,所以上面的代码将失败.但直到sql Server 2012它的工作正常.
获取保证订单而不依赖于未记录使用row_number的方法将是使用您通过位置提取节点的数字表.
select T.c.query('.') as q
from Numbers as N
cross apply @xml.nodes('/Fruits/*[sql:column("N.Number")]') as T(c)
where N.Number between 1 and @xml.value('count(/Fruits/*)','int')
order by N.Number