I have table xx_xml_data_table

ID XMLDATA creation_date XML_REQUEST_ID

1 10-JAN-2022 11386 2 10-JAN-2022 11386

I have xmladata like this

<?xml version="1.0"?>
<COLLECTIONSET><PARTY_ID>123</PARTY_ID><ACCOUNT_ID>456</ACCOUNT_ID><LOCATION_ID>789</LOCATION_ID><CONTACT_ID>090</CONTACT_ID>
 <COLLECTION>
  <CURRSYSDATE>11/15/2022</CURRSYSDATE>
  <FIRST_NAME>FNAME</FIRST_NAME>
  <LAST_NAME>LNAMEt</LAST_NAME>
  <PAYMENT_HISTORY>
   <PAYMENT_HISTORY_ROW>
    <INVOICE_NUMBER>inv0001</INVOICE_NUMBER>
    <PAY_STATUS>OPEN</PAY_STATUS>
    <AMOUNT_DUE_ORIGINAL>123</AMOUNT_DUE_ORIGINAL>
    <AMOUNT_DUE_REMAINING>100</AMOUNT_DUE_REMAINING>
    <INVOICE_CURRENCY_CODE>INR</INVOICE_CURRENCY_CODE>
    <ACCTD_AMOUNT_DUE_REMAINING>100</ACCTD_AMOUNT_DUE_REMAINING>
    <FUNCTIONAL_CURRENCY>INR</FUNCTIONAL_CURRENCY>
    <DUE_DAYS>17</DUE_DAYS>
    <DUE_DATE>29-OCT-2022</DUE_DATE>
   </PAYMENT_HISTORY_ROW>
   <PAYMENT_HISTORY_ROW>
    <INVOICE_NUMBER>inv0002</INVOICE_NUMBER>
    <PAY_STATUS>OPEN</PAY_STATUS>
    <AMOUNT_DUE_ORIGINAL>555</AMOUNT_DUE_ORIGINAL>
    <AMOUNT_DUE_REMAINING>200</AMOUNT_DUE_REMAINING>
    <INVOICE_CURRENCY_CODE>INR</INVOICE_CURRENCY_CODE>
    <ACCTD_AMOUNT_DUE_REMAINING>200</ACCTD_AMOUNT_DUE_REMAINING>
    <FUNCTIONAL_CURRENCY>INR</FUNCTIONAL_CURRENCY>
    <DUE_DAYS>17</DUE_DAYS>
    <DUE_DATE>29-OCT-2022</DUE_DATE>
   </PAYMENT_HISTORY_ROW>
  </PAYMENT_HISTORY>
 </COLLECTION>
</COLLECTIONSET>

I tried this query but not working:

WITH inv AS
     (SELECT xt2.INVOICE_NUMBER,
             t.ID
           FROM xx_xml_data_table t,
        XMLTABLE('/COLLECTIONSET/COLLECTION/PAYMENT_HISTORY/PAYMENT_HISTORY_ROW' PASSING t.XMLDATA
                 COLUMNS "INVOICE_NUMBER" VARCHAR2(40)  PATH '@INVOICE_NUMBER' ) xt2
        where  t.XML_REQUEST_ID IN (11386,11387))
SELECT * FROM inv;

I need result like:

ID Invoice_number 1 inv0001 1 inv0002 2 inv4001 2 inv4002

Can anyone help me on this.

CodePudding user response：

In your example XML, INVOICE_NUMBER is a node, not an attribute, so your path should be 'INVOICE_NUMBER' rather than '@INVOICE_NUMBER'.

You can also simplify a bit - you don't seem to need the CTE:

SELECT xt2.INVOICE_NUMBER,
       t.ID
  FROM xx_xml_data_table t,
       XMLTABLE(
         '/COLLECTIONSET/COLLECTION/PAYMENT_HISTORY/PAYMENT_HISTORY_ROW'
         PASSING t.XMLDATA
         COLUMNS "INVOICE_NUMBER" VARCHAR2(40) PATH 'INVOICE_NUMBER'
       ) xt2
WHERE t.XML_REQUEST_ID IN (11386,11387);

fiddle

If your XMLDATA column is defined as a string (varchar2 or clob) then you will ned to convert that to XMLType in the PASSING clause; otherwise you'll get an error, which could be ORA-00932 or ORA-19224 or something else, depending on the actual data type; so you would do:

SELECT xt2.INVOICE_NUMBER,
       t.ID
  FROM xx_xml_data_table t,
       XMLTABLE(
         '/COLLECTIONSET/COLLECTION/PAYMENT_HISTORY/PAYMENT_HISTORY_ROW'
         PASSING XMLType(t.XMLDATA)
         COLUMNS "INVOICE_NUMBER" VARCHAR2(40) PATH 'INVOICE_NUMBER'
       ) xt2
WHERE t.XML_REQUEST_ID IN (11386,11387);

fiddle