Guru: Generating XML Using SQL – The Easy Way

September 18, 2023 Bob Cozzi

There are many verbose methods of generating XML. You can:

• Write your own RPG code
• Using a free or third-party API
• Use SQL iQuery OUTPUT(*XML) option
• Use the built-in SQL XML functions such as XMLELEMENT

I’m sure there are others, but these seem to be the most popular.

For years I had been using the XMLELEMENT approach; a rather verbose set of XML function built into Db2 for i SQL. In fact, SQL iQuery’s OUTPUT(*XML) is based on that feature. It simply regenerates your SQL statement using XMLELEMENT statements for each output column/field name.

But if you’re using something IBM ACS RUNSQL to run SQL statements, or embedding the routine in an RPG IV program, there’s a much less vexing approach.

XMLROW

The XMLROW function returns an XML statement for the data, instead of the standard SQL column content. For example, the Customer Name column in a database file with a name of CUSTNAME is returned as:

I’ve been surprised by the number of RPG IV programmers who continue to write their own XML generators. I suppose if you’re using File specs and externally described data, you’re somewhat “stuck” with that approach.

I haven’t used an F spec in RPG IV for anything other than a Workstation or PRINTER file for at least 20 years.

Most people either have that one-row/record at a time output requirement, or they dump an entire dataset into XML. Both options can be done using SQL to XML conversion via XMLROW, and again the syntax is remarkably simple.

Let’s use the demo database file that ships with most IBM i systems. The QCUSTCDT file in the QIWS library. This file contains 12 records and if dumped with a “SELECT *” it might look like this:

To route this to XML output, you simply need to wrap the columns of the SELECT statement in XMLROW. Now if you’re a “select splat” person, this is going to be a little more work than you’re used to, but as a very wise SQL Guru once said, “no one should use select splat, except for dynamic queries.” Let’s first expand the query to explicitly coding the column names we want to include in the XML document. For our purposes, we only want the Customer number, name, and address.

SELECT
     cusnum as "CUSNUM", 
     TRIM(LSTNAM) as "LASTNAME",
     TRIM(INIT) as "INIT",
     TRIM(STREET) as "ADDRESS", 
     CITY as "CITY",
     STATE as "STATE",
     cast(digits(ZIPCOD) as varchar(6)) as "ZIPCODE"
  FROM qiws.qcustcdt;

The output from this statement using SQL iQuery or IBM ACS RUNSQL is basically the same as the original result above but includes fewer columns of data, ending after the zip code.

Now the fun part. Let’s cause this statement to output the result as an XML document. To do that we need to embed the XMLROW function around the columns. Effectively this means the XMLROW wraps everything after the SELECT clause and before the FROM clause, as follows:

SELECT
  xmlrow(
     cusnum as "CUSNUM", 
     TRIM(LSTNAM) as "LASTNAME",
     TRIM(INIT) as "INIT",
     TRIM(street) as "ADDRESS", 
     CITY as "CITY",
     STATE as "STATE",
     cast(digits(ZIPCOD) as varchar(6)) as "ZIPCODE"
   )
  FROM qiws.qcustcdt;

