SQL301-Nesting For XML Queries

Don't mess with their nest!
Don't mess with their nest!

Given the 4 options I’ve shown you for creating XML output from SQL, you should be able to create nearly any structure you’d want (especially considering FOR XML EXPLICIT).  But you may actually c0me across a scenario where you can’t pull off what you want in a single query.

When  you can’t make a single query return the results you want, you can always nest one FOR XML query within another. This goes back to the basics of building one related sub query within an outer query.  The difference here is the output is an XML fragment rather than a data grid.  Let’s look at a simple example of this situation.

Let’s say you want to create xml representing customers and their orders.  For each customer element you want three attributes, name, account number, and type.  For each of their orders you want the order ID and order date (from Sales.SalesOrderHeader).  Finally, for each order you want the product name and quantity ordered.

Let’s get started, as usual, I’m working in my 2008R2 instance of SQL Server.

   1: USE Adventureworks

   2: go

   3: SELECT

   4:     contact.FirstName + ' ' + contact.LastName AS "@Name"

   5:     ,customer.AccountNumber AS "@AccountNumber"

   6:     ,customer.CustomerType AS "@Type"

   7:     ,(

   8:         SELECT

   9:             soh.SalesOrderID AS "@Id"

  10:             ,soh.OrderDate AS "@OrderDate"

  11:             ,(

  12:                 SELECT

  13:                     p.Name AS "@ProductName"

  14:                     ,sod.OrderQty AS "@Quantity"

  15:                 FROM Sales.SalesOrderDetail AS sod

  16:                 INNER JOIN Production.Product p

  17:                     ON sod.ProductID = p.ProductID

  18:                 WHERE sod.SalesOrderID = soh.SalesOrderID

  19:                 FOR XML PATH('OrderDetail'), TYPE

  20:             )

  21:         FROM Sales.SalesOrderHeader AS soh

  22:         WHERE soh.CustomerID = customer.CustomerID

  23:         FOR XML PATH('Order'), TYPE

  24:     ) AS "Orders"

  25: FROM Sales.Customer AS customer

  26: INNER JOIN Person.Contact AS contact

  27:     on customer.CustomerID = contact.ContactID

  28: WHERE customer.CustomerID = 1

  29: FOR XML PATH('Customer');

I start out my query by getting the customer name, account number and customer type from the Sales.Customer table, joined to the Person.ContactID where the customerID matches ContactID.

I then move to my first related sub query.  I query sales order ID and order date from the Sales.SalesOrderHeader table, I relate the results of this query to the outer query where SalesOrderHeader.CustomerID matches the Customer.CustomerID.  Then in my inner most, and last query, I get the product Name and order quantity from the Sales.SalesOrderDetail table Joined to the Production.Product table on productID.

I then join the inner most query to the sub query just before it by relating the SalesOrderID from the Sales.SalesOrderDetail table to the SalesOrderID from the Sales.SalesOrderHeader table.

This gives us the following result on my server.

   1: <Customer Name="Gustavo Achong" AccountNumber="AW00000001" Type="S">

   2:   <Orders>

   3:     <Order Id="43860" OrderDate="2001-08-01T00:00:00">

   4:       <OrderDetail ProductName="Road-650 Red, 62" Quantity="2" />

   5:       <OrderDetail ProductName="Road-650 Black, 52" Quantity="1" />

   6:     </Order>

   7:     <Order Id="44501" OrderDate="2001-11-01T00:00:00">

   8:       <OrderDetail ProductName="Road-650 Red, 62" Quantity="1" />

   9:     </Order>

  10:   </Orders>

  11: </Customer>

You may get different results, but they will be in the same format.

Just like normal queries you should be careful when using sub queries.  If you use them in the wrong place, you could have  a significantly higher costing query.  Keep an eye on the execution plans for your queries, and you’ll be ok.

So, any questions?  It makes sense right?  Building on the ideas of sub queries and throwing on the FOR XML PATH option?  Let me know if you have any questions.  I’ll do my best to explain!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

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