Wednesday, June 6, 2018

Creating a XML file in just one step using SQL

writing xml directly from sql to ifs file

When I published the post about creating a XML file using SQL I received a message from Birgitta Hauser making a suggestion:

When using RPG Variables defined as SQLTYPE(CLOB: Length) the maximum supported length is 16 MB (RPG Limit) But there is no need to use RPG functions. Instead of writing the data into a variable first, It can be directly written into the XML-File in the VALUES ... INTO Statement.

She makes a very valid point. Many of my example programs have extra steps in them so that you, the reader, can see interim results. I feel this is a good thing as it helps to understand the processes shown.

In "real life" you would not want to perform those steps. This is a good example, if I wanted to generate a XML file I would want to take the data directly from the file/table and output it to a file in the IFS.

I am going to use the same example as I did in my previous example, and just make a few changes. When I do the program is a lot smaller than the previous example:

01  **free

02  dcl-s Outfile sqltype(xml_clob_file) ;
03  dcl-s Path varchar(100) inz('/MyFolder/xmlfile.xml') ;
04  dcl-s ThisCustomer char(10) inz('9877A') ;
05  dcl-s StartDate date(*iso) inz(d'2015-05-01') ;

06  exec sql SET OPTION COMMIT = *NONE, 
                        CLOSQLCSR = *ENDMOD, 
                        DATFMT = *ISO ;

07  clear Outfile ;
08  Outfile_Name = %trimr(Path) ;
09  Outfile_NL = %len(%trimr(Outfile_Name)) ;
10  Outfile_FO = SQFCRT ;

11  exec sql VALUES(SELECT XMLGROUP(RTRIM(ORDERNBR) 
                             AS "Order_Number",
                           RTRIM(CUSTOMER)
                             AS "Customer_Number",
                           ORDERDATE 
                             AS "Order_Date",
                           ORDERAMT 
                             AS "Order_Amount",
                           RTRIM(SKU) 
                             AS "Part_Number"
                           OPTION ROW "Order" 
                                  ROOT "New_Orders"
                           AS ATTRIBUTES)
                           FROM ORDERFILE
                           WHERE CUSTOMER = :ThisCustomer
                             AND ORDERDATE >= :StartDate)
              INTO :Outfile ;

12  *inlr = *on ;

Line 1: Why would I not write this in totally free RPG?

Lines 2: This is one of the changes. I have changed the variable definition SQL data type to XML_FILE_CLOB and I do not give a size. As this is a SQL data type and I am outputting directly from it I will not be limited by RPG's limit for a variable, 16MB. I can write up to 2GB of data directly to the file in the IFS.

The SQL precompiler translate the definition into a data structure:

DCL-DS OUTFILE;
  OUTFILE_NL UNS(10);
  OUTFILE_DL UNS(10);
  OUTFILE_FO UNS(10);
  OUTFILE_NAME CHAR(255) CCSID(*JOBRUNMIX);
END-DS OUTFILE;

These are the same subfields as was generated from a CLOB_FILE SQL data type.

  • OUTFILE_NAME: Name of the IFS file, including the full path.
  • OUTFILE_NL: Length of the IFS file's name.
  • OUTFILE_FO: File operation, more about this later.
  • OUTFILE_DL: Not used.

Lines 3 – 5: These variables are unchanged.

Line 6: I like to put the SQL options into the program's source rather that define them at compile time. If someone else compiles this program they do not have to worry about which compile options I used.

Lines 7 – 10: My definition of the output file in the IFS is the same as before. I use the value in the SQL precompiler constant SQFCRT as I want to create a new file for my output. See the previous post for the other possible values.

Line 11: This is the other change. The SQL statement may look very different from the previous example. It is just that I have formatted differently to fit on this page. The difference is on the last part, the INTO, I am moving the generated results into the variable Outfile. By doing this the results generated by the SQL statement are written directly to the file in the IFS. Wow!

The contents of the IFS XML file looks like (I have formatted the elements to their own lines to make it more readable):

<?xml version="1.0" encoding="UTF-8"?>
<New_Orders>
<Order Order_Number="15170Q" Customer_Number="9877A" 
Order_Date="2018-05-01" Order_Amount="100.23"
Part_Number="1BTEF8U1"/>
<Order Order_Number="56554B" Customer_Number="9877A"
Order_Date="2018-05-06" Order_Amount="9.51" 
Part_Number="AEW445"/>
<Order Order_Number="22231C" Customer_Number="9877A"
Order_Date="2018-04-15" Order_Amount="78.00" 
Part_Number="N/A"/>
</New_Orders>

Rather than use the SQL Value I could just use a Select into statement to achieve the same results:

11  exec sql SELECT XMLGROUP(RTRIM(ORDERNBR) AS "Order_Number",
                             RTRIM(CUSTOMER) AS "Customer_Number",
                             ORDERDATE AS "Order_Date",
                             ORDERAMT AS "Order_Amount",
                             RTRIM(SKU) AS "Part_Number"
                             OPTION ROW "Order" ROOT "New_Orders"
                             AS ATTRIBUTES)
               INTO :Outfile
               FROM ORDERFILE
              WHERE CUSTOMER = :ThisCustomer
                AND ORDERDATE >= :StartDate

 

This is another great example of showing how well RPG and SQL go together to do something complicated, simply.

 

This article was written for IBM i 7.3, and should work for earlier releases too.

4 comments:

  1. Spooky, a subject matter that was a discussion topic in our office today. Am I correct in thinking this functionality is only available in the latest OS release?

    ReplyDelete
    Replies
    1. I tested with IBM i 7.2 and the above code worked.

      I cannot speak for earlier releases as I do not have access to servers running older versions.

      Delete
  2. Writing XML Files is available since Release 7.1 (Introduction of XML support).
    But writing textes directly into the IFS with file reference variables (CLOB_FILE, DBCLOB_FILE, CLOB_FILE) could be already used in Release 7.1
    Birgitta

    ReplyDelete
  3. I got this to work. However, I'm wondering about a couple things.

    How would I go about creating a more elaborate XML header with additional XML groups that are not related to the actual data?

    Like custom schemas that are unique to what you are running?

    Also, this code produces a single line XML file. I would like to create a XML file that have a line breaks after each group so that it's easier to read when you are just viewing the file in a text editor.

    Thanks.

    ReplyDelete

To prevent "comment spam" all comments are moderated.
Learn about this website's comments policy here.

Some people have reported that they cannot post a comment using certain computers and browsers. If this is you feel free to use the Contact Form to send me the comment and I will post it for you, please include the title of the post so I know which one to post the comment to.