我有表 xx_xml_data_table
ID XMLDATA 創建日期 XML_REQUEST_ID
1 2022 年 1 月 10 日 11386 2 2022 年 1 月 10 日 11386
我有這樣的 xmladata
<?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_number 1 inv0001 1 inv0002 2 inv4001 2 inv4002
誰可以幫我這個事。
uj5u.com熱心網友回復：
在您的示例 XML 中，INVOICE_NUMBER是一個節點，而不是一個屬性，因此您的路徑應該是'INVOICE_NUMBER'而不是'@INVOICE_NUMBER'.
您還可以稍微簡化一下——您似乎不需要 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);
| 發票號碼 | ID |
|---|---|
| inv0001 | 1個 |
| inv0002 | 1個 |
小提琴
如果您的XMLDATA列定義為字串（varchar2或），那么您需要在子句clob中將其轉換為 XMLType ；PASSING否則你會得到一個錯誤，它可能是 ORA-00932 或 ORA-19224 或其他，這取決于實際的資料型別；所以你會這樣做：
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);
| 發票號碼 | ID |
|---|---|
| inv0001 | 1個 |
| inv0002 | 1個 |
小提琴
轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/534404.html
標籤：XML甲骨文
上一篇：Jetpackcompose為網路檢索視頻幀導致延遲
下一篇：甲骨文資料庫|轉義整個字串變數