Does the `nodes()` method keep the document order?

by

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

More Related Contents:

  1. Max Nbr of Months W/ No Activity in Past 12 Months
  2. SQL server query to output the data shown below
  3. Will ANSI JOIN vs. non-ANSI JOIN queries perform differently?
  4. How do I generate a random number for each row in a T-SQL select?
  5. Combine multiple results in a subquery into a single comma-separated value
  6. Pass Dictionary to Stored Procedure T-SQL
  7. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  8. Floor a date in SQL server
  9. How to drop a table if it exists?
  10. Create a view with ORDER BY clause
  11. What does ‘COLLATE SQL_Latin1_General_CP1_CI_AS’ do?
  12. INSERT INTO vs SELECT INTO
  13. Possible to store value of one select column and use it for the next one?
  14. How FOR XML PATH(”) works when concatenating rows
  15. Scheduled run of stored procedure on SQL server
  16. SQL Server ORDER BY date and nulls last
  17. SQL Server variable columns name?
  18. What is the Oracle equivalent of SQL Server’s IsNull() function?
  19. SQL Recursive CTE: Finding objects linked by property
  20. PRINT statement in T-SQL
  21. T-sql – determine if value is integer
  22. Convert decimal time to hours and minutes
  23. SQL Server – defining an XML type column with UTF-8 encoding
  24. SQL Query Where Column = ” returning Emoji characters 🎃 and 🍰
  25. FOR JSON PATH results in SSMS truncated to 2033 characters
  26. How Can I Detect and Bound Changes Between Row Values in a SQL Table?
  27. Update SQL with consecutive numbering
  28. comma delimited list as a single string, T-SQL [duplicate]
  29. SQL Server error “Implicit conversion of because the collation of the value is unresolved due to a collation conflict.”
  30. Count number of records returned by group by

Leave a Comment