Creating XML-Files via SQL

xmlwithsql Thumbnail

We will show you how to easily convert content of your database tables into an XML file.

And all it takes is just a SQL statement and a CL command. We start off by preparing a sample table, this one will have three columns and three rows for testing:

CREATE TABLE qtemp.TESTXML (FIELD1 CHAR (10 ) , FIELD2 CHAR (256 ), FIELD3 NUMERIC (10 , 3));
INSERT INTO qtemp.TESTXML VALUES('Record1', 'Description1', 1);
INSERT INTO qtemp.TESTXML VALUES('Record2', 'Description2', 2);
INSERT INTO qtemp.TESTXML VALUES('Record3', 'Description3', 3);

After executing the commands from above, our sample table has been created.

Record1Description11.000
Record2Description22.000
Record3Description33.000

 

Additionally, we need a temporary table to store our XML data:

create table qtemp.myxml as (select xmlserialize( 
                                    xmlelement(NAME "MyXMLRecord", xmlelement(NAME "RecNbr", field3), 
                                    xmlelement(NAME "RecordName", trim(field1)),
                                    xmlelement(NAME "RecordDescription", trim(field2))) 
                              as char(1024)) as "XMLResult" from qtemp.testxml) with data;

As soon as this is done, our data has been stored within the table and we just have to export them into a text file in the IFS. You can do this by using the following CL command:

CPYTOIMPF FROMFILE(MYXML) TOSTMF('/myxml.xml') MBROPT(*REPLACE) FROMCCSID(1252) RCDDLM(*CRLF) STRDLM(*NONE)

The final result will look like this:

<MyXMLRecord><RecNbr>1.000</RecNbr><RecordName>Record1</RecordName><RecordDescription>Description1</RecordDescription></MyXMLRecord> 
<MyXMLRecord><RecNbr>2.000</RecNbr><RecordName>Record2</RecordName><RecordDescription>Description2</RecordDescription></MyXMLRecord> 
<MyXMLRecord><RecNbr>3.000</RecNbr><RecordName>Record3</RecordName><RecordDescription>Description3</RecordDescription></MyXMLRecord>

Other structures within the XML can easily be created by using the respective keywords.

Enterprise Application Integration using Webservices
Managing Infor ERP XA data with SystemLink and Visual Basic