T-SQL XPath 查询，包括父级

Question

这个问题一直困扰着我的星期五下午：

我有这个 XML：

declare @xml as XML
set @xml =
'<fields>
<field>
<id>1</id>
<items>
<item>
<name>name1_1</name>
<value>value1_1</value>
</item>
<item>
<name>name1_2</name>
<value>value1_2</value>
</item>
</items>
</field>
<field>
<id>2</id>
<items>
<item>
<name>name2_1</name>
<value>value2_1</value>
</item>
<item>
<name>name2_2</name>
<value>value2_2</value>
</item>
</items>
</field>
</fields>'

使用 T-SQL 和 XPath，我需要一个查询来获取此结果：

id   name     value
1    name1_1  value1_1
1    name1_2  value1_2
2    name2_1  value2_1
2    name2_2  value2_2

我通过以下方式获取名称和值：

SELECT c.value('name[1]', 'nvarchar(255)') name,
c.value('value[1]', 'nvarchar(255)') value
FROM @xml.nodes('fields/field/items/item') t(c)

。但是如何插入父列"id"？

Answer 1

您自己的代码使用.nodes()从重复元素获取派生表。在您的情况下，有两个级别的重复元素：

• 许多字段和每个字段内
• 许多物品

您必须使用两次.nodes()：

SELECT fld.value(N'(id/text())[1]',N'int') AS FieldID
,itm.value(N'(name/text())[1]',N'nvarchar(max)') AS ItemName
,itm.value(N'(value/text())[1]',N'nvarchar(max)') AS ItemValue
FROM @xml.nodes(N'/fields/field') AS A(fld)
OUTER APPLY A.fld.nodes(N'items/item') AS B(itm);

第一个.nodes()返回 XML 片段，每个字段一个，第二个节点为每个字段片段调用以选取其项目。

如果可能存在没有<item>节点的字段，请使用OUTER APPLY，当您不想看到没有<item>节点的字段时，请使用CROSS APPLY(类似于LEFT JOIN与INNER JOIN)

Answer 2

假设：每个field只有一个id元素。

SELECT c.value('../../id[1]', 'int') id,
c.value('name[1]', 'nvarchar(255)') name,
c.value('value[1]', 'nvarchar(255)') value
FROM @xml.nodes('fields/field/items/item') t(c)

..运算符在 XPATH 中表示"选择节点的父节点"。因此，查询将选择item的父节点，然后选择items的父节点，然后选择第一个子节点id