Getting Parent Child XML using TSQL

Raja Krishnamurthy
13y
7.9k
0
0

Blog

 CREATE TABLE [Parent]
(
    Id INT,
    Name nvarchar(100)
)

CREATE TABLE [Child]
(
    Id INT,
    ParentId INT,
    Name nvarchar(100)
)

--parent details

INSERT INTO [Parent] VALUES (1, 'a')
INSERT INTO [Parent] VALUES (2, 'b')
INSERT INTO [Parent] VALUES (3, 'c')

--child details

INSERT INTO [Child] VALUES (1, 1, 'a1')
INSERT INTO [Child] VALUES (2, 1, 'a2')
INSERT INTO [Child] VALUES (3, 1, 'a3')
INSERT INTO [Child] VALUES (4, 2, 'b1')
INSERT INTO [Child] VALUES (5, 2, 'b2')
INSERT INTO [Child] VALUES (6, 2, 'b3')
INSERT INTO [Child] VALUES (7, 3, 'c1')
INSERT INTO [Child] VALUES (8, 3, 'c2')
INSERT INTO [Child] VALUES (9, 3, 'c3')

Expected output:

<Output>
    <Parent Id="1" Name="a">
        <Child Id="1" Name="a1" />
        <Child Id="2" Name="a1" />
        <Child Id="3" Name="a1" />
    </Parent>
    <Parent Id="2" Name="b">
        <Child Id="4" Name="b1" />
        <Child Id="5" Name="b1" />
        <Child Id="6" Name="b1" />
    </Parent>
    <Parent Id="3" Name="c">
        <Child Id="7" Name="c1" />
        <Child Id="8" Name="c1" />
        <Child Id="9" Name="c1" />
    </Parent>
</Output>

Query:

 select Id as [@Id], Name as [@Name],
    (select Child.Id as [@Id], Child.Name as [@Name]
     From Child
     where Child.ParentId = Parent.Id
     for xml path('Child'), type) as [*]
from Parent
for xml path ('Parent'), root('Output')