Finding node order in XML document in SQL Server

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         111     1            1111    11           112     1            2121    12           11211   121          11212   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.

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 XMLSET @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)

SQL Server's row_number() actually accepts an xml-nodes column to order by. Combined with a recursive CTE you can do this:

declare @Xml xml = '<value code="1">    <value code="11">        <value code="111"/>    </value>    <value code="12">        <value code="121">            <value code="1211"/>            <value code="1212"/>        </value>    </value></value>';with recur as (    select        ordr        = row_number() over(order by x.ml),        parent_code = cast('' as varchar(255)),        code        = x.ml.value('@code', 'varchar(255)'),        children    = x.ml.query('./value')    from @Xml.nodes('value') x(ml)    union all    select        ordr        = row_number() over(order by x.ml),        parent_code = recur.code,        code        = x.ml.value('@code', 'varchar(255)'),        children    = x.ml.query('./value')    from recur    cross apply recur.children.nodes('value') x(ml))select *from recurwhere parent_code = '121'order by ordr

As an aside, you can do this and it'll do what do you expect:

select x.ml.query('.')from @Xml.nodes('value/value')x(ml)order by row_number() over (order by x.ml)

Why, if this works, you can't just order by x.ml directly without row_number() over is beyond me.