Finding node order in XML document in SQL Server

by

You can emulate the position() function by counting the number of sibling nodes preceding each node:

SELECT
    code = value.value('@code', 'int'),
    parent_code = value.value('../@code', 'int'),
    ord = value.value('for $i in . return count(../*[. << $i]) + 1', 'int')
FROM @Xml.nodes('//value') AS T(value)

Here is the result set:

code   parent_code  ord
----   -----------  ---
1      NULL         1
11     1            1
111    11           1
12     1            2
121    12           1
1211   121          1
1212   121          2

How it works:

  • The for $i in . clause defines a variable named $i that contains the current node (.). This is basically a hack to work around XQuery’s lack of an XSLT-like current() function.
  • The ../* expression selects all siblings (children of the parent) of the current node.
  • The [. << $i] predicate filters the list of siblings to those that precede (<<) the current node ($i).
  • We count() the number of preceding siblings and then add 1 to get the position. That way the first node (which has no preceding siblings) is assigned a position of 1.

More Related Contents:

  1. How can I query a value in SQL Server XML column
  2. SQL Server SELECT to JSON function
  3. How can I get a list of element names from an XML value in SQL Server
  4. SQL: How can I get the value of an attribute in XML datatype?
  5. Concatenate grouped rows
  6. SQL Server FOR XML Path make repeating nodes
  7. SQL QUERY COUNT THEN ADD
  8. SQL server command help and tutorials for specific commands?
  9. How do I UPDATE from a SELECT in SQL Server?
  10. CROSS JOIN vs INNER JOIN in SQL
  11. INNER JOIN vs LEFT JOIN performance in SQL Server
  12. Function vs. Stored Procedure in SQL Server
  13. What’s the best way to select the minimum value from several columns?
  14. Counting DISTINCT over multiple columns
  15. How to check if a stored procedure exists before creating it
  16. Insert Into… Merge… Select (SQL Server)
  17. Correct use of transactions in SQL Server
  18. The parameterized query expects the parameter which was not supplied
  19. SQL Server: drop table cascade equivalent?
  20. SQL Server converting varbinary to string
  21. SSIS Source Format Implicit Conversion for Datetime
  22. How do I prevent a database trigger from recursing?
  23. UNION the results of multiple stored procedures
  24. Executing SQL Server Agent Job from a stored procedure and returning job result
  25. how does SELECT TOP works when no order by is specified?
  26. Should I use the CASCADE DELETE rule? [duplicate]
  27. Dynamic order direction
  28. Why is RAND() not producing random numbers?
  29. SQL, How to Concatenate results?
  30. Should every SQL Server foreign key have a matching index? [closed]

Leave a Comment