Returning Tabular Data as XML with FOR XML PATH and Nested XML Queries

FOR XML PATH is the favorite amongst most Database Developers due to its ease of use, and the fact that you can build almost any XML structure with it.

I’ll also be discusing nested XML Queries in this post, which kind of goes hand in hand with FOR XML PATH. More on this later.

I’ll start off with an example, then explain what’s going on afterwards:

SELECT P.Name AS "@Name",
       P.Age AS "@Age",
       'PersonId = ' + CAST(P.PersonId AS VARCHAR(5)) AS "comment()",
       (
        SELECT O.OrderId AS "@OrderId",
               O.Item AS "@Item"
	FROM Orders O
	WHERE O.PersonId = P.PersonId
	FOR XML PATH('Order'), TYPE
       ) 
 
FROM Person P
 
WHERE P.PersonId IN (1, 2)
 
FOR XML PATH('Person'), ROOT('People'), ELEMENTS XSINIL

As with each of the other XML Modes (Raw, Auto, and Explicit), you’re able to change the name of your elements from the default. To achieve this use FOR XML PATH(‘Person’), this will give your rows the name “Person”.

ROOT(‘People’) specifies the root element to be wrapped around you’re XML. The keyword ELEMENTS in this instance doesn’t do a whole lot as the structure is placed into elements and attributes depending on the names given to the columns, but XSINIL cannot be specified without it. XSINIL is used to give you’re XML a namespace, as well as to handle NULL values.

The actual output of the query used aboved is as below:

<People xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Person Name="Zach Stagers" Age="22">
    <!--PersonId = 1-->
    <Order OrderId="1" Item="Bike" />
    <Order OrderId="2" Item="Laptop" />
    <Order OrderId="3" Item="Phone" />
    <Order OrderId="4" Item="Keyboard" />
    <Order OrderId="5" Item="Mouse" />
    <Order OrderId="10" Item="Pizza" />
  </Person>
  <Person Name="Libbie Coulson" Age="24">
    <!--PersonId = 2-->
  </Person>
</People>

The first two columns in the select are specified as “@Name” and “@Age”, this assigns them as attributes to the Person Element.

Specifying the column name as “comment()” makes the value a comment within the XML, note that this is case sensitive – “comment()” is not the same as “Comment()”.

One column name that I haven’t used in the example is “text()”, this sets the value as the text of the element, instead of an attribute.. for example, using this against P.Name turns the value into:

<Person Age="22">Zach Stagers</Person>

Nested XML Queries

Nested XML Queries are what gives the FOR XML PATH mode its structure. To do this, as in the example above, you would use a correlated subquery. Here, you link the subquery to the outer query on the PersonId – linking the person to their orders, and therefore listing the orders beneath the correct person.

The subqueries FOR XML statement must have TYPE specified. This translates the queries results into true XML, Microsoft did this for backwards compatibility reasons.

Digg This
Reddit This
Stumble Now!
Buzz This
Vote on DZone
Share on Facebook
Bookmark this on Delicious
Kick It on DotNetKicks.com
Shout it
Share on LinkedIn
Bookmark this on Technorati
Post on Twitter
Google Buzz (aka. Google Reader)

2 thoughts on “Returning Tabular Data as XML with FOR XML PATH and Nested XML Queries

  1. How can I get top 10 rows in such a nested query which is using FOR XML PATH. For eg., if I want to get only those rows till “Order ID = 4″, or I want to get rows from “Order ID = 5″ to next persons’ orders.
    Is it possible?

  2. Hi Mansi,

    If you’re asking how to return only the top 10 orders, you can add the TOP clause to the sub-query since it’ll be executed for every row.

    You’d want to add an ORDER BY, too, which will determine which are your “top” records.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>