如何使用Oracle Query从表的xmldata列中提取xml值

我有一个表xx_xml_data_table

ID XMLDATA creation_date XML_REQUEST_ID

1 10-JAN-2022 113862 10-jan 2022 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;

我需要这样的结果:

ID Invoice_number1 inv00011 inv00022 inv40012 inv4002

有谁能帮我一下吗?