Geeks With Blogs
Sachin R. Chavan's - BizTalk Blog

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.

 

Posted on Monday, April 30, 2007 5:52 PM | Back to top


Comments on this post: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
This was pretty useful...in reality that very last comment explaining the attributes vs. element generation. Short, sweet, to the point.
Left by Humberto on Mar 30, 2009 11:38 PM

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
Thanks so much for posting this. Very helpful!
Left by Chad on Jun 08, 2009 3:02 PM

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
thanks for the great great useful information
Left by Abo Anas Elmasry on Dec 08, 2009 10:35 PM

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
That was very helpful. Needed to wrap bunch of columns in a node and then wrap all those nodes in a root node;
select whatever, then FOR XML PATH('node'), ELEMENTS, ROOT('Root').
Left by Chakri Bireddy on May 14, 2010 5:14 PM

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
it was very use full
Left by biju on May 27, 2010 2:42 AM

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
Thanks for ur information. Its very useful.
Left by Hariharan S on Sep 20, 2010 12:50 AM

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
Crisp and Clean
Left by Palanivel on Dec 08, 2010 11:52 PM

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
qweww eqeqwqweqw ewq eqw eqweeqweqw eqwewq
Left by eqwewqeewqeqweqwew on Mar 11, 2011 5:03 AM

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
I am using a query with XML PATH but the result set is restricted with 2034 characters and I get records are count = 3 where as I expect single record
Left by Anand Parmar on Aug 02, 2011 12:05 PM

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
This stuff works on the server, but when I want to use SQLcmd and want save it as a xml file the file does not display the xml structure, but characters.
"0x44087600610072006900610062006C00650......."
How can I save the XML structure to a file?
Left by Katrin on May 25, 2012 6:01 AM

# re: FOR XML PATH - A New Mode in FOR XML with SQL Server 2005.
Requesting Gravatar...
nice n easy to understand.
I`ve 1 query that is

if want to generate xml path like
<rootname>
<innerrootname>
<customer>
abc
</customer>
</innerrootname>
</rootname>


plz help me out ...
Left by vik on Nov 21, 2012 11:14 PM

Your comment:
 (will show your gravatar)


Copyright © Sachin R. Chavan | Powered by: GeeksWithBlogs.net | Join free