Bak2Basics: Learn T-SQL - FOR XML

Posted on 2/19/2007 @ 9:12 PM in #Bak2Basics by | Feedback | 31064 views

 

So if you'd ask me one of the most under-used and useful features of TSQL, I'd have to say "FOR XML" queries. I don't quite understand why enough people don't use 'em, because they truly are very useful, so I figured, hey why not write up a blog post on what the heck they are, so we could then decide why do you use, or not use them??

So here we go.

So FOR XML Queries are written as shown below -

 

FOR XML mode [, XMLDATA][, ELEMENTS][, BINARY BASE64]

 

There are 4 permissible FOR XML Modes -

a) RAW
b) AUTO
c) PATH (New in SQL2k5)
d) EXPLICIT

So FOR XML RAW is the most basic form. If you execute it, it will simply try and return you a bunch of <row> elements, there is no hierarchy per se. Here's an example runnin' on AdventureWorks (SQL2k5)

SELECT
   Loginid, Title,
   Humanresources.Department.Departmentid, Humanresources.Department.Name
FROM
   Humanresources.Employee
   INNER JOIN Humanresources.Department ON
   Humanresources.Employee.Departmentid = Humanresources.Department.Departmentid
WHERE Humanresources.Department.Departmentid = 7
FOR XML RAW


The output this query will produce looks like as shown below -

<row LOGINID="adventure-works\guy1" TITLE="Production Technician - WC60" DEPARTMENTID="7" NAME="Production" />
<row LOGINID="adventure-works\jolynn0" TITLE="Production Supervisor - WC60"
DEPARTMENTID="7" NAME="Production" />
<row LOGINID="adventure-works\ruth0" TITLE="Production Technician - WC10"
DEPARTMENTID="7" NAME="Production" />
<row LOGINID="adventure-works\barry0" TITLE="Production Technician - WC10"
DEPARTMENTID="7" NAME="Production" />
...

 

The bad news? This XML is like .. umm .. tabular data munged into XML, which is like .. WTF .. it ain't useful. Except of course if you ran XSLT on it, and I've met very few developers who like doing XSLT. Most guys would rather make love to this lady with pink titties rather than to fix the above using XSLT (I agree, fixing the above can get very painful).


So the other option is FOR XML AUTO. FOR XML AUTO exploits the hierarchical nature of SQL Queries. So each table associated with FOR XML AUTO query is represented as an XML element. So for instance, check the query out below (again on AdventureWorks) -

 

SELECT
   Humanresources.Department.Departmentid, Humanresources.Department.Name,
   Humanresources.Employee.Loginid, Humanresources.Employee.Title
FROM
   Humanresources.Department
   INNER JOIN Humanresources.Employee On
   Humanresources.Department.Departmentid = Humanresources.Employee.Departmentid
WHERE Humanresources.Department.Departmentid = 7
FOR XML AUTO


When you run the above query, SQL Server will take advantage of the fact that there is only one DepartmentID, and logically it will nest various employees that appear under this department id. The results are as shown below.

 

<HUMANRESOURCES.DEPARTMENT DEPARTMENTID="7" NAME="Production">
  <HUMANRESOURCES.EMPLOYEE LOGINID="adventure-works\guy1" TITLE="Production Technician - WC60" />
  <HUMANRESOURCES.EMPLOYEE LOGINID="adventure-works\jolynn0" TITLE="Production Supervisor - WC60" />
  <HUMANRESOURCES.EMPLOYEE LOGINID="adventure-works\ruth0" TITLE="Production Technician - WC10" />
  ...
  ...
  ...
  <HUMANRESOURCES.EMPLOYEE LOGINID="adventure-works\olinda0" TITLE="Production Technician - WC20" />
  <HUMANRESOURCES.EMPLOYEE LOGINID="adventure-works\tom0" TITLE="Production Technician - WC10" />
</HUMANRESOURCES.DEPARTMENT>


The criterion SQL Server follows is that the columns mentioned first, are attempted to be grouped together first. For example, the above mentioned query takes advantage of the fact that there is only one DepartmentID, and hence it uses the FOR XML AUTO directive to specify that all results should be grouped under the particular DepartmentID. If instead you had written the query as below.

