Finding node order in XML document in SQL Server
Solution 1:
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-likecurrent()
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.
Solution 2:
You can get the position of the xml returned by a x.nodes()
function like so:
row_number() over (order by (select 0))
For example:
DECLARE @x XML
SET @x = '<a><b><c>abc1</c><c>def1</c></b><b><c>abc2</c><c>def2</c></b></a>'
SELECT
b.query('.'),
row_number() over (partition by 0 order by (select 0))
FROM
@x.nodes('/a/b') x(b)