title: SQL FOR XML - Outputting data as Elements or as Attributes
body: <p>I'm trying to export some data from a database in a particular XML format specified by the customer. The XML I produce will be manipulated (presumably by XSLT) by a 3rd party to produce the final output, but I want to formal my XML as close as I can to that format.</p>
  
  <p>The customer has requested data on each product like so:</p>
  
  <pre><code>&lt;product id="1234567890123"&gt;
      &lt;activeState partNumber="A1234567890" shipmentDate="20110518" /&gt;
  &lt;/product&gt;
  </code></pre>
  
  <p>My existing SQL is:</p>
  
  <pre><code>SELECT SerialNo as id, 
      PartNo as partNumber,
      CONVERT(VARCHAR(8), GETDATE(), 112) AS shipmentDate, 
  FROM Products
  WHERE SerialNo = @SerialNo
  FOR XML PATH ('product'), TYPE)
  </code></pre>
  
  <p>...which renders:</p>
  
  <pre><code>&lt;product&gt;
    &lt;id&gt;100000000458&lt;/id&gt;
    &lt;partNumber&gt;10004905892&lt;/partNumber&gt;
    &lt;shipmentDate&gt;20120312&lt;/shipmentDate&gt;
  &lt;/product&gt;
  </code></pre>
  
  <p>I expect that it is easy enough to manipulate this data in XSLT, but purely as an intellectual exercise, I'd like to see how far I could in SQL. My first ambition was to simply express the id as an attribute of product rather than as a child element. The rendering of the activeState element I was going to leave to the XSLT, but clearly, if I can help them on their way, why not do so...</p>
  
  <p>Any suggestions?</p>