SELECT
   Humanresources.Employee.Loginid, Humanresources.Employee.Title,
   Humanresources.Department.Departmentid, Humanresources.Department.Name
FROM
   Humanresources.Department
   INNER JOIN Humanresources.Employee On
   Humanresources.Department.Departmentid = Humanresources.Employee.Departmentid
WHERE Humanresources.Department.Departmentid = 7
FOR XML AUTO


The results you would have got would have been in the form below.

...
<HUMANRESOURCES.EMPLOYEE LOGINID="adventure-works\guy1" TITLE="Production Technician - WC60">
  <HUMANRESOURCES.DEPARTMENT DEPARTMENTID="7" NAME="Production" />
</HUMANRESOURCES.EMPLOYEE>
...


Now ain't that cool ;-).

No that ain't cool, it's not ultra useful as you can clearly guess, because you ain't got much flexibility boy. So the ultra powerful version of FOR XML is FOR XML EXPLICIT. This form, gives you a WHOLE LOTTA CONTROL. I mean, you can pretty much munge any relational structure to any hierarchical structure - elements, attributes, entity encoding, CDATA sections - the whole burrito/enchilada take your pick.

So the following snippet from a FOR XML EXPLICIT query’s SELECT clause demonstrates how the DepartmentID and Name from AdventureWorks HumanResources.Department table can be specified as either an attribute or an element within the same XML document:

 

SELECT
   1 AS TAG,
   NULL AS PARENT,
   DEPARTMENTID AS [DEPARTMENT!1!DEPARTMENTID],
   NAME AS [DEPARTMENT!1!NAME!ELEMENT]
FROM
   HUMANRESOURCES.DEPARTMENT
FOR XML EXPLICIT


Ignoring the Tag and Parent parts of this query for now, the alias following the first instance of DepartmentID contains no directive, so it’s treated as an attribute (the default). However, the element directive in the alias following the second Name column causes that column to be represented as an element. I will go into more detail later on, but a portion of the XML generated by the previous SQL is as follows:

<DEPARTMENT DEPARTMENTID="12">
  <NAME>Document Control</NAME>
</DEPARTMENT>
<DEPARTMENT DEPARTMENTID="1">
  <NAME>Engineering</NAME>
</DEPARTMENT>
<DEPARTMENT DEPARTMENTID="16">
  <NAME>Executive</NAME>
</DEPARTMENT>
...

 

The problem is, FOR XML EXPLICIT Queries are UGLY. They are HUGE. And other than the creator, nobody understands their inner logic (I mean, try writing one with 3-4 tables, and you'll see what I'm sayin', the query would just extend over PAGES AND PAGES AND PAGES .. ugghh !!!).

So whadya do? Well in SQL2k you don't have much of a choice, so the answer is you're screwed. But in SQL2k5 - you got FOR XML PATH. (CLAP CLAP !!)

So, FOR XML PATH is a new addition to SQL Server 2005. In fact, most of what FOR XML EXPLICIT lets you do can be done using FOR XML PATH. There are very few scenarios such as CDATA sections where you may still need to use FOR XML EXPLICIT.

For example, if you wished to write the above query in FOR XML PATH, it could easily be written as below.

 

Select 
   DepartmentID "Department/@DepartmentID",
   NAME "Department/Name"
FROM
   HumanResources.Department
FOR XML PATH (
'')


As you can see, this is a simple SELECT query with some XPATH looking syntax after each column. The first Department/@DepartmentID specifies that DepartmentID should appear as an attribute under the department node, and the second “Department/Name” specifies that Name should appear as an element under Department. The results of this query look exactly like the FOR XML EXPLICIT query and are shown as below.

 

<Department DepartmentID="12">
  <Name>Document Control</Name>
</Department>
<Department DepartmentID="1">
  <Name>Engineering</Name>
</Department>
<Department DepartmentID="16">
  <Name>Executive</Name>
</Department>


Thus as you can see, the various modes of FOR XML allow you to easily render a relational and tabular structure into a hierarchical XML structure.

So, now I gotta ask ya !! What's stoppin' u from usin' the above? Huh?

Sound off but keep it civil:

Older comments..


On 2/20/2007 6:21:47 AM Christian Loris said ..
Nice overview. I have not taken the SQL 2K5 plunge yet but this really gets me excited about working with the XML features. Thanks!


On 11/7/2007 2:46:32 PM Queztor said ..
Well Done! I am trying to decipher a xml query on 2000. The developer that wrote it left and now I am stuck with it. The bugger is over 1000 Lines!!!


On 11/8/2007 10:06:33 AM Sahil Malik said ..
LOL - did ya manage to fix it?


On 4/15/2008 3:44:36 PM yosi said ..
Wow wow wow wow awesome..now how can I grab the xml result as a document object I can use to populate a table for example?

bi U ee fu


On 4/17/2008 6:00:41 AM Kim.Li said ..
Excellent!


On 4/23/2008 4:40:22 PM Ahmed Galal said ..
Good job man, nice explantions, btw this lady with pink titties worth it , lol


On 4/23/2008 6:07:33 PM Sahil Malik said ..
LOL Ahmed, you perv. You clicked on it, didn't you?


On 7/30/2008 3:21:06 PM Suchil said ..
Nice introduction to XML in T-SQL


On 9/13/2008 6:26:30 AM Frank said ..
Now this is the way to do tutorials. You should write a book. I soaked that up like a sponge.


On 9/13/2008 1:03:52 PM Sahil Malik said ..
Thanks Frank - y'know .. I do write books? :) .. but they are so much work and don't pay anything.


On 9/25/2008 11:01:51 AM Ather Idrees said ..
Dear Sahil,


Such an excelllent article. I have added it to my favourite list.


On 10/13/2008 1:36:30 AM zardoz said ..
This article is "Teach yourself SQL Server XML in 21 seconds". Great work!


On 10/13/2008 2:59:40 AM Sahil Malik said ..
21 seconds? Damn you read fast!


On 1/14/2009 11:34:52 AM Ann said ..
Great article! Very well explained. Thanks you for make things simpler!


On 3/5/2009 11:35:08 AM VM said ..
Great "Get To The Point" article.


Back to my seach. How the hell do I feed a SQL Server XML to a YUI Datatable ??!!


Thanks


On 3/23/2009 6:23:31 AM grateful said ..
The best post I've read in ages. Thank you!!! and yes, I click on the link about the pink titties as well.


On 4/22/2009 1:28:31 AM Mazhar Karimi said ..
Great Work Sahil! The way you explained this complicated topic in a way that made easy for us to understand is awesome! Keep it Goin Please :).


On 4/22/2009 12:07:05 PM Sashidhar Kokku said ..
Is there any way we can read from an xml into a table (tmp table) directly ..basically reverse of "for xml"


On 7/27/2009 7:13:42 AM Sayan Ghosh said ..
Nice one, but FOR XML is quite very slow when dealing with huge data, say table having over a million rows. Under such circumstances, XML DML is a great savior. It could also be used to achieve what Sashidhar wants to do....I guess it provides great flexibility.

http://www.15seconds.com/Issue/050803.htm


On 12/17/2009 11:46:18 AM JG said ..
The best post ever. Did I mention this is the best post ever? Ok now I've said that how the hell do I get my xml column to a file without using xp shell command since it's restricted on the server.

Any takers?


On 2/18/2010 8:16:24 PM Tony said ..
Great stuff. Thank you for taking the time.


On 4/8/2010 11:11:40 PM James Crossley said ..
Thank you, thank you, thank you!


On 4/13/2010 7:09:24 AM sql-developer said ..
Thanks for the brief entry for the XML capabilities of the new tsql


On 6/14/2010 1:46:55 PM Laura said ..
Thank you for the technical information in this post. However, the "pink titties" comment is irrelevant, unprofessional, and sexist. When you say, "Most guys would rather...", you assume that there are no women in your intended audience and create a sexualized environment that is unwelcoming to women.

I suggest that you read http://geekfeminism.wikia.com/wiki/Feminism_101.


On 6/15/2010 2:05:50 AM Sahil Malik said ..
Here we go again. I just can't be funny these days without offending someone.


On 7/28/2010 11:51:04 AM Mark said ..
Then to be safe I suggest you offend everyone.

PS I have pink titties too. I wasn't offended. But then I'm a man. I guess I must offend women, cos surely pink titties should only belong to a woman.


On 7/28/2010 12:52:13 PM Sahil Malik said ..
HAHAHA! Mark, you're awesome! Well, I don't want to offend anyone, but at the same time, people gotta learn how to loosen up a bit.


On 10/13/2010 4:08:32 PM Lorraine said ..
The article's technical content was great. The author is obviously a smart man, and I trust very capable of generating engaging humor without the sexism. I completely agree with 6/14/2010 1:46:55 PM Laura; the tittie comments are very offensive. I would rate as best tech writers who can serve up engaging commentary without putting down any members of the audience. A suggestion for you would-be leaders of the tech community - please ask yourselves 'Would Bill Gates say this?'


On 11/19/2010 3:12:05 AM Ville Vainio said ..
This looks great. But...

> So, now I gotta ask ya !! What's stoppin' u from usin' the above? Huh?

At the moment, this is:


- The FOR XML clause is not allowed in a ASSIGNMENT statement.


- The FOR XML clause is not allowed in a INSERT statement.

What I'm trying to do, is create a rather large xml -file from fragments (around 2GB from maybe 50 tables). This would be great using the FOR XML -syntax, but I just don't see it happening in one select. And if storing the result to variables or temporary tables is not an option, what is there left for me, except coding the xml by hand (parametrized obviously).


On 1/18/2011 10:15:02 AM Mike said ..
Please help, Im a newbie to this and I need to insert an attribute for the parent node?

SELECT(


SELECT


'HR Analysis' AS PositionTitle,


(


SELECT 'Firstname' as GivenName,


'LastName' AS FamilyName


FOR XML PATH('PersonName'),


TYPE


)


FOR XML PATH ('Contact'),


TYPE)


FOR XML PATH (''),


ROOT ('JobPosting')

The contact node needs to have an attribute name="ID" Value="1".


So I tried this but it doesn't work


SELECT(


SELECT


'1' 'Contact/@id',


'HR Analysis' AS PositionTitle,


(


SELECT 'Firstname' as GivenName,


'LastName' AS FamilyName


FOR XML PATH('PersonName'),


TYPE


)


FOR XML PATH ('Contact'),


TYPE)


FOR XML PATH (''),


ROOT ('JobPosting')

This is what i am trying to achive:


<JobPosting>


<Contact id=1>


<PositionTitle>HR Analysis</PositionTitle>


<PersonName>


<GivenName>Firstname</GivenName>


<FamilyName>LastName</FamilyName>


</PersonName>


</Contact>


</JobPosting>


On 6/3/2011 4:20:29 PM Mark #2 said ..
As a male I have to say it was jarring when I first read the bit cited by the women as offensive. It *is* offensive - and I hate political correctness more than most. So unnecessary, and mars an otherwise very good post.


On 3/5/2012 7:36:15 PM Christopher Zahrobsky said ..
Why don't people show queries of more than one column, and things like attributes as in the raw format you get from SQL? Here's my example:

<code>

DECLARE @KeyValues As xml


SET @KeyValues = '<row SettingName="PI" SettingValue="3.141592653589793238462643383279502884197169399375" /><row SettingName="SqRt2" SettingValue="1.4142135623730950488016887242" /><row SettingName="FeetPerMile" SettingValue="5280" />'

select ParamValues.Row.value('@SettingName','NVARCHAR(20)') as [key]


, ParamValues.Row.value('@SettingValue','float') as [setting]


FROM @KeyValues.nodes('/row') as ParamValues(Row)

</code>

Enjoy!


On 1/22/2013 2:45:25 PM Cory said ..
To be frank (or should I use a female equivalent) "This lady with pink titties" is a pig NOT woman, so why is a woman being offended? He is speaking from a Mans perspective, it is a female pig hence the term "lady". Women are free to not want to make love to the pig also! If he said most people instead of most guys would that be ok? Or is it the sight of "lady" and "titties" in close proximity that has pinched a nerve.


My advice - (take it or leave it. (most may leave it)) is ... Feeling offended doesn't mean your right to feel offended so think before you comment.