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.

Record1 Description1 1.000
Record2 Description2 2.000
Record3 Description3 3.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.

Offline inventory client thumbnail
Offline Inventory Client for Stocktaking without Network Connection
Video Thumbnail
Managing Infor ERP XA data with SystemLink and Visual Basic
Menü