Wednesday, August 15, 2018

Getting data from XML file directly into data structure array

xml straight into file

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:

 

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

5 comments:

  1. Hello, I'm Alessandro, I have to read an XML for the first time and I'm trying with the SQL looking at your example.
    In the XML there is this line: that is the first node or the principal tag (I don't know the right name)
    When I run the SQL with XMLTABLE('ns3:FatturaElettronica...etc I get the sqlcode error -16005
    After a lot of test the problem is the character ":"...if I remove the ":" in the XML (beginning and end) the SQL returns the data!
    Why? I don't know...Well, do you know how can I automatically remove this character? Or bypass it with some option?
    The XML comes from the italian Internal Revenue Service so is not possible to change it at the creation...
    The machine is a 7.3.

    Thanks and sorry for my English
    Alessandro

    ReplyDelete
    Replies
    1. To remove the ":" I would try using the SQL REPLACE function within the XMLTABLE function.
      For REPLACE function see here.

      Delete
    2. Hi Simon, thanks for your answer...I tried a little, but I don't know the right way to insert the REPLACE function within the XMLTABLE function... :-(
      This is my SQL:
      SELECT *
      FROM XMLTABLE('ns3:FatturaElettronica/FatturaElettronicaBody/DatiGenerali/DatiDDT'
      PASSING XMLPARSE(DOCUMENT GET_XML_FILE('/tmp/fatxml.xml'))
      COLUMNS
      NUMDDT CHAR(40) PATH 'NumeroDDT',
      DATDDT CHAR(10) PATH 'DataDDT',
      RIFNUMLIN CHAR (10) PATH 'RiferimentoNumeroLinea') ;

      This is how fatxml.xml is:





      AAAAAAAAAAAAAAA
      2023-08-07
      1





      Can you help me again?
      Thanks Alessandro

      Delete
    3. "ns3:" is a name space.
      I would try what IBM suggests on this page =>
      https://www.ibm.com/docs/en/i/7.5?topic=table-using-xmltable-namespaces

      Delete
    4. Hi..."XMLTABLE('*:FatturaElettronica/..." so easy that I can't believe it...works perfectly...sorry to have bothered you.

      Thank you very much!
      Alessandro

      Delete

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.