Getting Parent Child XML using TSQL

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')