• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • 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:

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




    ID

    ID

    ACCT
    NO

    NAME

    XML_ORDER_DATA

    1

    AW00000001

    A Bike Store

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

    2

    AW00000002

    Progressive Sports

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

    The result has a row for each store, and the XML_ORDER_DATA column contains the XML for each store’s sales orders.

    Next, we’ll need to transform each store’s information into an XML <Store> element, and nest the sales orders as its children. This can be accomplished once again by using the XML row function:

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

    The outer XMLROW function accomplishes the following:

    1. Places the store column data into XML elements
    2. Assigns a parent element called <Store>
    3. Includes the aggregation of the sales order fragments in a parent element called <Orders> (note the AS “Orders” alias)

    The results of the query now look like this:




    XML_SCALAR

    XML_SCALAR

    <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″/>
    </Orders>
    </Store>

    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.

    RELATED STORY

    Composing An XML Document From Relational Data, Part 1



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    WorksRight Software

    Do you need area code information?
    Do you need ZIP Code information?
    Do you need ZIP+4 information?
    Do you need city name information?
    Do you need county information?
    Do you need a nearest dealer locator system?

    We can HELP! We have affordable AS/400 software and data to do all of the above. Whether you need a simple city name retrieval system or a sophisticated CASS postal coding system, we have it for you!

    The ZIP/CITY system is based on 5-digit ZIP Codes. You can retrieve city names, state names, county names, area codes, time zones, latitude, longitude, and more just by knowing the ZIP Code. We supply information on all the latest area code changes. A nearest dealer locator function is also included. ZIP/CITY includes software, data, monthly updates, and unlimited support. The cost is $495 per year.

    PER/ZIP4 is a sophisticated CASS certified postal coding system for assigning ZIP Codes, ZIP+4, carrier route, and delivery point codes. PER/ZIP4 also provides county names and FIPS codes. PER/ZIP4 can be used interactively, in batch, and with callable programs. PER/ZIP4 includes software, data, monthly updates, and unlimited support. The cost is $3,900 for the first year, and $1,950 for renewal.

    Just call us and we’ll arrange for 30 days FREE use of either ZIP/CITY or PER/ZIP4.

    WorksRight Software, Inc.
    Phone: 601-856-8337
    Fax: 601-856-9432
    Email: software@worksright.com
    Website: www.worksright.com

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Townsend Security:  IBM i Encryption Without Program Changes!   >> View Webcast
    looksoftware:  iBelieve New York. A free IBM i Community Event. September 27.
    Help/Systems:  2012 Solutions Summit. September 17-20 in Minneapolis, MN.

    IT Jungle Store Top Book Picks

    BACK IN STOCK: Easy Steps to Internet Programming for System i: List Price, $49.95

    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The iSeries Pocket Database Guide: List Price, $59
    The iSeries Pocket SQL Guide: List Price, $59
    The iSeries Pocket WebFacing Primer: List Price, $39
    Migrating to WebSphere Express for iSeries: List Price, $49
    Getting Started with WebSphere Express for iSeries: List Price, $49
    The All-Everything Operating System: List Price, $35
    The Best Joomla! Tutorial Ever!: List Price, $19.95

    UC4 Sold to Private Equity Firm for $270 Million IBM Lassos Texas Memory Systems For Flashy Storage

    Leave a Reply Cancel reply

Volume 12, Number 21 -- August 22, 2012
THIS ISSUE SPONSORED BY:

T.L. Ashford
WorksRight Software
American Top Tools

Table of Contents

  • Composing An XML Document From Relational Data: Part 2
  • Where’s The Module?
  • Admin Alert: Copying User Profiles Between Systems

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • Liam Allan Shares What’s Coming Next With Code For IBM i
  • From Stable To Scalable: Visual LANSA 16 Powers IBM i Growth – Launching July 8
  • VS Code Will Be The Heart Of The Modern IBM i Platform
  • The AS/400: A 37-Year-Old Dog That Loves To Learn New Tricks
  • IBM i PTF Guide, Volume 27, Number 25
  • Meet The Next Gen Of IBMers Helping To Build IBM i
  • Looks Like IBM Is Building A Linux-Like PASE For IBM i After All
  • Will Independent IBM i Clouds Survive PowerVS?
  • Now, IBM Is Jacking Up Hardware Maintenance Prices
  • IBM i PTF Guide, Volume 27, Number 24

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2025 IT Jungle