XMLTABLE – shred XML file into a DB2 file

30 Dec

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 ;

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: