CODEWITHSUNDEEP
sql-serverxml
zicom
zicom

Reputation: 83

Selecting parent and children values of XML together

Having an issue trying to retreive the parent and children values together from the XML. The code looks as below but only able to retrieve 1 child per parent. How do I select all the children with the parent?


declare @x xml;

set @x = '
    <Parent ParentID="1" ParentName="Mary">
        <Child ChildID="2" ChildName="Paul" />
        <Child ChildID="3" ChildName="Alan" />
        <Child ChildID="4" ChildName="David" />
    </Parent>
    <Parent ParentID="5" ParentName="James">
        <Child ChildID="6" ChildName="Amy" />
        <Child ChildID="7" ChildName="Julie" />
    </Parent>
';

select 
tbl.col.value('@ParentID', 'int') AS ParentID,
tbl.col.value('@ParentName', 'varchar(50)') AS ParentName,
tbl.col.value('Child[1]/@ChildID[1]', 'int') AS ChildID,
tbl.col.value('Child[1]/@ChildName[1]', 'varchar(50)') AS ChildName
from @x.nodes('/Parent') as tbl(col);

I get the following results but want the parent to repeat for every child node.


ParentID   ParentName   ChildID  ChildName
1          Mary         2        Paul
5          James        6        Amy

The problem is the Child[1] but I dont know how to get around it. I tried sql-variable but couldnt get that to work either. Ideally I want to see the following :


ParentID   ParentName   ChildID  ChildName
1          Mary         2        Paul
1          Mary         3        Alan
1          Mary         4        David
5          James        6        Amy
5          James        7        Julie

Any help would be much appreciated. Thanks.

Upvotes: 8

Views: 5809

Answers (2)

Prathamesh shinde
Prathamesh shinde

Reputation: 21

Try the Following Code:

    select 
    tbl.col.value('@ParentID', 'int') AS ParentID,
    tbl.col.value('@ParentName', 'varchar(50)') AS ParentName,
    p.j.value('@ChildID[1]', 'int') AS ChildID,
    p.j.value('@ChildName[1]', 'varchar(50)') AS ChildName
    from @x.nodes('/Parent') as tbl(col)
    cross apply tbl.col.nodes('Child') as p(j)

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453897

select 
tbl.col.value('parent::*/@ParentID', 'int') AS ParentID,
tbl.col.value('parent::*/@ParentName', 'varchar(50)') AS ParentName,
tbl.col.value('@ChildID', 'int') AS ChildID,
tbl.col.value('@ChildName', 'varchar(50)') AS ChildName
from @x.nodes('/Parent/Child') as tbl(col);

Upvotes: 7

Related Questions