New SQL to XML in SQL Server 2005, no more FOR XML EXPLICIT

Did you ever try to spit out XML data from a SQL database? Of course you can use the “FOR XML AUTO” clause but it will throw you something like this:

SELECT TOP 6 ProductID, ProductName, UnitPrice
FROM Products 
FOR XML EXPLICIT

<root>
	<Products ProductID="1" ProductName="Chai" UnitPrice="18.0000" />
	<Products ProductID="2" ProductName="Chang" UnitPrice="19.0000" />
	<Products ProductID="3" ProductName="Aniseed Syrup" UnitPrice="10.0000" />
	<Products ProductID="4" ProductName="Chef Anton's Cajun Seasoning" UnitPrice="22.0000" />
	<Products ProductID="5" ProductName="Chef Anton's Gumbo Mix" UnitPrice="21.3500" />
	<Products ProductID="6" ProductName="Grandma's Boysenberry Spread" UnitPrice="25.0000" />
</root>

With “FOR XML EXPLICIT” you can have a little more control:

SELECT TOP 6 
	1 AS Tag, 
	NULL AS parent, 
	ProductID AS [Product!1!ID], 
	ProductName AS [Product!1!Name!element], 
	UnitPrice AS [Product!1!Price!element]
FROM Products 
FOR XML EXPLICIT

<root>
  <Product ID="1">
    <Name>Chai</Name>
    <Price>18.0000</Price>
  </Product>
  <Product ID="2">
    <Name>Chang</Name>
    <Price>19.0000</Price>
  </Product>
  <Product ID="3">
    <Name>Aniseed Syrup</Name>
    <Price>10.0000</Price>
  </Product>
  <Product ID="4">
    <Name>Chef Anton&apos;s Cajun Seasoning</Name>
    <Price>22.0000</Price>
  </Product>
  <Product ID="5">
    <Name>Chef Anton&apos;s Gumbo Mix</Name>
    <Price>21.3500</Price>
  </Product>
  <Product ID="6">
    <Name>Grandma&apos;s Boysenberry Spread</Name>
    <Price>25.0000</Price>
  </Product>
</root>

But this SQL syntax is a bit odd. It gets worst when you try to add multiple level in your XML output. SQL Server 2005 provides another constrct: “FOR XML PATH”. Here is the same sample:

SELECT TOP 6 
	ProductID AS [@ID], 
	ProductName AS [Name], 
	UnitPrice AS [Price]
FROM Products 
FOR XML PATH('Product')

This will produce the same output as the previous EXPLICIT sample but the syntax is much more understandable.