CODEWITHSUNDEEP
sqlsql-serverxmlt-sql
Siva Sankaran
Siva Sankaran

Reputation: 1568

How to retrieve all child elements for all parent elements from xml in sql

<people>
<parent>
  <parent-name> joel </parent-name>
  <child> john </child>
  <child> sara </child>
  <child> ram </child>
</parent>

<parent>
  <parent-name> sam </parent-name>
  <child> david </child>
  <child> george </child>
  <child> wilson </child>
</parent>
</people>

the desired output is:

parent  |  child
--------|---------
joel    |  john
joel    |  sara
joel    |  ram
sam     |  david
sam     |  george
sam     |  wilson

I tried the following sql query to retrieve all child element for all parent, only I can get first child element

select a.b.value('parent-name[1]','varchar(50)') as parent
      , a.b.value('child[1]' ,'varchar(50)') as child
from @myxml.nodes('people/parent')a(b)

Upvotes: 5

Views: 9938

Answers (3)

Ajay
Ajay

Reputation: 6590

Try this.

select 
tbl.col.value('parent::*/@parent-name', 'varchar(50)') AS ParentName,
tbl.col.value('@child', 'varchar(50)') AS ChildName
from @x.nodes('/people/parent/child') as tbl(col);

Upvotes: 0

Ben
Ben

Reputation: 35613

The trick is to iterate the children, then go up one level and get the parent name.

select a.b.value('(../parent-name)[1]','varchar(50)') as parent
  , a.b.value('(.)[1]' ,'varchar(50)') as child
from @myxml.nodes('people/parent/child')a(b)

Upvotes: 5

marc_s
marc_s

Reputation: 754268

You need to use CROSS APPLY and .nodes() on the <child> nodes of each parent node:

SELECT
    a.b.value('(parent-name)[1]', 'varchar(50)') as parent, 
    XChild.value('.' ,'varchar(50)') as child
FROM
    @myxml.nodes('people/parent') AS a(b)
CROSS APPLY
    b.nodes('child') AS XTbl(XChild)

This will return all parents with all their children.

Upvotes: 8

Related Questions