Finding node order in XML document in SQL server

Question

How can I find the order of nodes in an XML document?

What I have is a document like this:

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

and I'm trying to get this thing into a table defined like

CREATE TABLE values(
    code int,
    parent_code int,
    ord int
)

preserving the order of the values from the XML document (they can't be ordered by their code). I want to be able to say

SELECT code FROM values WHERE parent_code = 121 ORDER BY ord

and the results should, deterministically, be

code
1211
1212

I have tried

SELECT value.value('@code', 'varchar(20)') code, value.value('../@code', 'varchar(20)') parent, value.value('position()', 'int')
  FROM @xml.nodes('/root//value') n(value)
 ORDER BY code desc

but it doesn't accept the position() function ('position()' can only be used within a predicate or XPath selector).

I guess it's possible some way, but how?

Answer 1

According to this document and this connect entry it is not possible, but the Connect entry contains two workarounds.

I do it like this:

WITH n(i) AS (SELECT 0 UNION SELECT 1 UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9),
     o(i) AS (SELECT n3.i * 100 + n2.i * 10 + n1.i FROM n n1, n n2, n n3)
SELECT v.value('@code', 'varchar(20)') AS code,
       v.value('../@code', 'varchar(20)') AS parent,
       o.i AS ord
  FROM o
 CROSS APPLY @xml.nodes('/root//value[sql:column("o.i")]') x(v)
 ORDER BY o.i