SQL 查询具有多个子元素的分层 XML

Question

我正在使用 Microsoft SQL 服务器。

我有一个简单的层次结构，如 Xml 中的方向图:

DECLARE @XML as XML = CAST(
'<ROOT>
    <NODE NODE_ID="1">
        <EDGE>2</EDGE>
        <EDGE>3</EDGE>
        <EDGE>4</EDGE>
    </NODE>
    <NODE NODE_ID="2">
        <EDGE>1</EDGE>
        <EDGE>3</EDGE>
    </NODE>
</ROOT>' AS XML);

我想要的输出是这样的表格:

SOURCE_NODE_ID | DEST_NODE_ID
1              | 2
1              | 3
1              | 4
2              | 1
2              | 3

---

这样的查询:

SELECT  B.value('data(@NODE_ID)','int') AS SOURCE_NODE_ID,
        A.B.value('(EDGE/text())[1]', 'int') AS DEST_NODE_ID
FROM @XML.nodes('/ROOT/NODE') AS A(B);

只返回第一条边:

SOURCE_NODE_ID | DEST_NODE_ID
1              | 2
2              | 1

---

这个做得好一点:

SELECT  B.value('data(@NODE_ID)','int') AS SOURCE_NODE_ID,
        B.query('EDGE').value('.', 'int') AS DEST_NODE_ID
FROM @XML.nodes('/ROOT/NODE') AS A(B);

只有它将所有边连接到一个单元格中:

SOURCE_NODE_ID | DEST_NODE_ID
1              | 234
2              | 13

---

我怎样才能得到我想要的结果？我应该加入内部查询还是什么？可能我把它弄得太复杂了，肯定有一个简单的解决方案吗？

Answer 1

这样试试

由于NODE元素较多，需要为它们调用.nodes()。由于嵌套了很多EDGE元素，您需要为它们调用CROSS APPLY .nodes()。

剩下的就简单了...

DECLARE @XML as XML = CAST(
'<ROOT>
    <NODE NODE_ID="1">
        <EDGE>2</EDGE>
        <EDGE>3</EDGE>
        <EDGE>4</EDGE>
    </NODE>
    <NODE NODE_ID="2">
        <EDGE>1</EDGE>
        <EDGE>3</EDGE>
    </NODE>
</ROOT>' AS XML);

SELECT Nd.value('@NODE_ID','INT') AS SOURCE_NODE_ID
      ,Edg.value('.','INT') AS DEST_NODE_ID
FROM @XML.nodes('/ROOT/NODE') AS A(Nd)
CROSS APPLY A.Nd.nodes('EDGE') AS B(Edg)

结果

SOURCE_NODE_ID  DEST_NODE_ID
1               2
1               3
1               4
2               1
2               3