You can see that I’ve embedded the XMLROW( just after the “SELECT” keyword, and placed the closing paren just after the “ZIPCODE” label for the ZIPCOD column.

That’s it! It will now output a well-formed XML document. Where’s an excerpt:

<row>
 <CUSNUM>938472</CUSNUM>
 <LASTNAME>Henning</LASTNAME>
 <INIT>G K</INIT>
 <ADDRESS>4859 Elm Ave</ADDRESS>
 <CITY>Dallas</CITY>
 <STATE>TX</STATE>
 <ZIPCODE>75217</ZIPCODE>
</row>

Each row should have a container, so XMLROW inserts the default container node name of “row.” You can override this by specify the OPTION ROW clause after the last column, as follows:

SELECT
  xmlrow(
     cusnum as "CUSNUM", 
     TRIM(LSTNAM) as "LASTNAME",
     TRIM(INIT) as "INIT",
     TRIM(street) as "ADDRESS", 
     CITY as "CITY",
     STATE as "STATE",
     cast(digits(ZIPCOD) as varchar(6)) as "ZIPCODE"
   OPTION ROW "CUSTOMER")
FROM qiws.qcustcdt;

This would insert the <CUSTOMERS> node around each set of column nodes; replacing the <row> node that is inserted by default.

There is one more option on the XMLROW function: the “AS ATTRIBUTES” clause. This cause the XMLROW to create one tag/node for each row. The tag/node is named just like before, that is by default it is called “row” otherwise your user-specified name is issued. However, each column is generated as an XML attribute. So that first row I showed you earlier, would instead be generated as follows:

Depending on your specific needs, AS ATTRIBUTES could be a great option.

In most environments the entire set of XML data should also be wrapped in one larger outer node. While SQL iQuery OUTPUT(*XML) has an XMLOPT parameter that supports this feature, native XML doesn’t seem to have an easy way to make that happen. So my advice is this:

If you are using SQL to generate XML and need that XML saved to an IFS file, you should be using either IFS APIs to write out the data or using the IFS_WRITE SQL stored procedures. In either case, you can simply call the output routine before you send out the XML content and then after you write all of it out to the IFS file to enclose the XML in the outer most node wrapper. Something like that which is illustrated in this work RPG IV example. I’ve also posted this code over on my GitHub page.

That’s all there is to it.

           ctl-opt dftactgrp(*NO);

           // ------------------------------------------------------
           // How to generate XML from Db2 and save that XML content
           // to the IFS as an ASCII text file.
           // ------------------------------------------------------

           dcl-s  content  SQLTYPE(CLOB:65532);
           dcl-s  start    int(10);
           dcl-s  ifsXMLFile varchar(1024) INZ('/home/<USRPRF>/DEMO.XML');
           dcl-s  ifsUser  varchar(10) INZ(*USER);

           dcl-s parentNode varchar(16) inz('CUSTOMERS>');

           exec SQL SET OPTION commit=*NONE, NAMING=*SYS;
            *INLR = *ON;

            EXEC SQL DECLARE XC CURSOR for
               SELECT
                 xmlrow(
                    cusnum as "CUSNUM",
                    TRIM(LSTNAM) as "LASTNAME",
                    TRIM(INIT) as "INIT",
                    TRIM(street) as "ADDRESS",
                    CITY as "CITY",
                    STATE as "STATE",
                    cast(digits(ZIPCOD) as varchar(6)) as "ZIPCODE"
                  OPTION ROW "CUSTOMER" )
              FROM QIWS.QCUSTCDT;

             EXEC SQL OPEN XC;

               // Read XML into a CLOB or you'll have a learning experience.
             EXEC SQL FETCH XC INTO :content;

             if (SQLState < '02000');
             ifsXMLFile  = %SCANRPL('<USRPRF>' : %TrimR(ifsUser) : ifsXMLFile);
                // write out the starting/opening node to the IFS file
               EXEC SQL call qsys2.ifs_write_UTF8( :ifsXMLFile,
                                                   '<' concat :parentNode );
               DOW (SQLState < '02000');
                  // XMLROW returned via RPG IV SQL FETCH adds the <?xml...> tag
                  // We don't want that, so we skip past it using POSITION and SUBSTR
                 EXEC SQL VALUES POSITION('<CUSTOMER>', :content) INTO :START;
                 EXEC SQL call qsys2.ifs_write_UTF8( :ifsXMLFile,
                                                    substr(:content,:start));
                 EXEC SQL FETCH XC INTO :content;
               enddo;
                // write out the ending/closing node to the IFS file
               EXEC SQL call qsys2.ifs_write_UTF8( :ifsXMLFile,
                                                   '</' concat :parentNode );
             endif;
             EXEC SQL CLOSE XC;

Bob Cozzi helps IBM i clients solve complex issues using SQL, RPG IV, or C/C++ as well as the SQL iQuery licensed program (the best way to modernize your Query/400 with SQL). Visit his website at http://www.SQLiQuery.com. Cozzi is also the author of The Modern RPG Language, developer of SQL iQuery and SQL Tools, and a speaker on SQL and RPG IV topics you can actually use.

RELATED STORIES

Guru: Retrieving The Long And Short Object Name

Guru: Object Usage Statistics

Guru: Binding Directory Entries

Guru: Find Unused Objects On IBM i Using SQL

What’s In the Top 5 Hottest IBM i RFEs

Cozzi Refines Data-Centric Ideas For IBM i Report Writer

Cozzi Updates RPG xTools, Partners with Linoma