SQL query hierarchical XML with multiple sub-elements

Question

I'm using Microsoft SQL server.

I have a simple hierarchy like a directional graph in 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);

My desired output would be a table like this:

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

---

A query like this:

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);

Only returns the first edge:

SOURCE_NODE_ID | DEST_NODE_ID
1              | 2
2              | 1

---

This one does a little better:

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);

Only it concatenates all edges into one cell:

SOURCE_NODE_ID | DEST_NODE_ID
1              | 234
2              | 13

---

How can I get my desired result? Should I join with an inner query or something? Probably I'm making it too complicated, surely there is a simple solution to this?

Answer 1

Try it like this

As there are many NODE elements, you need to call .nodes() for them. As there are many EDGE elements nested, you need to call CROSS APPLY .nodes() for them.

The rest is easy...

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)

The result

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