Last week I was searching for a solution to generate a complicated XML straight away out of the SQL Server. Actually, I had huge data scattered in few tables that I wanted to map to an XML with several attributes n elements with multiples levels of hierarchy, I was trying to generate this XML with the FOR XML Explicit mode. Doing things with this was really really complicated n terrible. So, I started to look around for new XML features introduced with SQL Server 2005 (as I was using SQL Server 2005 for this).
While browsing through MSDN pages I came around this new mode 'FOR XML PATH' introduced with SQL Server 2005. This is really a cool feature and gives you the ability to generate the complex XML with a greater ease, with this feature I was safe home and was able to generate the XML in desired format with a great ease. Thank God!!!
The new PATH mode allows you to use an XPath-like syntax as a column name which then is mapped into an attribute or element or sub element of the XML as per you specify and you can have multiple level of hierarchy and can mix elements n attributes. The problem with FOR XML AUTO n RAW modes with SQL Server 2000 was that you can have either the attributes or the elements but not both. If you want to have mixed content in XML you have to go for XML Explicit mode which was too complex n for generating hierarchy you have to Union lot of SQL Statements so, it sometimes, used to correctly called as the "query from hell".
With the Introduction of the FOR XML Path Mode things can be as easy as following:
SELECT CustomerID as "@CustomerID",
CompanyName,
Address as "address/street",
City as "address/city",
Region as "address/region",
PostalCode as "address/zip",
Country as "address/country",
ContactName as "contact/name",
ContactTitle as "contact/title",
Phone as "contact/phone",
Fax as "contact/fax"
FROM Customers
FOR XML PATH('Customer')
This will give you the following result:
<Customer CustomerID="ALFKI">
<CompanyName>Alfreds Futterkiste</CompanyName>
<address>
<street>Obere Str. 57</street>
<city>Berlin</city>
<zip>12209</zip>
<country>Germany</country>
</address>
<contact>
<name>Maria Anders</name>
<title>Sales Representative</title>
<phone>030-0074321</phone>
<fax>030-0076545</fax>
</contact>
</Customer>
...
If you analyze down the results you can figure out that the column you specify as "@" are resulted in attributes e.g. customer ID, the column with no alias is resulted in element e.g. company name. Now the columns which you want to be embedded in some tag can be also generated using syntax 'element1/element2' e.g. for city column we have specified 'address/city'. It is this simple to generate the mixed kind of XML with this new mode.
There's a lot to it, lot many features and options available, for complete details visit the following links:
http://msdn2.microsoft.com/en-us/library/ms345137.aspx
http://sqljunkies.com/WebLog/mrys/archive/2004/02/19/1208.aspx
Hope you get benefited with this.