如何使用sql Server 2012获取行的行号？
这是xml
<Rows> <Row>Coating</Row> <Row>Drying</Row> <Row>Waxing</Row> </Rows>
我需要像这样返回的数据
RowLabel RowNumber ------------------- Coating 1 Drying 2 Waxing 3
解决方法
您可以使用有关sql Server如何实现XML碎化的一些内部知识,并像这样使用row_number().
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)
参考：Uniquely Identifying XML Nodes with DENSE_RANK
或者你可以“安全地玩”并使用数字表.
select T.X.value('text()[1]',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')
