SQL Server 2005, relational tables to hierarchical xml.

Question

Based on the following data structure.

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

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

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

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

Is it possible to produce a single Select statement that can produce the hierarchical output below?

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

Answer 1

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