title: How to return XML from SQL Server 2008 that is structured with multiple selections sharing a common parent
body: <p>I've tried using "FOR XML PATH", "FOR XML EXPLICIT" and "FOR XML AUTO" but the data is never structured with the correct heirarchy.</p>
  
  <p>Basically, I have one parent table (Customers) and 3 child tables. Each table has a customerid column. There is a one-to-many relationship from the Customers table to each of the 3 child tables.</p>
  
  <p>As a mock example, I have a parent "Customers" table, and I have 3 other tables - Products, Hobbies and Vehicles - all related to the Customers table by a customerid.</p>
  
  <p>What is the SQL code to achieve the following kind of structure -</p>
  
  <pre><code>&lt;Customers&gt;
      &lt;Customer customerid="1" name="Fred"&gt;
         &lt;Products&gt;
             &lt;Product productname="table" /&gt;
             &lt;Product productname="chair" /&gt;
             &lt;Product productname="wardrobe" /&gt;
         &lt;/Products&gt;
         &lt;Hobbies&gt;
             &lt;Hobby hobbyname="Golf" /&gt;
             &lt;Hobby hobbyname="Swimming" /&gt;
         &lt;/Hobbies&gt;
         &lt;Vehicles&gt;
             &lt;Vehicle name="Car" color="Red" /&gt;
             &lt;Vehicle name="Bicycle" color="Blue" /&gt;
         &lt;/Vehicles&gt;
      &lt;/Customer&gt;
      &lt;Customer customerid="2" name="Sue"&gt;
         &lt;Products&gt;
             &lt;Product productname="CD player" /&gt;
             &lt;Product productname="Picture frame" /&gt;
         &lt;/Products&gt;
         &lt;Hobbies&gt;
             &lt;Hobby hobbyname="Dancing" /&gt;
             &lt;Hobby hobbyname="Reading" /&gt;
         &lt;/Hobbies&gt;
         &lt;Vehicles&gt;
             &lt;Vehicle name="Car" color="Yellow" /&gt;
         &lt;/Vehicles&gt;
      &lt;/Customer&gt;
  &lt;/Customers&gt;
  </code></pre>