Everybody knows Giovanni B. Perotti and his great work with Mel Rothmann for CGIDEV2 Open Source project, he is retired but often pop out some new utilities, like his last work EZ4PARSE.
EZ4PARSE is an utility to convert xml document into a phisical files: more often whe face the necessity to extract XML data from XML documents … if we could see them like normal db2 tables life would be simple for RPG old school programmers like me.
So let’s we try with some simple XML sample documents.
Download and Install EZ4PARSE utility
First of all we have to download and install EZ4PARSE utility from Easy400 downlad page: http://www.easy400.net/ez4parse/html/page1.htm
Sure enough download and install this utility it’s easy, thanks G.B.Perotti !
A CD Catalog XML Document
If you google for XML sample one of first results is http://www.w3schools.com/xml/xml_examples.asp … I downloaded “CD Catalog XML doc” on my IFS and I created a destination table with exactly the same columns name:
CREATE TABLE QUSRSYS/CDCATXML ( TITLE CHAR (50) NOT NULL WITH DEFAULT '', ARTIST CHAR (50) NOT NULL WITH DEFAULT ' ', COUNTRY CHAR (50) NOT NULL WITH DEFAULT ' ', COMPANY CHAR (50) NOT NULL WITH DEFAULT ' ', PRICE DEC (15, 4) NOT NULL WITH DEFAULT 0, YEAR DEC (4, 0) NOT NULL WITH DEFAULT 0 ) RCDFMT CDCATXMLR;
Now we are ready to extract data from XML document and insert them in our DB2 table
XMLPARSE STMF(‘/smedoc/ute/roberto/cd_catalog.xml’) TOFILE(QUSRSYS/CDCATXML)
Ok, now we can treat our table as we are used to
Microsoft Sample XML file (books.xml)
Now let we try with another famous XML document from Microsoft MSDN : https://msdn.microsoft.com/en-us/library/ms762271(v=vs.85).aspx
In this case we have and XML with long columns name (“publish_date” and “description”and we fall into some problems
CREATE TABLE QUSRSYS/BOOKSXML ( id CHAR (15) NOT NULL WITH DEFAULT '', author CHAR (50) NOT NULL WITH DEFAULT ' ', title CHAR (50) NOT NULL WITH DEFAULT ' ', genre CHAR (50) NOT NULL WITH DEFAULT ' ', price DEC (14, 4) NOT NULL WITH DEFAULT 0, publish_date FOR COLUMN publishdt DATE , description FOR COLUMN descriptn VARCHAR (100) NOT NULL WITH DEFAULT ' ', PRIMARY KEY (id) ) RCDFMT BOOKSXMLR;
As you can see in this SQL statement for long columns name “publish_date” and “description” I used “for column” syntax to give them a better system name (I hate system names like “PUBLI00001” or “DESCRI00001” !).
If we call XMLPARSE
XMLPARSE STMF(‘/smedoc/ute/roberto/books.xml’) TOFILE(QUSRSYS/BOOKSXML)
and we get a CPF9898 Message
Message. . . : The XML parser ended in error, see the previous joblog
message. However, 0 records were loaded to file QUSRSYS/BOOKSXML, member
Oh, I need some help. Ask to the author GB Perotti who reply with a workaround and a promise for next EZ4PARSE release.
The problem is: XML-Into function ignore alternate column names and use obly system column name. We can rename xml tag names with an editor o a simple program or we can ignore those columns and load other data in the table with this command:
XMLPARSE STMF(‘/smedoc/ute/roberto/books.xml’) TOFILE(QUSRSYS/BOOKSXML) DFTOPTIONS(*NO) ALWMISSING(*YES) ALWEXTRA(*YES)
And now the GB Perotti’s promise: next EZ4PARSE release will have a command to rename XML tags ! … Thanks again Giovanni !
You can download source code here: