sql server - SQL query hierarchical XML with multiple sub-elements -

i'm using microsoft sql server.

i have simple hierarchy directional graph in xml:

declare @xml 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>' xml); 

my desired output table this:

source_node_id | dest_node_id 1              | 2 1              | 3 1              | 4 2              | 1 2              | 3 

---

a query this:

select  b.value('data(@node_id)','int') source_node_id,         a.b.value('(edge/text())[1]', 'int') dest_node_id @xml.nodes('/root/node') a(b); 

only returns first edge:

source_node_id | dest_node_id 1              | 2 2              | 1 

---

this 1 little better:

select  b.value('data(@node_id)','int') source_node_id,         b.query('edge').value('.', 'int') dest_node_id @xml.nodes('/root/node') a(b); 

only concatenates edges 1 cell:

source_node_id | dest_node_id 1              | 234 2              | 13 

---

how can desired result? should join inner query or something? i'm making complicated, surely there simple solution this?

try this

as there many node elements, need call .nodes() them. there many edge elements nested, need call cross apply .nodes() them.

the rest easy...

declare @xml 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>' xml);  select nd.value('@node_id','int') source_node_id       ,edg.value('.','int') dest_node_id @xml.nodes('/root/node') a(nd) cross apply a.nd.nodes('edge') b(edg) 

the result

source_node_id  dest_node_id 1               2 1               3 1               4 2               1 2               3