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?

This is a good question: Copy'n'pasteable test code, own effort, expected output, clear explanation... If only all question were like this :-) Voted it up! — Gottfried Lesigang
– Gottfried Lesigang, Commented Jul 6, 2016 at 12:29

Gottfried Lesigang · Accepted Answer · 2016-07-06 12:21:52Z

6

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

answered Jul 6, 2016 at 12:21

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Louis Somers Over a year ago

Ah, CROSS APPLY, I had seen it before but couldn't wrap my head around it yet... Thanks for your lightning fast response!

Gottfried Lesigang Over a year ago

@LouisSomers, glad to read this! You were very close! Thx to your good question it was no big deal to copy your query, type some more letters and place the answer... Happy Coding!

Collectives™ on Stack Overflow

SQL query hierarchical XML with multiple sub-elements

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related