sql server - Does the `nodes()` method keep the document order?

Question

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>';

which is queried as

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

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?

Answer 1

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.

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.

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

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

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

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.

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.

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