SQL FOR XML - Outputting data as Elements or as Attributes

Question

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.

The customer has requested data on each product like so:

<product id="1234567890123">
    <activeState partNumber="A1234567890" shipmentDate="20110518" />
</product>

My existing SQL is:

SELECT SerialNo as id, 
    PartNo as partNumber,
    CONVERT(VARCHAR(8), GETDATE(), 112) AS shipmentDate, 
FROM Products
WHERE SerialNo = @SerialNo
FOR XML PATH ('product'), TYPE)

...which renders:

<product>
  <id>100000000458</id>
  <partNumber>10004905892</partNumber>
  <shipmentDate>20120312</shipmentDate>
</product>

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

Any suggestions?

Solution

Use @ to create attributes.

select SerialNo as "@id",
       PartNo as "activeState/@partNumber",
       convert(varchar(8), getdate(), 112) as "activeState/@shipmentDate"
from Products
where SerialNo = @SerialNo
for xml path('product')