How can I parse correctly this XML file with parameter in main TAG?-CodePudding

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">
    <BkToCstmrDbtCdtNtfctn>
        <GrpHdr>
            <MsgId>CAMT054_20220708_173908613_1OUMVN4N</MsgId>
            <CreDtTm>2022-07-08T17:39:08.613Z</CreDtTm>
            <MsgPgntn>
                <PgNb>1</PgNb>
                <LastPgInd>true</LastPgInd>
            </MsgPgntn>
            <AddtlInf>SPS/1.7/PROD</AddtlInf>
        </GrpHdr>
        <Ntfctn>

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" 
COLUMNS                                                                             
Reference         VARCHAR(27)       PATH 'RmtInf/Strd/CdtrRefInf/Ref',
Amount            DECIMAL(17, 2)    PATH 'AmtDtls/TxAmt/Amt'
 ) AS XMLDOC

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

xmlns="urn:iso:std:iso:20022:tech:xsd:camt.054.001.04"

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

CodePudding user response：

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.

SQL

...
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"
...