在T-SQL中解析XML

Question

我知道已经有很多关于在MS T-SQL中查询xml的话题了，但是尽管有这么多示例，我仍然无法使我的查询正常工作。

我有以下XML：

<group>
    <items>
        <groupitem>
            <key>23137</key>
        </groupitem>
        <groupitem>
            <key>23139</key>
        </groupitem>
        <groupitem>
            <key>23151</key>
        </groupitem>
        <groupitem>
            <key>23153</key>
        </groupitem>
    </items>
</group>

我希望获得所有“key”项目，这样我就可以将它们插入表格中（因此有4行）

我首先将我的XML放入变量 @xml 中并运行以下查询：

SELECT doc.value('(key/text())[1]', 'nvarchar(255)') AS 'key'
    FROM @xml.nodes('/group/items/groupitem/*') AS ref(doc)

这给了我4个空行，如果我去掉[1]，会出现以下错误："XQuery [value()]: '*value()'需要一个单一值（或者空序列），但是发现类型为'xdt:untypedAtomic ''的操作数"

然后我尝试了这个：

SELECT doc.value('(/group/items/groupitem/key)[1]', 'nvarchar(255)') AS 'key'
    FROM @xml.nodes('/group/items/groupitem/*') AS ref(doc)

这实际上给了我一些数据，但不幸的是，它4次使用了相同的键23137，可能是由于语句中的[1]引起的。然而，删除它会使我回到之前的错误消息。

我知道如何在Xpath中做到这一点（/group/items/groupitem/key），但不知道如何在T-Sql中实现。有什么想法吗？

- ErikL

---

你在.nodes()末尾添加星号的原因是什么？你期望不同的元素吗？ - Shnugo

Answer 1

在以下查询中，doc 的上下文元素已经是 key 元素：

SELECT doc.value('(key/text())[1]', 'nvarchar(255)') AS 'key'
FROM @xml.nodes('/group/items/groupitem/*') AS ref(doc)

因此，在选择子句中不应再提及key。您可以使用.来引用当前上下文元素：

SELECT doc.value('.', 'nvarchar(255)') AS 'key'
FROM @xml.nodes('/group/items/groupitem/*') AS ref(doc)

- har07

---

谢谢，确实有效。我猜节点语句中的*作为通配符起作用，由于groupitem下只有一个元素，因此它被分配给doc的根节点？ - ErikL

可能是因为OP的查询中特意使用了text()，而忽略了更深层节点的内容。因此需要加上'(./text())[1]'。 - Serg

Answer 2

试试这个：

SELECT 
    doc.value('(key)[1]', 'int') AS 'key'
FROM 
    @xml.nodes('/group/items/groupitem') AS ref(doc)

在我的情况下，这将返回类似以下的输出：

key
-----
23137
23139
23151
23153

你是在寻找这个吗？

调用.nodes()基本上为您提供了一个XML片段的“伪”表格，每个匹配XPath表达式的元素都有一个。所以在你的情况下，你会得到四行XML返回，每一行代表<groupitem>节点的内容。你可以进入其中，获取包含在内部的<key>元素的值，将其转换为int - 然后就完成了！

- marc_s

---

谢谢您的解释，那正是我所需要的。还有一个问题：为什么需要[1]? - ErikL

@ErikL：因为可能会有多个元素嵌套在父元素下面。仅使用(key)将返回一个元素集合，而您无法将整个XML元素集合转换为原子值（如int）-只能转换单个元素。 - marc_s