Geek asks and answers

Do nodes () or openxml return strings in the same order as in xml?

I have xml that I need to parse with openxml or nodes (). Xml contains several child tags that are repeated with different values, as shown 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 all of these lines are returned in the same order as in xml. I am googled and gogled, but it tells me nothing if @mp: id always returns in the same order as in xml. Or, if node () returns the values ​​in the same order in which they occur.

All I want to know is whether I can trust any of these two methods and be satisfied with the correct line order.

PS sorry any errors or errors in the above text, I also do not receive input codes in the Android window.

+5
2

row_number XML, .

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

XML DENSE_RANK

Update:

, :

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

XPath : '/root[1]/value[1]' - , '/root[1]/value[2]' - .. '(/root/value)[1]' '(/root/value[2])'. , , 1, 2, 3 .. . , .

P.S. ?

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

select x.value(N'position()', N'int') as position,
  x.value(N'.', 'int') as value
  from @x.nodes(N'//root/value') t(x)

, ...

Msg 2371, Level 16, State 1, Line 9
XQuery [value()]: 'position()' can only be used within a predicate or XPath selector

, ...

0

More articles:


All Articles