Extracting values from XML column in Oracle PLSQL

Question

SELECT   x.xml_data.getClobVal()
FROM   test_xml x;

Results of above query is:

<CstmrCdtTrfInitn>
<GrpHdr>
<MsgId>191189    </MsgId>
</GrpHdr>
<PmtInf>
<PmtInfId>191189_65437    </PmtInfId>
<PmtMtd>EFT    </PmtMtd>
<NbOfTxs>2    </NbOfTxs>
<CtrlSum>3000.00    </CtrlSum>
<PmtTpInf>
<SvcLvl>
<Cd>NURG    </Cd>
</SvcLvl>
<LclInstrm>
<Cd>SDCL    </Cd>
</LclInstrm>
</PmtTpInf>
<PmtInf>
<PmtInfId>191189_65437    </PmtInfId>   
<RmtInf>
<Strd>
<RfrdDocInf>
<Tp>
<CdOrPrtry>
<Cd>CINV    </Cd>
</CdOrPrtry>
<Issr>Office Supplies    </Issr>
</Tp>
<Nb>3    </Nb>
<RltdDt>2022-01-24    </RltdDt>
</RfrdDocInf>
<RfrdDocAmt>
<DuePyblAmt Ccy="EUR">1000.00    </DuePyblAmt>
</RfrdDocAmt>
</Strd>
<Strd>
<RfrdDocInf>
<Tp>
<CdOrPrtry>
<Cd>CINV    </Cd>
</CdOrPrtry>
<Issr>Office Supplies    </Issr>
</Tp>
<Nb>4    </Nb>
<RltdDt>2022-01-24    </RltdDt>
</RfrdDocInf>
<RfrdDocAmt>
<DuePyblAmt Ccy="EUR">1000.00    </DuePyblAmt>
</RfrdDocAmt>
</Strd>
</RmtInf>
</PmtInf>

If I use:

select x.File_name, y.* 
from test_xml x, XMLTABLE('/CstmrCdtTrfInitn' passing  x.xml_data
COLUMNS  
--  CdtrInstrId PATH 'PmtInf/CdtTrfTxInf/PmtId/InstrId'
--,CdtrNm PATH 'PmtInf/CdtrAcct/Nm2'
 StrdCd PATH 'PmtInf/RmtInf/Strd/RfrdDocInf/Tp/CdOrPrtry/Cd'
) y 
  where File_name = 2;

throws an error even though EXTRACTVALUE is not used:

ORA-19025: EXTRACTVALUE returns value of only one node
19025. 00000 -  "EXTRACTVALUE returns value of only one node"
*Cause:    Given XPath points to more than one node.
*Action:   Rewrite the query so that exactly one node is returned.     

Need help to know how can I extract the values when there are repeating nodes such as tag Strd or tag RfrdDocInf etc. Appreciate the help.

Answer 1

I think you haven't quite understood how XMLTABLE works.

An XMLTABLE expression takes the form

XMLTABLE('row_xpath' PASSING xml_source COLUMNS
  column1 datatype PATH 'col1_xpath',
  column2 datatype PATH 'col2_xpath'
  ...
)

Each matching result from the XPath expression row_xpath within xml_source corresponds to one row in the returned table. The XPath expressions col1_xpath, col2_xpath and so on contain the XPath expressions to select the values that make up each row from each element matching row_xpath.

In your XMLTABLE, your row_xpath is '/CstmrCdtTrfInitn'. This selects only the top-level element CstmrCdtTrfInitn, so your XMLTABLE will only ever return one row.

If we want our XMLTABLE to generate one row for each Cd element, then we need our row_xpath to select elements that only include one Cd. Looking at your document, we could select the Strd elements for this purpose, because each of the two Strd elements only contains one Cd. We can't use the RmtInf element for this purpose because it contains the two Cd elements. So, let's adjust the row-XPath to select the Strd elements and adjust the column-XPath to select the Cd element from within each Strd element:

SQL> select x.file_name, y.*
  2  from test_xml x,XMLTABLE('/CstmrCdtTrfInitn/PmtInf/RmtInf/Strd' passing  x.xml_data
  3  COLUMNS
  4  --  CdtrInstrId PATH 'PmtInf/CdtTrfTxInf/PmtId/InstrId'
  5  --,CdtrNm PATH 'PmtInf/CdtrAcct/Nm2'
  6   StrdCd VARCHAR2(50 CHAR) PATH 'RfrdDocInf/Tp/CdOrPrtry/Cd'
  7   ) y
  8    where File_name = 2;

 FILE_NAME STRDCD
---------- --------------------------------------------------
         2 CINV
         2 CINV

You can then modify your XMLTABLE query to read further values out of your XML document and into other columns of the returned table. In the example below I read out the value of the Nb elements:

SQL> select x.file_name, y.*
  2  from test_xml x,XMLTABLE('/CstmrCdtTrfInitn/PmtInf/RmtInf/Strd' passing  x.xml_data
  3  COLUMNS
  4  --  CdtrInstrId PATH 'PmtInf/CdtTrfTxInf/PmtId/InstrId'
  5  --,CdtrNm PATH 'PmtInf/CdtrAcct/Nm2'
  6   StrdCd VARCHAR2(50 CHAR) PATH 'RfrdDocInf/Tp/CdOrPrtry/Cd',
  7   StrdNb INTEGER PATH 'RfrdDocInf/Nb'
  8   ) y
  9    where File_name = 2;

 FILE_NAME STRDCD                                                 STRDNB
---------- -------------------------------------------------- ----------
         2 CINV                                                        3
         2 CINV                                                        4

---

Note also that in the last two examples I've specified the data-type of the value pulled out the XPath (VARCHAR2(50 CHAR) and INTEGER). In fact, if you modify your original XMLTABLE expression to add the column data-type in the same way, then you get a slightly more descriptive error:

SQL> select x.File_name, y.*
  2  from test_xml x, XMLTABLE('/CstmrCdtTrfInitn' passing  x.xml_data
  3  COLUMNS
  4  --  CdtrInstrId PATH 'PmtInf/CdtTrfTxInf/PmtId/InstrId'
  5  --,CdtrNm PATH 'PmtInf/CdtrAcct/Nm2'
  6   StrdCd VARCHAR2(50 CHAR) PATH 'PmtInf/RmtInf/Strd/RfrdDocInf/Tp/CdOrPrtry/Cd'
  7  ) y
  8    where File_name = 2;
select x.File_name, y.*
*
ERROR at line 1:
ORA-19279: XPTY0004 - XQuery dynamic type mismatch: expected singleton sequence
- got multi-item sequence

This tells you that you were expecting StrdCd to have a single value, but multiple elements matched the XPath expression PmtInf/RmtInf/Strd/RfrdDocInf/Tp/CdOrPrtry/Cd.