Getting data from XML file directly into data structure array

I have written before about retrieving data from a XML file in the IFS. In that example I took that data from the file into a XML column in a DDL (SQL) table and then processed it from there. Since I published the example I have received two messages, one from Birgitta Hauser and another from Jan Koefoed-Nielsen, giving me examples of how to retrieve the data from the file and format it into columns, not using a DDL table as an interim step.

In this example I will not be directly outputting the information from the IFS XML file directly into an output file or table in IBM i. My experience of receiving XML files makes me want to validate what I am sent, before I start updating production files. A comma in a number, currency symbols, characters in what should be numeric value, and untranslatable characters should all be handled before updating any file. To this end I will be retrieving the data from the XML file and putting it into a data structure array. I can then "read" the array and perform any validations I want.

I am going to use the same XML file I have used before, xmlfile.xml, which resides in the folder MyFolder in the IFS of the IBM i. The XML file contains two "records", sets of data, for new orders. I have formatted it nicely so that you can see the individual XML elements:

<?xml version="1.0" encoding="UTF-8"?> <New_Orders> <Order> <Order_Number>15170Q</Order_Number> <Customer_Number>9877A</Customer_Number> <Order_Date>201805-01</Order_Date> <Order_Amount>100.23</Order_Amount> <Part_Number>1BTEF8U1</Part_Number> </Order> <Order> <Order_Number>565548</Order_Number> <Customer_Number>9877A</Customer_Number> <Order_Date>2018-05-6</Order_Date> <Order_Amount>9.51</Order_Amount> <Part_Number>AEW445</Part_Number> </Order> </New_Orders>

The program I created looks very similar to the one I created before. Let me start with what I call the "definition area".

01 **free 02 ctl-opt option(*nodebugio:*srcstmt) ; 03 dcl-ds Data qualified dim(9999) ; 04 OrderNo char(10) ; 05 Customer char(10) ; 06 OrderDate char(10) ; 07 OrderAmt char(10) ; 08 PartNo char(20) ; 09 end-ds ; 10 dcl-s Rows uns(5) inz(%elem(Data)) ; 11 exec sql SET OPTION COMMIT = *CHG, CLOSQLCSR = *ENDMOD ;

Line 1: This is going to be a totally free RPG program. Why write anything in columns anymore?

Line 2: My favorite control options to make any debugging on this program easier to follow.

Lines 3 – 9: This is the data structure array with subfields for all of the elements in the XML file. I have deliberately coded all subfields for the numeric elements in the XML to be character, therefore, I can validate them if they are truly numbers or something else.

Line 10: This variable will perform two functions. When the program starts it contains the number of elements in the data structure array. After data is fetched I will use it to contain the number of rows fetched.

Line 11: I always use the SQL set option to ensure when the program is compiled someone does not forget to change the default compile options needed to create this program. I think in all the other example programs I have shown I have always COMMIT = *NONE, but in this program it is different. The explanation will be later.

The rest of the program is pretty much only SQL statements:

12 clear Data ; 13 exec sql DECLARE C0 CURSOR FOR 14 SELECT A.* 15 FROM XMLTABLE('New_Orders/Order' 16 PASSING XMLPARSE(DOCUMENT 17 GET_XML_FILE('/MyFolder/xmlfile.xml')) 18 COLUMNS 19 OrderNo CHAR(10) PATH 'Order_Number', 20 Customer CHAR(10) PATH 'Customer_Number', 21 OrderDate CHAR(10) PATH 'Order_Date', 22 OrderAmt CHAR(10) PATH 'Order_Amount', 23 PartNo CHAR(20) PATH 'Part_Number' 24 ) AS A ; 25 exec sql OPEN C0 ; 26 exec sql FETCH C0 FOR :Rows ROWS INTO :Data ; 27 exec sql GET DIAGNOSTICS :Rows = ROW_COUNT ; 28 exec sql CLOSE C0 ;

Line 12: I guess I don't really need this line, but I have anyway.

Lines 13 – 24: The cursor definition includes all the statements necessary to take the XML data and parse it into columns.

Lines 13 and 14: Standard start of a cursor definition.

Line 15: XMLTABLE is used to define how to break the XML data into columns. 'New_Orders/Order' informs the XMLTABLE that when I define the columns they will be grandchildren of the New_Orders and children of the Order elements.

Line 16: XMLPARSE reformats the XML file's data into a XML value.

Line 17: GET_XML_FILE gets the data from the file in the IFS, that XMLPARSE converts into a XML value.

Line 18 – 23: The columns that will be returned are defined here.

DataStructureSubfield CHAR(x) PATH 'XML_element'

I defined the data type and size of the columns to be the same as data structure subfields.

Line 25: I open the cursor.

line 26: I fetch the same number of rows as there are elements in the data structure array into the data structure array. This is where the commit value comes into play. When I used the set option COMMIT = *NONE the fetch would fail with the SQL code, SQLCOD, of -443. The description of this error, see SQL0443, is cryptic as to the cause of this error. After trying various changes I discovered that the commit could be a change, COMMIT = *CHG, for this to work.

Line 27: I am retrieving the number of rows that were fetched so I can use it later to condition a For group I would use the "read" all of the retrieved data structure elements.

Line 28: I close the cursor.

In debug I can see that I retrieved the two sets of XML data into the data structure array:

EVAL data DATA.ORDERNO(1) = '15170Q ' DATA.CUSTOMER(1) = '9877A ' DATA.ORDERDATE(1) = '2018-05-01' DATA.ORDERAMT(1) = '100.23 ' DATA.PARTNO(1) = '1BTEF8U1 ' DATA.ORDERNO(2) = '565548 ' DATA.CUSTOMER(2) = '9877A ' DATA.ORDERDATE(2) = '2018-05-06' DATA.ORDERAMT(2) = '9.51 ' DATA.PARTNO(2) = 'AEW445 ' DATA.ORDERNO(3) = ' ' EVAL rows ROWS = 2

So what happens if I want to insert data into a file/table and I do not want it committed? Fortunately there is way to do that in the Insert statement:

29 exec sql INSERT INTO QTEMP.TESTTABLE 30 :Rows ROWS 31 VALUES(:Data) 32 WITH NC ;

Lines 29-32: This is a multiple row insert using the data structure array. And is another place I need to know how many rows were Fetched to know how many elements of the data structure array should be inserted.

Line 32: The NC stands for no commit, therefore, all inserts performed by this statement will not be committed.

In the real world I would not just insert the un-validated subfields from the data structure array into a file/table. This insert is for example purposes only.

 

You can learn more about this from the IBM website:

• XMLPARSE
• GET_XML_FILE

 

This article was written for IBM i 7.3, and will work for every release since 7.1 TR4.