title: Generate XML in proper syntax from SQL Server table
body: <p>How to write a SQL statement to generate XML like this</p>
  
  <pre><code>&lt;ROOT&gt;
    &lt;Production.Product&gt; 
       &lt;ProductID&gt;1 &lt;/ProductID&gt;
       &lt;Name&gt;Adjustable Race&lt;/Name&gt;
       ........
    &lt;/Production.Product&gt;
  &lt;/ROOT&gt;
  </code></pre>
  
  <p>Currently I am getting this with </p>
  
  <pre><code>SELECT * FROM Production.Product
      FOR XML auto
  </code></pre>
  
  <p>Result is: </p>
  
  <pre><code>&lt;ROOT&gt;
     &lt;Production.Product ProductID="1" Name="Adjustable Race" 
         ProductNumber="AR-5381" MakeFlag="0" FinishedGoodsFlag="0" 
         SafetyStockLevel="1000" ReorderPoint="750" StandardCost="0.0000" 
         ListPrice="0.0000" DaysToManufacture="0" SellStartDate="1998-06-01T00:00:00" 
         rowguid="694215B7-08F7-4C0D-ACB1-D734BA44C0C8" 
         ModifiedDate="2004-03-11T10:01:36.827" /&gt;
  </code></pre>