OK, so far I’ve covered FOR XML RAW. It’s a pretty simple way to crank out some XML when you need to. FOR XML AUTO has better support for hierarchies, but it too has it’s limits. The end all be all for extensibility is FOR XML EXPLICIT. It actually supports some options that you can’t even do with FOR XML PATH.
The real difficulty with working with FOR XML EXPLICIT comes from the fact you have to build your result set in a specific way; you have to name the columns in a specific way; finally, you must also order your results in a specific way to get the results you’re looking for. Let’s dig into this a bit more.
First off, your results must always contain columns called tag and parent. The tag column holds an integer for each xml element you want to return. Parent refers to the tag you want the current tag to belong to… as in a parent – child relationship. When you want the element to be the top most result (it doesn’t have a parent) you use NULL as the parent value.
Let’s build an XML showing off Customers and their orders in the Adventureworks database.
1: SELECT
2: 1 AS Tag
3: ,NULL AS Parent
4: ,NULL AS "Customers!1!!element"
5: ,NULL AS "Customer!2!ID"
6: ,NULL AS "Customer!2!AccountNumber"
7: ,NULL AS "Order!3!ID"
8: ,NULL AS "Order!3!OrderDate"
9: UNION ALL
10: SELECT
11: 2 AS Tag
12: ,1 AS Parent
13: ,NULL AS "Customers!1!!element"
14: ,c.CustomerID AS "Customer!2!ID"
15: ,c.AccountNumber AS "Customer!2!AccountNumber"
16: ,NULL AS "Order!3!ID"
17: ,NULL AS "Order!3!OrderDate"
18: FROM Sales.Customer AS c
19: WHERE c.CustomerID IN (1,2)
20: UNION ALL
21: SELECT
22: 3 AS Tag
23: ,2 AS Parent
24: ,NULL AS "Customers!1!!element"
25: ,soh.CustomerID AS "Customer!2!ID"
26: ,NULL AS "Customer!2!AccountNumber"
27: ,soh.SalesOrderID AS "Order!3!ID"
28: ,soh.OrderDate AS "Order!3!OrderDate"
29: FROM Sales.SalesOrderHeader AS soh
30: WHERE soh.CustomerID IN (1,2)
31: ORDER BY "Customer!2!ID", Tag
32: FOR XML EXPLICIT;
Let’s go through this query taking it a piece at a time. The first sub query in my whole query sets up the basic structure. For the first element ( or root element) we want Customers, to do that, we set up 1 as the tag (that’s like 2), NULL as the parent (on line 3), then we name it Customers, and mark it to be an element (on line 4). Customers has no attributes, so the “element” tells the interpreter just make the element, there aren’t going to be any attributes.
Now, for the second element, Customer. We want to set it up to have two attributes, ID, and AccountNumber. So we build line 5 to create The ID attribute for Customer, and line 6 builds the AccountNumber attribute for Customer.
Finally we build the 3rd element, Order. It has two attributes, ID and OrderDate. So we build those on lines 7 and 8.
You should get the idea of XML construction using FOR XML EXPLICIT. You build the Elements and Attributes with “ElementName!Tag!AttributeName (if applicable, otherwise just element).
Make sense?
Let’s move on to the second sub query. This one builds the data for the Customer elements. Since Customer is the second element, we choose 2 as Tag ( in line 11). Customer is the child element of Customers (tag 1), so we enter 1 as the parent (line 12). Since we’re populating data for element 2, we aren’t going to enter data for the Customers element, so we want to skip that for this query…enter NULL for the customer’s element’s data (line 13).
Now we want to populate the data for Customer!2!ID and Customer!2!AccountNumber. You can see in lines 18 and 19, we’re querying the Sales.customer Data (and limiting it to the first two customers), and we fill in the data on lines 14 and 15. We put CustomerID from the Sales.Customer table into the attribute Customer!2!ID, and we put AccountNumber from Sales.Customer into the Customer!2!AccountNumber Attribute.
Again, since this sub query only deals with Customer Information, we pass NULL for the two Order Attibutes.
Now, for the final query. We’re going to fill in Order attributes in this query, so we mark these results as Tag 3, 2 is the parent this time (the customer element), The Customer’s element is still null here, but this time we are going to fill in the CustomerID.
The reason we do that is so the ordering is going to work later. If we don’t put in a value for CustomerID in this last query, the results won’t come out right. Remember I said your results had to be ordered in a specific way? This is what I was referring to. Try this change line 25 to NULL as “Customer!2!ID”. What do you get?
1: Msg 6833, Level 16, State 1, Line 1
2: Parent tag ID 2 is not among the open tags. FOR XML EXPLICIT requires parent tags to be opened first. Check the ordering of the result set.
3:
This error is basically telling you it can’t order the results in such a way that it can construct your XML. When I’ve seen it, it’s usually because I’ve failed to relate one sub query to the rest. Look for this when you get that error, and you’ll be fine.
Now, since we’re in the third sub query, we’ll be populating Order!3!ID and Order!3!OrderDate, so we fill those in on lines 27 and 28, using Sales.SalesOrderHeader.
Finally, on line 31, we mark the order we want to build this XML, we want to order by Customer!2!CustomerID (even though in the third sub query we get the customer ID from SalesOrder Header). After sorting by CustomerID, we sort by Tag, so the hierarchy comes out the way we want.
After that, all you have to do is tell the interpreter you want this rendered in XML Explicit mode, and viola, you’re done!
You’ll get a response that looks something like this.
1: <Customers>
2: <Customer ID="1" AccountNumber="AW00000001">
3: <Order ID="43860" OrderDate="2001-08-01T00:00:00" />
4: <Order ID="44501" OrderDate="2001-11-01T00:00:00" />
5: <Order ID="45283" OrderDate="2002-02-01T00:00:00" />
6: <Order ID="46042" OrderDate="2002-05-01T00:00:00" />
7: </Customer>
8: <Customer ID="2" AccountNumber="AW00000002">
9: <Order ID="46976" OrderDate="2002-08-01T00:00:00" />
10: <Order ID="47997" OrderDate="2002-11-01T00:00:00" />
11: <Order ID="49054" OrderDate="2003-02-01T00:00:00" />
12: <Order ID="50216" OrderDate="2003-05-01T00:00:00" />
13: <Order ID="51728" OrderDate="2003-08-01T00:00:00" />
14: <Order ID="57044" OrderDate="2003-11-01T00:00:00" />
15: <Order ID="63198" OrderDate="2004-02-01T00:00:00" />
16: <Order ID="69488" OrderDate="2004-05-01T00:00:00" />
17: </Customer>
18: </Customers>
This is the mode I use most often when I have to build the results in a VERY specific way. Otherwise I usually will fall back to raw just to see the output in XML.
It may take a little bit to get this under your belt, I will admit, I don’t use XML output every day, when I do, I usually have to hit up a reference to make sure I get it just right. But that won’t help you in the 70-433. Practice with this, try to break it. Really dig into it…you’ll get this in no time.
Next time, we’ll cover the FOR XML PATH option. It’s supposed to make building XML result sets easier. I’ll leave that to you to judge… As usual if you have any questions, please send them in. I’m here to help.