Composing An XML Document From Relational Data: Part 2

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:

-- Base Query
SELECT C.CUSTOMERID,C.ACCOUNTNUMBER,S.NAME,
       SALESORDERID AS "Id",
       CAST(SHIPDATE AS DATE) AS "ShipDate",
       TOTALDUE AS "Total"
  FROM ADVWORKS.CUSTOMER C
  JOIN ADVWORKS.STORE S ON S.CUSTOMERID=C.CUSTOMERID
  JOIN ADVWORKS.SALESORDERHEADER SOH ON SOH.CUSTOMERID=S.CUSTOMERID
 WHERE C.CUSTOMERTYPE='S';

Author’s Note: By the way, as usual, I’m using the Microsoft Adventure Works sample data for this demonstration.

The shown code below, which we’ll call Figure 1, is what the final XML should look like:

<Stores>
  <Store>
    <Account>AW00000001</Account>
    <Name>A Bike Store</Name>
    <Orders>
      <Order Id="43860" ShipDate="2001-08-08" Total="14603.7393" />
      <Order Id="44501" ShipDate="2001-11-08" Total="26128.8674" />
      <Order Id="45283" ShipDate="2002-02-08" Total="37643.1378" />
      <Order Id="46042" ShipDate="2002-05-08" Total="34722.9906" />
    </Orders>
  </Store>
  <Store>
    <Account>AW00000002</Account>
    <Name>Progressive Sports</Name>
    <Orders>
      <Order Id="46976" ShipDate="2002-08-08" Total="10184.0774" />
      <Order Id="47997" ShipDate="2002-11-08" Total="5469.5941" />
      <Order Id="49054" ShipDate="2003-02-08" Total="1739.4078" />
      <Order Id="50216" ShipDate="2003-05-08" Total="1935.5166" />
      <Order Id="51728" ShipDate="2003-08-08" Total="3905.2547" />
      <Order Id="57044" ShipDate="2003-11-08" Total="4537.8484" />
      <Order Id="63198" ShipDate="2004-02-08" Total="4053.9506" />
      <Order Id="69488" ShipDate="2004-05-08" Total="908.3199" />
    </Orders>
  </Store>
</Stores>

Figure 1. Final XML containing store names (element based) and the respective orders (attribute based.)

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):

-- Step 2 - Transform the store columns into XML elements
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 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_Scalar
  FROM CTE_STORES S
  JOIN ADVWORKS.SALESORDERHEADER SOH ON SOH.CUSTOMERID=S.CUSTOMERID
GROUP BY S.CUSTOMERID,S.ACCOUNTNUMBER,S.NAME

-- 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