[SOLVED] How can I parse correctly this XML file with parameter in main TAG?


I have a file XML in the iSeries IFS.

This is the initial parts of the file:

<?xml version="1.0" encoding="UTF-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.054.001.04">

I need to extract some tags, but doesn’t work

SELECT XMLDOC.*                                                     
FROM (VALUES(XMLPARSE(DOCUMENT GET_XML_FILE('/temp/CAMT054.xml')) )) AS XMLFILE(FATTURE),                                             
XMLTABLE('$doc/Document/BkToCstmrDbtCdtNtfctn/Ntfctn/Ntry/NtryDtls/TxDtls' PASSING XMLFILE.FATTURE AS  "doc" 
Reference         VARCHAR(27)       PATH 'RmtInf/Strd/CdtrRefInf/Ref',
Amount            DECIMAL(17, 2)    PATH 'AmtDtls/TxAmt/Amt'

But if I manually delete this part from the tag "DOCUMENT", all work correctly


How can I bypass this problem?
I see XMLNAMESPACES function, but I didn’t understand if it’s useful for my problem.


The XML sample has a default namespace.

All XML elements are bound to that default namespace, even if you don’t see it explicitly.

So you need to add a namespace handling as a first parameter to the XMLTABLE(..) function. Without it any XPath expression will not find XML elements.


XMLTABLE(XMLNAMESPACES(DEFAULT 'urn:iso:std:iso:20022:tech:xsd:camt.054.001.04'),
'$doc/Document/BkToCstmrDbtCdtNtfctn/Ntfctn/Ntry/NtryDtls/TxDtls' PASSING XMLFILE.FATTURE AS  "doc"

Answered By – Yitzhak Khabinsky

Answer Checked By – Mary Flores (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.