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

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags: Tags: C/C++, IBM i, RPG, RPG IV, SQL, SQL iQuery, SQL XML, XML, XMLROW

    Sponsored by
    Maxava

    Migrate IBM i with Confidence

    Tired of costly and risky migrations? Maxava Migrate Live minimizes disruption with seamless transitions. Upgrading to Power10 or cloud hosted system, Maxava has you covered!

    Learn More

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Rocket Buys Data Integration Provider B.O.S. Facing The Challenges Of Upgrading Old Systems With The Cloud

    2 thoughts on “Guru: Generating XML Using SQL – The Easy Way”

    • Ewart says:
      September 25, 2023 at 1:52 am

      Sorry, I am not very strong in SQL, so please excuse my silly question. But if I need all the fields in the file to be considered why doesn’t the below statement work?
      select xmlrow(*) from QIWS.QCUSTCDT

      Reply
      • Bob Cozzi says:
        December 2, 2023 at 10:13 am

        The only response I can give is because it doesn’t. The “SELECT *” is not something SQL advocates embrace. Doesn’t matter if it looks easier to RPG/DDS developers or not, fact is, it can introduce a “level check” type of issue if the file changes in the future. Which is why they don’t advocate SELECT * in the first place.

        Reply

    Leave a Reply Cancel reply

TFH Volume: 33 Issue: 55

This Issue Sponsored By

  • New Generation Software
  • Racksquared
  • WorksRight Software
  • Focal Point Solutions Group
  • Raz-Lee Security

Table of Contents

  • The Subscription Pricing For The IBM i Stack So Far
  • Facing The Challenges Of Upgrading Old Systems With The Cloud
  • Guru: Generating XML Using SQL – The Easy Way
  • Rocket Buys Data Integration Provider B.O.S.
  • IBM i PTF Guide, Volume 25, Number 37

Content archive

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

Recent Posts

  • POWERUp 2025 –Your Source For IBM i 7.6 Information
  • Maxava Consulting Services Does More Than HA/DR Project Management – A Lot More
  • Guru: Creating An SQL Stored Procedure That Returns A Result Set
  • As I See It: At Any Cost
  • IBM i PTF Guide, Volume 27, Number 19
  • IBM Unveils Manzan, A New Open Source Event Monitor For IBM i
  • Say Goodbye To Downtime: Update Your Database Without Taking Your Business Offline
  • i-Rays Brings Observability To IBM i Performance Problems
  • Another Non-TR “Technology Refresh” Happens With IBM i TR6
  • IBM i PTF Guide, Volume 27, Number 18

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