`nodes()` 方法是否保持文档顺序? [英] Does the `nodes()` method keep the document order?

问题描述

xml 数据类型的nodes() 方法是否按文档顺序返回节点?

Does the nodes() method of the xml data type return nodes in document order?

例如，如果有这样的数据:

For example, if there are data like:

declare @xml xml;
set @xml = '<Fruits><Apple /><Banana /><Orange /><Pear /></Fruits>';

被查询为

select T.c.query('.')
from @xml.nodes('/Fruits/*') T(c);

元素会按文档顺序返回吗?如果省略 order by 子句，则 select 返回的行顺序已知是未定义的.select ... from ... .nodes() 是这种情况，还是例外?

will elements be returned in document order? Order of rows returned by select is known to be undefined if order by clause is omitted. Is it the case for select ... from ... .nodes(), or is it exceptional?

推荐答案

是的，nodes() 按文档顺序生成行集.查询计划中用于执行此操作的运算符是 表值函数 XML读者.

Yes, nodes() generates a row set in document order. The operator used in the query plan to do this is the Table Valued Function XML Reader.

表值函数 XML Reader 输入一个 XML BLOB 作为参数，然后生成表示 XML 文档顺序中的 XML 节点的行集.其他输入参数可能会将返回的 XML 节点限制为 XML 的子集文档.

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.

但是没有 order by 的查询具有未定义的顺序，因此无法保证.

But a query without order by has an undefined order so there are no guarantees.

解决该问题的一种方法是在 row_number() over() 子句中使用表值函数生成的 id 并按顺序使用生成的数字

One way to work around that is to use the id generated by the table valued function in row_number() over() clause and use the generated number in the order by.

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

不能直接在 order by 中使用 T.c.尝试这会给你

It is not possible to use T.c in an order by directly. Trying that will give you

消息 493，级别 16，状态 1，第 19 行
从 nodes() 方法返回的列 'c' 不能直接使用.它只能与四种 XML 数据类型方法之一一起使用，exist()、nodes()、query() 和 value()，或者用于 IS NULL 和 IS NOT NULL 检查.

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 为止它都可以正常工作.

The error did not mention that it should work with row_number but it does and that could very well be bug that might get fixed so the code above will fail. But up until SQL Server 2012 it works just fine.

在不依赖row_number 的未公开使用的情况下获得有保证的订单的一种方法是使用数字表，您可以在其中按位置提取节点.

A way to get a guaranteed order without relying on the undocumented use of row_number would be to use a table of numbers where you extract the nodes by position.

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

这篇关于`nodes()` 方法是否保持文档顺序?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！