SQL301-Nesting For XML Queries – shannonlowder.com

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.

USE Adventureworks
go
SELECT
    contact.FirstName + ' ' + contact.LastName AS "@Name"
    ,customer.AccountNumber AS "@AccountNumber"
    ,customer.CustomerType AS "@Type"
    ,(
        SELECT
            soh.SalesOrderID AS "@Id"
            ,soh.OrderDate AS "@OrderDate"
            ,(
                SELECT
                    p.Name AS "@ProductName"
                    ,sod.OrderQty AS "@Quantity"
                FROM Sales.SalesOrderDetail AS sod
                INNER JOIN Production.Product p
                    ON sod.ProductID = p.ProductID
                WHERE sod.SalesOrderID = soh.SalesOrderID
                FOR XML PATH('OrderDetail'), TYPE
            )
        FROM Sales.SalesOrderHeader AS soh
        WHERE soh.CustomerID = customer.CustomerID
        FOR XML PATH('Order'), TYPE
    ) AS "Orders"
FROM Sales.Customer AS customer
INNER JOIN Person.Contact AS contact
    on customer.CustomerID = contact.ContactID
WHERE customer.CustomerID = 1
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.

<Customer Name="Gustavo Achong" AccountNumber="AW00000001" Type="S">
  <Orders>
    <Order Id="43860" OrderDate="2001-08-01T00:00:00">
      <OrderDetail ProductName="Road-650 Red, 62" Quantity="2" />
      <OrderDetail ProductName="Road-650 Black, 52" Quantity="1" />
    </Order>
    <Order Id="44501" OrderDate="2001-11-01T00:00:00">
      <OrderDetail ProductName="Road-650 Red, 62" Quantity="1" />
    </Order>
  </Orders>
</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!

Leave a Reply Cancel reply