In AS400 or currently known as System i we have databases defined using DDS. So lets create 2 simple physical files representing Order Header and Order Detail Physical Files. These file are native to AS400(System i) and are easily accessible via RPGLE. You can also create Db2 table using SQL create table statements if you want. Or if you already have tables for which you want to generate XML using RPGLE is also not an issue. But here I am going to create two physical files using DDS.
Member Type Text ODETL PF Order Detail file OHEAD PF Order Header fileDDS Source for Order Detail Physical File
A UNIQUE A R ODETLR A X2DEL 1A COLHDG('Status') * A = Active * B = XML Processed A X2CMP 3S 0 COLHDG('Company No.') A X2ORD 9S 0 COLHDG('Order Number') A X2SEQ 4S 0 COLHDG('Order Line No.') A X2ITEM 20A COLHDG('Item Number') A X2DESC 30A COLHDG('Item Description') A X2QTY 8S 0 COLHDG('Order Quantity') A X2PRIC 11S 2 COLHDG('Item Price') A K X2CMP A K X2ORD A K X2SEQ
DDS Source for Order Header Physical File
A UNIQUE A R OHEADR A X1DEL 1A COLHDG('Status') * A = Active * B = XML Processed A X1CMP 3S 0 COLHDG('Company No.') A X1ORD 9S 0 COLHDG('Order Number') A X1TYPE 1A COLHDG('Transaction Type') * O = Sales Order * C = Credit A X1ORDT 8S 0 COLHDG('Order Date') A X1CUST 10A COLHDG('Customer Number') A X1PO 20A COLHDG('PO Number') A X1OTOT 11S 2 COLHDG('Order Total') A K X1CMP A K X1ORD
To display the data in these files i copied the file into CSV format for ease of display.
Commands to copy data into CSV
CPYTOIMPF FROMFILE(OHEAD) TOSTMF('/myxml/data/ohead.csv') MBROPT(*REPLACE) STMFCODPAG(*PCASCII) RCDDLM(*CRLF) RMVBLANK(*BOTH) CPYTOIMPF FROMFILE(OHEAD) TOSTMF('/myxml/data/ohead.csv') MBROPT(*REPLACE) STMFCODPAG(*PCASCII) RCDDLM(*CRLF) RMVBLANK(*BOTH)
Sample data in Order Header File
"A",1,101,"O",20110501,"ABC","TEST PO - 123",99.99 "A",1,102,"O",20110503,"ABC","TEST PO - 126",1212.12
Sample data in Order Detail File
"A",1,101,1,"MCP-X2","Cuisinart MultiClad Pro",5,10.00 "A",1,101,2,"DC25-X","Dyson Vacuum Cleaner",1,49.99 "A",1,102,1,"MMM-X123","Houdini Hand Blown Crystal",20,20.00 "A",1,102,2,"LG123456","French Door Refrigerator",1,800.12
If you want to use the sample data for your testing of XML data generation using RPGLE then copy the CSV files into the physical files using CPYFRMIMPF command.
Now that we have our input database ready lets generate the XML using the RPGLE program