获取 xml 元素在 SQL Server 2012 中的位置
问题描述:
如何使用 SQL Server 2012 获取行的行号?
How to get the row number for the rows using 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()
.
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: 唯一识别 XML具有 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')