Does nodes() or openxml returns rows in same order as it finds in xml?

Question

I have an xml which i need to parse using openxml or nodes(). The xml contains few child tags that repeat with different values, as below.

<root>
    <value>10</value>
    <value>12</value>
    <value>11</value>
    <value>1</value>
    <value>15</value>
<root>

For my code it is very important that i get all these rows returned in same order as in xml. I googled and gogled but nothing tells me if the @mp:id is always returned in same order as in xml. Or if nodes() return values in same order as it encounters them.

All I want to know if I can trust any of those two methods and be happy with proper order of rows.

P.S. excuse any errors or mistakes in above text, I dont enjoy typing codes in an android window either.

Answer 1

You can use row_number on the shredded XML like this.

declare @XML xml= 
'<root>
    <value>10</value>
    <value>12</value>
    <value>11</value>
    <value>1</value>
    <value>15</value>
</root>'

select value
from
  (
  select T.N.value('.', 'int') as value,
         row_number() over(order by T.N) as rn 
  from @xml.nodes('/root/value') as T(N)
  ) as T
order by T.rn

Uniquely Identifying XML Nodes with DENSE_RANK

Update:

You can also use a numbers table like this;

declare @XML xml= 
'<root>
    <value>10</value>
    <value>12</value>
    <value>11</value>
    <value>1</value>
    <value>15</value>
</root>';

with N(Number) as
(
  select Number
  from master..spt_values
  where type = 'P'
)
select @XML.value('(/root/value[sql:column("N.Number")])[1]', 'int')
from N
where N.Number between 1 and @XML.value('count(/root/value)', 'int')
order by N.Number