Returning Multiple Disparate Result Sets as XML From SQL Server Using FOR XML EXPLICIT

OK.  So I’m finally returning to a SQL Server topic.  The integration tool I’m working on does a great deal of processing of XML.  In some cases, a single XML document consists of multiple result sets.  I won’t get into the details of why we need this, but a good example of where you might use it is to return an Order object from a database for an edit form and additional return a list of Order Types to populate the Order Type drop down box.  In this case the XML might look like this:

<orderformdata>
    <orders>
        <order>
            <orderdate>1/1/2011</orderdate>
            <ordertypeid>1</ordertypeid>
            <orderamount>1000.00</orderamount>
        </order>
        <order>
            <orderdate>1/1/2011</orderdate>
            <ordertypeid>1</ordertypeid>
            <orderamount>1000.00</orderamount>
        </order>
    </orders>
    <references>
        <ordertypes>
            <ordertype id="1" name="Professional Services"/>
            <ordertype id="2" name="Product"/>
            <ordertype id="3" name="Support Contract"/>
        </ordertypes>
    </references>
</orderformdata>

Using SQL Servers FOR XML clause, it’s quite easy to output a result set as XML.  With the EXPLICIT directive you can have a great amount of control over how the XML is rendered.  However, the examples tend to show how to create XML from a single result set or nested records (say orders and order details).  So how to return the above?
First let’s start by creating our sample schema and inserting a few records:

--  Create order types table
CREATE TABLE tblOrderTypes(
OrderTypeId int IDENTITY(1,1) NOT NULL,
Name nvarchar(50) NOT NULL
CONSTRAINT PK_tblOrderTypes PRIMARY KEY CLUSTERED(OrderTypeId ASC)
)
GO
--  Create orders table
CREATE TABLE tblOrders(
OrderId int IDENTITY(1,1) NOT NULL,
OrderTypeId int NOT NULL,
OrderDate datetime NOT NULL,
OrderAmount money NOT NULL,
CONSTRAINT PK_tblOrders PRIMARY KEY CLUSTERED (OrderId ASC)
)
GO
--  Create foreign key constraint
ALTER TABLE tblOrders  WITH CHECK ADD CONSTRAINT FK_tblOrders_tblOrderTypes FOREIGN KEY(OrderTypeId)
REFERENCES tblOrderTypes (OrderTypeId)
GO
ALTER TABLE tblOrders CHECK CONSTRAINT FK_tblOrders_tblOrderTypes
GO
--  Insert order types
SET IDENTITY_INSERT tblOrderTypes ON
GO
INSERT INTO tblOrderTypes(
OrderTypeId
,Name)
SELECT OrderTypeId = 1, Name = 'Professional Services'
UNION ALL
SELECT OrderTypeId = 2, Name = 'Product'
UNION ALL
SELECT OrderTypeId = 3, Name = 'Support Contract'
GO
SET IDENTITY_INSERT tblOrderTypes OFF
GO
--  Insert order
INSERT INTO tblOrders(
OrderDate
,OrderTypeId
,OrderAmount)
SELECT OrderDate = '1/1/2001', OrderTypeId = 1, OrderAmount = 1000.00
UNION ALL
SELECT OrderDate = '2/1/2001', OrderTypeId = 2, OrderAmount = 1500.00
GO

First, a simple query to return the order as XML:

SELECT 
        * 
    FROM tblOrders 
    FOR XML AUTO

This will  return the following XML:

<tblOrders 
          OrderId="1" 
          OrderTypeId="1" 
          OrderDate="2001-01-01T00:00:00" 
          OrderAmount="1000.0000" /> 
<tblOrders 
          OrderId="2" 
          OrderTypeId="2" 
          OrderDate="2001-02-01T00:00:00" 
          OrderAmount="1500.0000" />

First problem is there’s no root node.  We can fix that with the ROOT directive as follows:

SELECT 
        * 
    FROM tblOrders 
    FOR XML AUTO, ROOT('OrderFormData')

This will now produce a valid XML document as follows:

<OrderFormData>
    <tblOrders OrderId="1" 
        OrderTypeId="1" 
        OrderDate="2001-01-01T00:00:00" 
        OrderAmount="1000.0000"/>
    <tblOrders 
        OrderId="2" 
        OrderTypeId="2" 
        OrderDate="2001-02-01T00:00:00" 
        OrderAmount="1500.0000"/>
</OrderFormData>

Now lets use the EXPLICIT directive to format this how we want it to look above:

SELECT 
            Tag = 1 
            ,Parent = NULL 
            ,[Orders!1] = NULL 
            ,[Order!2!OrderDate!ELEMENT] = NULL 
            ,[Order!2!OrderTypeId!ELEMENT] = NULL 
            ,[Order!2!OrderAmount!ELEMENT] = NULL 
    UNION ALL 
    SELECT 
            Tag = 2 
            ,Parent = 1 
            ,[Orders!1] = NULL 
            ,[Order!2!OrderDate!ELEMENT] = OrderDate 
            ,[Order!2!OrderTypeId!ELEMENT] = OrderTypeId 
            ,[Order!2!OrderAmount!ELEMENT] = OrderAmount 
        FROM tblOrders 
        FOR XML EXPLICIT, ROOT('OrderFormData')

