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 names 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.
: