I initially tried loading a CSV download, but there were issues with stray quote marks in the data, which caused issues for CPYFRMIMPF. I switched to an XML download, because V7R1 on the iSeries has XMLTABLE.
Note the test for empty fields: [text () != “”]
Here is a code sample I use to import XML into a DB2 table.
EXEC SQL SET OPTION COMMIT = *ALL ;
@Stmt = ‘create alias QTEMP/BT__X for bat__v1(‘ +
%trim(@MbrName) + ‘)’;
exec sql execute immediate :@Stmt ;
SQL_Cmd = ‘insert into QTEMP/BT__X ‘ +
‘Select a.* from ‘ +
‘(VALUES(XMLPARSE(DOCUMENT GET_XML_FILE(”’ + %trim(@Dir) + ‘/’ +
%subst(d_name: 1: d_namelen) + ”’)))) as XMLFILE(Invoice), ‘ +
‘XMLTABLE(”Download/Invoice_Download” passing XMLFILE.INVOICE ‘ +
‘COLUMNS F1 Integer default 0 Path ”bill_to_acct_number’+
‘[text () != “”]” ,’ +
‘F2 Integer default 0 Path ”invoice_date[text () != “”]”,’+
‘F3 Integer default 0 Path ”invoice_number’ +
‘[text () != “”]”, ‘+
‘F4 Decimal(8,2) default 0 Path ”original_amount_due ‘ +
‘[text () != “”]”, ‘+
. . . .
) as a ‘ ;
exec sql execute immediate :SQL_Cmd ;
if sqlcod = 0 ;
commit ;
endif ;
@Stmt = ‘drop alias qtemp/BT__X’ ;
exec sql execute immediate :@Stmt ;
harleysavage 
Leave a Reply