title: SQL Server 2005, relational tables to hierarchical xml
body: <p>Based on the following data structure.</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>Is it possible to produce a single Select statement that can produce the hierarchical output below?</p>
  
  <pre><code>&lt;Output&gt;
      &lt;Parent Id="1" Name="a"&gt;
          &lt;Child Id="1" Name="a1" /&gt;
          &lt;Child Id="2" Name="a1" /&gt;
          &lt;Child Id="3" Name="a1" /&gt;
      &lt;/Parent&gt;
      &lt;Parent Id="2" Name="b"&gt;
          &lt;Child Id="4" Name="b1" /&gt;
          &lt;Child Id="5" Name="b1" /&gt;
          &lt;Child Id="6" Name="b1" /&gt;
      &lt;/Parent&gt;
      &lt;Parent Id="3" Name="c"&gt;
          &lt;Child Id="7" Name="c1" /&gt;
          &lt;Child Id="8" Name="c1" /&gt;
          &lt;Child Id="9" Name="c1" /&gt;
      &lt;/Parent&gt;
  &lt;/Output&gt;
  </code></pre>