我有表xx_xml_data_table

标识XMLDATA创建日期XML请求标识

1 2022年1月10日11386 2 2022年1月10日11386
我有这样的xml数据

<?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>

我尝试了以下查询，但无效：

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;

我需要这样的结果：
发票标识_编号1发票0001 1发票0002 2发票4001 2发票4002
有人能帮我吗。