Testing EZ4PARSE (XML to PF) Utility by Giovanni B. Perotti

0
293

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
BOOKSXML.

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:

f4_xml01_sqlrpgle

f4_xml02_sqlrpgle

http://www.easy400.net/easy400p/maindown.html#d37