<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)
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.
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)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With