SQL 301–FOR XML PATH – shannonlowder.com

OK, I’ve covered FOR XML RAW for simple quick XML outputs. Then I covered FOR XML AUTO, while it automatically handles simple hierarchical data, it’s still pretty limited. And last time I covered FOR XML EXPLICIT, which is the end all – be all when it comes to building XML exactly the way you want to. But Microsoft felt there needed to be an easier way to construct XML.

Enter the ~~Dragon~~ PATH. The idea was you could build your XML structure using an XPath-like method. Let’s look at a an example of a FOR XML PATH query.

SELECT
    c.ContactID AS "@ID" 
    ,c.FirstName + ' ' + c.LastName AS "@FullName" 
    ,c.rowguid AS "node()"
    ,CAST('<XMLExample/>' AS XML) AS "node()" 
    ,c.RowGuid AS "comment()" 
    ,e.SalariedFlag AS "JobInfo/@Salaried" 
    ,e.Title AS "JobInfo/text()" 
FROM Person.Contact AS c 
INNER JOIN HumanResources.Employee e
    on c.ContactID = e.ContactID
WHERE 
    c.LastName = 'Skywalker'
FOR XML PATH('Employee'), ROOT('Employees');

Ok, I’ll be honest, I’m not an authority on XPath. XPuath is considered part of the .NET Framework… that puts it a bit outside my specialty. But let’s go through the code and I can explain it to you.

Unlike XML EXPLICIT, the declaration for the first two nodes, Employees and Employee, are built at the bottom, see line 14. This makes it more like XML RAW or AUTO than EXPLICIT. You declare the PATH your data will be in as well as you wrap your results in a ROOT node on line 14.

With those two nodes set up, we move on to setting up attributes of the first node.

We’re going to set up two attributes for every Employee, ID and Full Name. You can see I pull ContactID from Person.Contact to populate ID. Then I concatenate FirstName and LastName from Person.Contact to fill in the FullName Attribute.

Next I wanted to show you the node() function, you can pass text, or xml to a node() alias, if you just pass text (or something that converts to text ie an INT would work, and be added as text to the XML) It will simply add the text in for that node. That’s what I’m doing on line 4.

However, you can pass fully formed XML to node() alias and it would add that XML fragment to your xml output. That’s what I’m doing on line 5.

Next I wanted to show you how to add a comment, you select some data and alias it as “comment()”. The interpreter will then add comments to your XML output.

On lines 7 and 8 I show you how to create data in child nodes. In this case the child data is information from HumanResources.Employee. I called it JobInfo. I wanted to include whether or not the employee was salaried in an attribute called Salaried, and then I wanted to add the title the employee had as text using the alias text().

You can interchange all these aliases as you wish. It’s supposed to make building XML easier.

Let’s look at the results.

<Employees>
  <Employee ID="25" FullName="Luke Skywalker">
    4865D7B7-5961-43CA-AD25-9F77D9D5E4A8
    <XMLExample />
    <!--4865D7B7-5961-43CA-AD25-9F77D9D5E4A8-->
    <JobInfo Salaried="0">Marketing Assistant</JobInfo>
  </Employee>
</Employees>

Hrm, I could have swore we hired on Luke as a Jedi Master, tasked with teaching the next generation of padawans in the ways of The Force. I’ll have to talk this over with HR.

FOR XML PATH is nice and all, but I just feel more comfortable with XML EXPLICIT…for now. The up side to this method is you can add comments and other types of XML constructs to the output. If you’re asked about adding constructs other than elements, nodes, or attributes, they’re probably asking you to use FOR XML PATH.

If you have any questions for me, please send them in. I’m here to help. Luke is here to rescue you…not assist with marketing.

Leave a Reply Cancel reply