Composing An XML Document From Relational Data: Part 2

August 22, 2012
Michael Sansoterra

In Part 1 of this series, I covered how to transform the results of a DB2 for i7.1 query into either an element- or attribute-based XML document. This time, I’m going to delve into a more complicated XML composition that requires the nesting of sales order XML elements as children of retail store elements. As with the last tip, I’ll start with a base SQL statement that extracts the required data and then gradually transform that statement until it produces the desired XML document using the new DB2 for i XML functionality.

To start, here is the base query that extracts store information and the corresponding sales orders for each store:

If you haven’t read Part 1, please review it so you understand the basics of the new i7.1 XML functions.

The trick with this example is to nest the <Order> elements as children of the <Store> elements. To ease the XML construction task, I’m going to restructure the query using a common table expression (CTE) for the store data. Hopefully it will become clear as to why I’m doing this as you read on:

-- Step 1 - Aggregate the sales order XML at the store level
WITH CTE_STORES AS (
SELECT C.CUSTOMERID,C.ACCOUNTNUMBER,S.NAME
FROM ADVWORKS.CUSTOMER C
JOIN ADVWORKS.STORE S ON S.CUSTOMERID=C.CUSTOMERID
WHERE C.CUSTOMERTYPE='S'
)
SELECT S.CUSTOMERID,S.ACCOUNTNUMBER,S.NAME,
XMLAGG(XMLROW(SALESORDERID AS "Id",CAST(SHIPDATE AS DATE)
AS "ShipDate",TOTALDUE AS "Total" OPTION ROW "Order" AS
ATTRIBUTES)) AS XML_ORDER_DATA
FROM CTE_STORES S
JOIN ADVWORKS.SALESORDERHEADER SOH ON SOH.CUSTOMERID=S.CUSTOMERID
GROUP BY S.CUSTOMERID,S.ACCOUNTNUMBER,S.NAME

XMLROW is used to convert the sales order data into an attribute-based XML fragment (one fragment for each sales order) with <Order> as the parent element. The XMLAGG aggregate function is used to concatenate all of the sales order fragments for each store (as specified in the GROUP BY).

The abridged query results look like this (with modified heading names for the sake of space):

That is getting pretty close to the final product shown above in Figure 1. (Remember all line breaks were inserted for readability).

To achieve the final product, there are only two steps left. The first step is to aggregate the <Store> fragments into a single XML column. The second step is to assign a root element called <Stores>.

To make things easier to read, I placed the prior query example into a new CTE called CTE_STORES_ORDERS which returns multiple rows and one column called XML_DATA. The main SELECT will now operate on this one column and concatenate all of the rows into a single value using XMLAGG:

-- Step 3 -- Aggregate XML at the store level
WITH CTE_STORES AS (
SELECT C.CUSTOMERID,C.ACCOUNTNUMBER,S.NAME
FROM ADVWORKS.CUSTOMER C
JOIN ADVWORKS.STORE S ON S.CUSTOMERID=C.CUSTOMERID
WHERE C.CUSTOMERTYPE='S'
),
CTE_STORES_ORDERS AS (
SELECT XMLROW(S.ACCOUNTNUMBER AS "Account",S.NAME AS "Name",
XMLAGG(XMLROW(SALESORDERID AS "Id",CAST(SHIPDATE AS DATE)
AS "ShipDate",TOTALDUE AS "Total" OPTION ROW "Order" AS
ATTRIBUTES)) AS "Orders" OPTION ROW "Store") AS XML_Data
FROM CTE_STORES S
JOIN ADVWORKS.SALESORDERHEADER SOH ON SOH.CUSTOMERID=S.CUSTOMERID
GROUP BY S.CUSTOMERID,S.ACCOUNTNUMBER,S.NAME
)
SELECT XMLDOCUMENT(XMLELEMENT(NAME "Stores",XMLAGG(XML_Data)))
AS XML_Document
FROM CTE_STORES_ORDERS

Function XMLAGG(XML_Data) combines all of the XML from the prior result set into a single XML column. This aggregate expression is the second parameter to the XMLELEMENT function. XMLELEMENT creates the root <Stores> element and assigns the aggregated store and order XML as its value. The XMLDOCUMENT function is used to validate that the XML has been constructed correctly (a valid XML document with one root element).

The results of the DB2 for i query now match the XML shown in Figure 1.

The XML capabilities in i7.1 are marvelous. Of course there is more than one way to skin a cat. There are several other new XML functions in DB2 for i7.1 that I haven’t introduced that can be used to write alternative queries yet produce the same results. Check them out in the DB2 for i SQL Reference. DB2 for i is a great tool for composing XML documents based on data in your relational database.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.