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.
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?
ReplyDeleteI tested with IBM i 7.2 and the above code worked.
DeleteI cannot speak for earlier releases as I do not have access to servers running older versions.
Writing XML Files is available since Release 7.1 (Introduction of XML support).
ReplyDeleteBut 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
I got this to work. However, I'm wondering about a couple things.
ReplyDeleteHow 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.