Get the position of xml element in SQL Server 2012

Question

How to get the row number for the rows using SQL Server 2012?

Here is the xml

<Rows>
   <Row>Coating</Row>
   <Row>Drying</Row>
   <Row>Waxing</Row>
</Rows>

I need data returned like this

RowLabel  RowNumber
-------------------
Coating    1
Drying     2
Waxing     3

Answer 1

You can use some internal knowledge about how SQL Server implements XML shredding and use row_number() like this.

declare @XML xml = 
'<Rows>
   <Row>Coating</Row>
   <Row>Drying</Row>
   <Row>Waxing</Row>
</Rows>'

select T.X.value('text()[1]', 'nvarchar(100)') as RowLabel,
       row_number() over(order by T.X) as RowNumber
from @XML.nodes('/Rows/Row') as T(X)

Ref: Uniquely Identifying XML Nodes with DENSE_RANK

Or you can "play it safe" and use a numbers table.

select T.X.value('text()[1]', 'nvarchar(100)') as RowLabel,
       N.Number as RowNumber
from Numbers as N
  cross apply @XML.nodes('/Rows/Row[sql:column("N.Number")]') as T(X)
where N.Number between 1 and @XML.value('count(/Rows/Row)', 'int')

Answer 2

There is a way within XQuery to return the position of the current node: you can count all nodes previous to it

SELECT T.X.value('text()[1]', 'nvarchar(100)') as RowLabel,
       T.X.value('let $i := . return count(/Rows/Row[. << $i]) + 1', 'int') as RowNumber
FROM @xml.nodes('/Rows/Row') as T(X);

What this does is:

• Assign the current node to the variable $i
• Takes all the nodes in /Rows/Row that are previous to $i and counts them
• Then add 1 to make it one-based

In some situations you may not want to go back to the root node, in which case you can use the .. parent axis

SELECT T.X.value('text()[1]', 'nvarchar(100)') as RowLabel,
       T.X.value('let $i := . return count(../Row[. << $i]) + 1', 'int') as RowNumber
FROM @xml.nodes('/Rows/Row') as T(X);

You can also count backwards from the end by changing to >>.

dbfiddle