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

    August 8, 2012 Hey, Mike

    My boss asked me if I know of a cheap or free tool that can automatically export a DB2 (for i) table to an XML file. I told him that I could write a Java program pretty easily that could accomplish this for a specific table, but I’m not aware of any way to “generically” do it for any table.

    For example:

    select *
      from deleteme
    
    TheKey   SomeText
         1   Row Number 1
         2   Row Number 2
    

    That code should produce output something like this:

    <?xml version="1.0" encoding="UTF-8" ?>
    <deleteme>
     <row>
       <TheKey>1</TheKey>
       <SomeText>Row Number 1</SomeText>
     </row>
     <row>
       <TheKey>2</TheKey>
       <SomeText>Row Number 2</SomeText>
     </row>
    </deleteme>
    

    I’m pretty sure the DB2 utility from QSH could probably do this, but I don’t know how to do it. Do you know of an easy way to accomplish this on DB2 for i? Thanks!

    –D.F.

    I’m not aware of any free tool to accomplish this. In fact, even the latest IBM iAccess tools (including the file transfer utility and the RunSQL “save results as”) fail to provide this kind of save as XML option.

    I believe part of the reason IBM hasn’t given us a way to do this is because offering XML as an export option opens up a whole can of worms. Think about it: Do you want to export your data as elements? How about as attributes or a combination of both? What do you want to call your root element and what case should it be?

    IBM could probably make this work for single table flat data, but what if you have multiple tables in a query? What if you’re joining customers with sales orders and you want the sales data nested within the customer data? With XML, the export options are endless and having the user configure an XML export could be a little daunting.

    However, DB2 for i 7.1 includes full support for creating XML documents almost any way you want. While there is no generic utility in DB2 to do this type of conversion automatically, the SQL required to build an XML is a simple pattern that can be molded into a generic utility.

    For now, I’ll cover how to use the new XML features to build a simple XML from relational data. Below is the base SQL data retrieval statement:

    -- Base Query
    SELECT PRODUCTID,NAME,PRODUCTNUMBER,COLOR
    FROM ADVWORKS.PRODUCT
    ORDER BY NAME
    

    The resulting XML should look like this:

    <?xml version="1.0" encoding="UTF-8" ?>
    <Products>
    <Product>
    <ProductId>1</ProductId>
    <Name>Adjustable Races</Name>
    <ProductNumber>AR-5381</ProductNumber>
    </Product>
    <Product>
    <ProductId>352</ProductId>
    <Name>Front Derailleur Linkage</Name>
    <ProductNumber>FL-2301</ProductNumber>
    <Color>Silver</Color>
    </Product>
    </Products>
    

    Author’s Note: Column COLOR is nullable and hence doesn’t appear in the XML when it is null.

    And now we’ll transform the base query to build the XML document. The first thing to do when converting table row data to XML elements is to use the XMLROW function.

    -- Step 1: Convert a tabular row to an equivalent "XMLROW"
    SELECT XMLROW(PRODUCTID AS "ProductId",NAME 
    AS "Name",PRODUCTNUMBER AS "ProductNumber",COLOR 
    AS "Color" OPTION ROW "Product") AS XML_ROW
    FROM ADVWORKS.PRODUCT
    ORDER BY NAME
    

    XMLROW accepts a list of columns and returns an XML fragment. Each column name passed to the function will be converted to an XML element with its data as the element value. In this case, I want the element names to be mixed case so I aliased the column names within the function. Finally, OPTION ROW is used to define a parent element. The element “Product” will be a “parent element,” and all of the column elements are “child elements.”

    The result of this query now looks like this (only a few sample rows are shown here):




    XML_ROW

    XML_ROW

    <Product><ProductId>712</ProductId><Name>AWC
    Logo
    Cap</Name><ProductNumber>CA-1098</ProductNumber><Color>Multi</Color></Product>

    <Product><ProductId>1</ProductId><Name>Adjustable
    Races</Name><ProductNumber>AR-5381</ProductNumber></Product>

    At this point, there is only one column in the result set (called XML_ROW) that contains the requested data as XML elements. But since the goal is for all of the information to be placed in one XML document, the query should return only one row with one XML column.

    So the next step is to combine all of the XML rows into a single row and column. Aggregate function XMLAGG (XML Aggregate) can combine the XML data from all of the product rows to create a single column:

    -- Step 2: Aggregate all rows  (ORDER BY goes in nested table expression)
    SELECT XMLAGG(XMLROW(PRODUCTID AS "ProductId",NAME AS "Name",
    PRODUCTNUMBER AS "ProductNumber",COLOR AS "Color"OPTION ROW "Product")
    ) AS XML_AGG
    FROM (SELECT * FROM ADVWORKS.PRODUCT ORDER BY NAME) PRODUCT
    

    Since XMLAGG is an aggregate function it can be used just like any other aggregate function with a GROUP BY. Though in this case, aggregating the XML of all the rows doesn’t require a GROUP BY. The input to the XMLAGG function is the result of the XMLROW function; each XMLROW is concatenated into one large string. The abridged result set containing only one row and one column looks like this:




    XML_AGG

    XML_AGG

    <Product><ProductId>1</ProductId><Name>Adjustable
    Races</Name><ProductNumber>AR-5381</ProductNumber></Product>

    <Product><ProductId>2</ProductId><Name>Bearing
    Ball</Name><ProductNumber>BA-8327</ProductNumber></Product>

    Author’s Note: There is no white space inserted in the XML; I inserted the line breaks for readability.

    With an aggregate query, the ORDER BY cannot specify a column that is not in the SELECT list. Therefore, to maintain the ordering of the data in the XML, the base query was converted to a nested table expression.

    An XML document has only one root element. The next step to polish off the XML is to add a root element named “Products”. The XMLELEMENT function can be used to define an element. This function accepts two inputs: an element name and its value. In the query below, the name given to our root element is “Products” and the value is going to be the result of the XMLAGG aggregate function:

    -- Step 3: Add root element
    SELECT XMLELEMENT(NAME "Products",
    XMLAGG(XMLROW(PRODUCTID AS "ProductId",NAME AS "Name",PRODUCTNUMBER 
    AS "ProductNumber",COLOR AS "Color" OPTION ROW "Product"))
    ) AS XML_DOCUMENT
    FROM (SELECT * FROM ADVWORKS.PRODUCT ORDER BY NAME) PRODUCT
    

    The abridged result looks like this (the same result as the prior query with the exception that there is now a root element):




    XML_DOCUMENT

    XML_DOCUMENT

    <Products>

    <Product><ProductId>1</ProductId><Name>Adjustable
    Races</Name><ProductNumber>AR-5381</ProductNumber></Product>

    <Product><ProductId>2</ProductId><Name>Bearing
    Ball</Name><ProductNumber>BA-8327</ProductNumber></Product>

    <Products>

    Again, the line breaks were added to the XML for readability. The actual result is one long string with no line breaks.

    The XML is now close to the one shown above. The last item to add is the XML declaration (i.e., <?xml version=”1.0″ encoding=”UTF-8″?>) to the beginning of the XML document. The XMLSERIALIZE function will perform this task for us:

    -- Step 4: Add the XML declaration
    SELECT XMLSERIALIZE(XMLELEMENT(NAME "Products",
    XMLAGG(XMLROW(PRODUCTID AS "ProductId",NAME AS "Name",
    PRODUCTNUMBER AS "ProductNumber",COLOR AS "Color" 
    OPTION ROW "Product"))) AS CLOB(1M)  INCLUDING 
    XMLDECLARATION)  AS XML_DOCUMENT
    FROM (SELECT * FROM ADVWORKS.PRODUCT ORDER BY NAME) PRODUCT
    

    XMLSERIALIZE accepts an XML input parameter, and can optionally add an XML declaration to it and cast the entire result to a string or binary value. In this case the input is everything we’ve already done to this point (an aggregate of XML fragments for each row in the Products table combined with a root element). The abridged result now looks like this:




    XML_DOCUMENT

    XML_DOCUMENT

    <?xml
    version=”1.0″ encoding=”UTF-8″?>

    <Products>

    <Product><ProductId>1</ProductId><Name>Adjustable
    Races</Name><ProductNumber>AR-5381</ProductNumber></Product>

    <Product><ProductId>2</ProductId><Name>Bearing
    Ball</Name><ProductNumber>BA-8327</ProductNumber></Product>

    <Products>

    Because of the function nesting, the SQL statements that generate XML can get a little messy. However, it’s pretty easy to understand at each step how these functions work and it is definitely worth the small time investment to learn how to construct XML with DB2 for i. IBM has done a great job in giving developers a versatile approach to building XML.

    Attribute Based XML

    One simple variation I want to introduce is how to create an attribute based XML result instead of an element based XML. The code is exactly the same except for the addition of the AS ATTRIBUTES option. Backing up to the first step in the query:

    -- Step 1A (Alternative) - Attribute Based
    SELECT XMLROW(PRODUCTID AS "ProductId",NAME AS "Name",PRODUCTNUMBER 
    AS "ProductNumber",COLOR AS "Color" OPTION ROW "Product" 
    AS ATTRIBUTES) AS XML_ROW
    

    The abridged result now looks like this:




    XML_ROW

    XML_ROW

    <Product ProductId=”712″ Name=”AWC Logo Cap” ProductNumber=”CA 1098″
    Color=”Multi”/>

    <Product ProductId=”1″ Name=”Adjustable Races”
    ProductNumber=”AR 5381″/>

    Every column given to XMLROW is now rendered as an attribute value instead of an element. Attribute based XML is smaller than element based XML because the ending element tag isn’t required. For my small data set, the XML size was reduced from about 63K (element based) to 42K (attribute based).

    For reusability, it’s possible to create a view producing the desired XML as follows:

    CREATE VIEW ADVWORKS.PRODUCT_XML AS
    SELECT XMLSERIALIZE(XMLELEMENT(NAME "Products",
    XMLAGG(XMLROW(PRODUCTID AS "ProductId",NAME AS "Name",
    PRODUCTNUMBER AS "ProductNumber",COLOR AS "Color" 
    OPTION ROW "Product"))) AS CLOB(1M)  INCLUDING XMLDECLARATION)  
    AS XML_DOCUMENT
    FROM (SELECT * FROM ADVWORKS.PRODUCT ORDER BY NAME) PRODUCT
    

    From here, the QShell DB2 utility can be used to dump the results of the XML to an IFS file:

    db2 select xml_document from advworks.product_xml 
    >/tmp/products.xml
    

    However, the DB2 utility includes headings and a row count that must be stripped out to make the result useful. Therefore, I actually favor using RPG and either the CLOB_FILE SQL data type or the IFS APIs to dump the XML data directly to the IFS.

    While it’s not the prettiest thing to look at, it is possible to construct a utility that accepts a simple SQL statement and adds the necessary XML functions in order to get the desired XML result.

    In a future tip, I’ll demonstrate how to construct an XML document from multiple tables where nesting is required.

    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.



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

    Move Projects Forward with Expert Staffing Services

    Gain access to IBM i experts to move IT projects forward, reduce backlog and support business-critical systems.

    Fast onboarding, flexible engagement models for IBM i, RPG, COBOL, CA 2E (Synon), ERPs and more:

    • Bug Fixes & Maintenance
    • Full-Stack Web and Mobile Development
    • Application Enhancements
    • Application Maintenance
    • Database Modernization

    Speak to an Expert »

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    Linoma Software:  Stop doing FTP the hard way. Take a brief video tour of the GoAnywhere Suite
    Help/Systems:  FREE: Download the IBM i Scheduling Survival Guide
    Four Hundred Monitor Calendar:  Latest info on national conferences, local events, & Webinars

    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

    RJS Nabs Custom App Dev Firm Power7+ Chips Juiced With Faster Clocks, Memory Compression

    Leave a Reply Cancel reply

Volume 12, Number 20 -- August 8, 2012
THIS ISSUE SPONSORED BY:

SEQUEL Software
WorksRight Software
System i Developer

Table of Contents

  • Accessing Multiple DB2 Relational Databases In A Single Query
  • Composing An XML Document From Relational Data, Part 1
  • Admin Alert: The Right Way To Delete User Profiles, Part 2

Content archive

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

Recent Posts

  • IBM Tweaks Some Power Systems Prices Down, Others Up
  • Disaster Recovery: From OS/400 V5R3 To IBM i 7.4 In 36 Hours
  • The Disconnect In Modernization Planning And Execution
  • Superior Support: One Of The Reasons You Pay The Power Systems Premium
  • IBM i PTF Guide, Volume 25, Number 13
  • IBM i Has a Future ‘If Kept Up To Date,’ IDC Says
  • When You Need Us, We Are Ready To Do Grunt Work
  • Generative AI: Coming to an ERP Near You
  • Four Hundred Monitor, March 22
  • IBM i PTF Guide, Volume 25, Number 12

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 © 2023 IT Jungle