Systems Engineering and RDBMS

Generating XML data for a Parent Child Relationship

Posted by decipherinfosys on October 3, 2008

We have written quite a few blog posts and articles on the XML capabilities in SQL Server and Oracle. You can search for those posts on our site. One of the questions that a reader had asked recently was how to generate an XML output when we have multiple join conditions between tables which have 1:N (one to many) relationships? We will focus this post on putting together some examples and then using FOR XML PATH clause in SQL Server 2005, we will show how easy it is to generate this type of information in an XML.

Let’s pick up the two tables Sales.SalesOrderHeader and Sales.SalesOrderDetail from AdventureWorks Sample user database. And also tie it to the Production.Product table to get the name of the products on the detail items. And say, we are interested in these 3 Sales Orders:

select
SalesOrder.SalesOrderNumber,
SalesOrder.PurchaseOrderNumber,
SalesOrder.AccountNumber,
SalesOrder.OrderDate,
SalesOrder.DueDate,
SalesOrder.ShipDate,
OrderDetails.SalesOrderDetailID,
Product.Name,
OrderDetails.CarrierTrackingNumber,
OrderDetails.UnitPrice,
OrderDetails.LineTotal,
OrderDetails.OrderQty
from Sales.SalesOrderHeader as SalesOrder
inner join Sales.SalesOrderDetail as OrderDetails
on SalesOrder.SalesOrderID = OrderDetails.SalesOrderID
inner join Production.Product as Product
on OrderDetails.ProductID = Product.ProductID
Where SalesOrder.SalesOrderID in (43569, 43660, 43661)

Since this is a 1:N relationship, you will get the header records repeated N number of times where N is the number of details for a given sales order. Also, the Order Details is linked into a N:1 relationship with the Production.Product table as well. If we just use a “FOR XML PATH (‘SalesOrder’)” clause in this SQL, the output will be like the one seen here: outpt_data_1

As you can see, it is neither optimal nor a good way of representing the data since the data gets repeated. So, let’s add the details and the product data as a nested select in order to demonstrate the relation in the XML output as well:

Use AdventureWorks
go
select
SalesOrder.SalesOrderNumber,
SalesOrder.PurchaseOrderNumber,
SalesOrder.AccountNumber,
SalesOrder.OrderDate,
SalesOrder.DueDate,
SalesOrder.ShipDate,
(SELECT
OrderDetails.SalesOrderDetailID,
Product.Name,
OrderDetails.CarrierTrackingNumber,
OrderDetails.UnitPrice,
OrderDetails.LineTotal,
OrderDetails.OrderQty
FROM Sales.SalesOrderDetail as OrderDetails
inner join Production.Product as Product
on OrderDetails.ProductID = Product.ProductID
WHERE SalesOrder.SalesOrderID = OrderDetails.SalesOrderID
FOR XML PATH (‘Details’)) AS DETAILS
from Sales.SalesOrderHeader as SalesOrder
Where SalesOrder.SalesOrderID in (43569, 43660, 43661)
FOR XML PATH (‘SalesOrder’)

And now the output will be like the one shown here: outpt_data_2

Uughhhh! The Details node looks ugly and is not even properly readable. What we need to do is add the TYPE directive at the end of it so that we get the data back as an XML type:

Use AdventureWorks
go
select
SalesOrder.SalesOrderNumber,
SalesOrder.PurchaseOrderNumber,
SalesOrder.AccountNumber,
SalesOrder.OrderDate,
SalesOrder.DueDate,
SalesOrder.ShipDate,
(SELECT
OrderDetails.SalesOrderDetailID,
Product.Name,
OrderDetails.CarrierTrackingNumber,
OrderDetails.UnitPrice,
OrderDetails.LineTotal,
OrderDetails.OrderQty
FROM Sales.SalesOrderDetail as OrderDetails
inner join Production.Product as Product
on OrderDetails.ProductID = Product.ProductID
WHERE SalesOrder.SalesOrderID = OrderDetails.SalesOrderID
FOR XML PATH (‘Details’), TYPE) AS DETAILS
from Sales.SalesOrderHeader as SalesOrder
Where SalesOrder.SalesOrderID in (43569, 43660, 43661)
FOR XML PATH (‘SalesOrder’)

And the output is as shown over here: outpt_data_3

You can also achieve this by using the FOR EXPLICIT mode but that is very cumbersome. FOR XML PATH provides easier and simpler alternatives. You can read more on the PATH mode over here for SQL Server 2005 and here for SQL Server 2008.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: