Creating XML documents from database data can be complicated sometimes. But the DB2 database inside IBM i has some powerful XML functions to create XML documents, elements, and attributes, and for the serialization of the XML data.

I want to give special credits to Nuno Carriço from Portugal – he brought my attention to the XML functions which inspired me to do some research and write about it. And of course I want to thank Holger Scherer for pub400.com.

Disclaimer: I developed and tested the SQL statements on pub400.com which runs IBM i Version 7.4 – using the statements on other OS versions/releases can return different results. English is not my native language, so please excuse any errors I made.

As always we are starting from a database – in this case with two tables. A customer table with one row for each customer and a customer attributes table with multiply qualified attributes per customer:

What we want is a XML document with a list element, containing one element per customer which contains elements for the address, and one element for each qualified contact attribute of the customer. So the hierarchy should look like this:

• XML-Document
• Customer List
  • Customer
    • Names
    • Address
    • Contacts (Phone, Mail, etc.)
  • Customer
    • Names
    • Address
    • Contacts (Phone, Mail, etc.)

This shouldn’t be so hard, but we have to dive into the XML functions of IBM’s DB2 database.

To build the typical hierarchical XML structure, we need the following functions:

• XMLELEMENT – to create a typical XML element like <element>data</element>
• XMLATTRIBUTES – to add XML attributes to an element like <element attribute=“value“>
• XMLAGG – to aggregate multiple SQL rows into a list of elements
• XMLDOCUMENT – to wrap all the created elements into a XML document
• XMLSERIALIZE – to serialize the XML document into a SQL data value of a given type like CLOB

So enough written about the building blocks – here is the code:

I won’t go through all of the code line by line – because I find that kind of explanation somehow annoying – and because I think that you are an experienced programmer.

So let’s concentrate on the inner SELECT statement from line 19 to 27. We are selecting all the customer attributes from the table for the customer that is processed. With xmlagg(...) the rows are aggregated into one value – and for each customer attribute row we are creating a XML element like this:

<contact type="EMAIL">email@address</contact>

You surely noticed, that the ORDER BY clause seems to be in the wrong position. Normally it has to follow the FROM clause of the SELECT statement – but in this case, the ORDER BY clause belongs to the xmlagg(...) function.

The outer SELECT statement follows the same structure als the inner one. We are using different types of data structures – some elements and some attributes.

Finally we wrap this all into a xmldocument(...) function – this doesn’t create a visible XML tag – it is only a wrapper. The resulting XML document is serialized with xmlserialize(...) into a CLOB of size 1000 and we are including the typical XML version and encoding tags at the beginning.

The result of our SQL statement will look like this:

<?xml version="1.0" encoding="UTF-8"?>
<customer_list>
  <customer customer_number="CQ4711">
    <name>Marvin's Toys</name>
    <name2></name2>
    <address type="STREET">
      <line1>64a N Williams St</line1>
      <line2></line2>
      <city>Crystal Lake</city>
      <state>IL</state>
      <zip>60014</zip>
    </address>
    <contact type="MAIL">marvinstoys@gmail.com</contact>
    <contact type="PHONE">7792204179</contact>
  </customer>
  <customer customer_number="XN123">
    <name>LS Dolls &amp; Teddy Bears</name>
    <name2></name2>
    <address type="STREET">
      <line1>267 Main St</line1>
      <line2></line2>
      <city>Huntington</city>
      <state>NY</state>
      <zip>11743</zip>
    </address>
    <contact type="MAIL">lsdolls@aol.com</contact>
    <contact type="PHONE">6315498743</contact>
  </customer>
</customer_list>

Hopefully my explanations are somehow helpfull – I always think, that it’s the best, to try it yourself – maybe with some of your own tables. I think you will find, that after playing around with the XML functions it’s quite easy to create all kinds of XML documents with SQL.

VALUES(
  xmlserialize(
    xmldocument(
      xmlelement(name "customer_list", (
        SELECT xmlagg(
                 xmlelement(name "customer",
                   xmlattributes(TRIM(customer.custno) AS "customer_number"),
                   xmlelement(name "name", customer.name),
                   xmlelement(name "name2", customer.name2),
                   xmlelement(name "address",
                     xmlattributes('STREET' AS "type"),
                     xmlelement(name "line1", customer.addr_line1),
                     xmlelement(name "line2", customer.addr_line2),
                     xmlelement(name "city", customer.city),
                     xmlelement(name "state", customer.state),
                     xmlelement(name "zip", customer.zip)
                   ),
                   (
                     SELECT xmlagg(
                              xmlelement(name "contact",
                                xmlattributes(TRIM(custattr.qualifier) AS "type"),
                                TRIM(custattr.value)
                              )
                              ORDER BY custattr.qualifier
                            )
                     FROM custattr
                     WHERE custattr.idcust = customer.id
                   )
                 )
                 ORDER BY customer.custno
               )
          FROM customer         
        )
      )
    )
    AS CLOB(1000) including xmldeclaration
  )
);

Edit: I updated the SQL code and the resulting XML structure to better reflect a typical use case (especially around the „address“ tag). I also commented the SQL code, so that the resulting XML tags and attributes are related to each SQL function – I hope this makes the code a bit easier to understand. Thanks to Jon Paris and Nuno Carriço for their input.