[ACCEPTED]-Get the position of xml element in SQL Server 2012-sql-server-2012
Accepted answer
You can use some internal knowledge about 3 how SQL Server implements XML shredding 2 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 1 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')
There is a way within XQuery to return the position of 6 the current node: you can count all nodes 5 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/Rowthat are previous to$iand counts them - Then add 1 to make it one-based
In some 4 situations you may not want to go back to 3 the root node, in which case you can use 2 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 1 from the end by changing to >>.
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.