sql-server - SQL Server 2012 中 XML 元素的位置

Question

Create Table #tmp(ID int IDentity(1,1), XMLData XML)

Insert Into #tmp(XMLData)
Values('<SampleXML>
<Fruit>
<Fruits>Apple</Fruits>
<Fruits>Pineapple</Fruits>
</Fruit>
<Fruit>
<Fruits>Grapes</Fruits>
<Fruits>Melon</Fruits>
</Fruit>
</SampleXML>')

SELECT
ID,
A.x.query('data(.)') as name,
Row_Number() over(order by A.x) as number
FROM #tmp
CROSS APPLY XMLData.nodes('SampleXML/Fruit/Fruits') AS A(x)

这导致以下结果：

ID  name    number
1   Apple   1
1   Pineapple   2
1   Grapes  3
1   Melon   4

但我确实希望它看起来像这样：

ID  name    number
1   Apple   1
1   Pineapple   1
1   Grapes  2
1   Melon   2

我想知道“水果”是在哪个“水果”元素中找到的。

标签： sql-serverxml

Answer 1

您可以使用以下 SQL，请注意使用“OVER XML 节点”是一个未记录且不受支持的功能：

DECLARE @MyXML XML
SET @MyXML = '<SampleXML>
<Fruit>
<Fruits>Apple</Fruits>
<Fruits>Pineapple</Fruits>
</Fruit>
<Fruit>
<Fruits>Grapes</Fruits>
<Fruits>Melon</Fruits>
</Fruit>
</SampleXML>'

SELECT
Friuts.col.query('data(.)') as name,
DENSE_RANK() over(order by Friut.col) as number
FROM @MyXML.nodes('SampleXML/Fruit') AS Friut(col)
CROSS APPLY Friut.col.nodes('./Fruits') AS Friuts(col)