This will return the following XML:

<OrderFormData>
    <Orders>
        <Order>
            <OrderDate>2001-01-01T00:00:00</OrderDate>
            <OrderTypeId>1</OrderTypeId>
            <OrderAmount>1000.0000</OrderAmount>
        </Order>
        <Order>
            <OrderDate>2001-02-01T00:00:00</OrderDate>
            <OrderTypeId>2</OrderTypeId>
            <OrderAmount>1500.0000</OrderAmount>
        </Order>
    </Orders>
</OrderFormData>

Now to add the order types to the References node:

--  Create Orders node 
    SELECT 
            Tag = 1 
            ,Parent = NULL 
            ,[Orders!1] = NULL 
            ,[Order!2!OrderDate!ELEMENT] = NULL 
            ,[Order!2!OrderTypeId!ELEMENT] = NULL 
            ,[Order!2!OrderAmount!ELEMENT] = NULL 
            ,[References!3] = NULL 
            ,[OrderTypes!4] = NULL 
            ,[OrderType!5!id] = NULL 
            ,[OrderType!5!name] = NULL 
    UNION ALL 
    --  Add Order record nodes to Orders node 
    SELECT 
            Tag = 2 
            ,Parent = 1 
            ,[Orders!1] = NULL 
            ,[Order!2!OrderDate!ELEMENT] = OrderDate 
            ,[Order!2!OrderTypeId!ELEMENT] = OrderTypeId 
            ,[Order!2!OrderAmount!ELEMENT] = OrderAmount 
            ,[References!3] = NULL 
            ,[OrderTypes!4] = NULL 
            ,[OrderType!5!id] = NULL 
            ,[OrderType!5!name] = NULL 
        FROM tblOrders 
    UNION ALL 
    --  Create References node 
    SELECT 
            Tag = 3 
            ,Parent = NULL 
            ,[Orders!1] = NULL 
            ,[Order!2!OrderDate!ELEMENT] = NULL 
            ,[Order!2!OrderTypeId!ELEMENT] = NULL 
            ,[Order!2!OrderAmount!ELEMENT] = NULL 
            ,[References!3] = NULL 
            ,[OrderTypes!4] = NULL 
            ,[OrderType!5!id] = NULL 
            ,[OrderType!5!name] = NULL 
    UNION ALL 
    --  Create OrderType record nodes under OrderTypes node 
    SELECT 
            Tag = 4 
            ,Parent = 3 
            ,[Orders!1] = NULL 
            ,[Order!2!OrderDate!ELEMENT] = NULL 
            ,[Order!2!OrderTypeId!ELEMENT] = NULL 
            ,[Order!2!OrderAmount!ELEMENT] = NULL 
            ,[References!3] = NULL 
            ,[OrderTypes!4] = NULL 
            ,[OrderType!5!id] = NULL 
            ,[OrderType!5!name] = NULL 
    UNION ALL 
    --  Create OrderTypes node under References node 
    SELECT 
            Tag = 5 
            ,Parent = 4 
            ,[Orders!1] = NULL 
            ,[Order!2!OrderDate!ELEMENT] = NULL 
            ,[Order!2!OrderTypeId!ELEMENT] = NULL 
            ,[Order!2!OrderAmount!ELEMENT] = NULL 
            ,[References!3] = NULL 
            ,[OrderTypes!4] = NULL 
            ,[OrderType!5!id] = OrderTypeId 
            ,[OrderType!5!name] = Name 
        FROM tblOrderTypes 
        FOR XML EXPLICIT, ROOT('OrderFormData')

This will now produce the desired XML document:

<OrderFormData>
    <Orders>
        <Order>
            <OrderDate>2001-01-01T00:00:00</OrderDate>
            <OrderTypeId>1</OrderTypeId>
            <OrderAmount>1000.0000</OrderAmount>
        </Order>
        <Order>
            <OrderDate>2001-02-01T00:00:00</OrderDate>
            <OrderTypeId>2</OrderTypeId>
            <OrderAmount>1500.0000</OrderAmount>
        </Order>
    </Orders>
    <References>
        <OrderTypes>
            <OrderType id="1" name="Professional Services"/>
            <OrderType id="2" name="Product"/>
            <OrderType id="3" name="Support Contract"/>
        </OrderTypes>
    </References>
</OrderFormData>

If the FOR XML EXPLICIT SELECT statements are looking a little Greek to you and making you pine for a bottle of Excedrin Migraine, take a look at the MSDN article, Using EXPLICIT Mode.