Creating a XML file in just one step using SQL

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.