oracle - 将 xml clob 插入到 oracle 中 xml 中提供的各个表中

Question

寻求帮助，从表中插入 xml 以插入 xml clob 中提供的相应表，如下 XML

<?xml version="1.0" encoding="UTF-8"?>
<STORESET STORE_KEY="11">
<DATASET NUM="1">
<ROWSET TABLE_NAME="CC_INVOICE">
<ROW NUM="1">
<SALES_STORE_KEY>11</SALES_STORE_KEY>
<INVOICE_DATE>19-Aug-21 10:24:33</INVOICE_DATE>
<CHECKOUT_ID>1</CHECKOUT_ID>
<INVOICE_ID>000060</INVOICE_ID>
<STORE_KEY>15</STORE_KEY>
<CUST_NO>123456</CUST_NO>
<AUTH_PERSON_ID>-1</AUTH_PERSON_ID>
<SELL_VAL_GSP_CHECKOUT>18133.0000</SELL_VAL_GSP_CHECKOUT>
<SELL_VAL_PAID>18133.0000</SELL_VAL_PAID>
<VAT_FREE_IND>0</VAT_FREE_IND>
<TRANSACTION_VALIDITY_IND>1</TRANSACTION_VALIDITY_IND>
<STAFF_IND>0</STAFF_IND>
<INVOICE_TYPE_CD>1</INVOICE_TYPE_CD>
<SPEC_INVOICE_TYPE_CD>1</SPEC_INVOICE_TYPE_CD>
<CASHIER_NO>110</CASHIER_NO>
<SELF_SCAN_TYPE_CD>0</SELF_SCAN_TYPE_CD>
<SURTAX_CD>0</SURTAX_CD>
<BOOKING_CD>0</BOOKING_CD>
<COD_INVOICE_TYPE_CD>0</COD_INVOICE_TYPE_CD>
<CUST_INVOICE_TYPE>NORM</CUST_INVOICE_TYPE>
<LEGAL_INVOICE_ID>0/0/0001/000060</LEGAL_INVOICE_ID>
<NBR_LINES>6</NBR_LINES>
</ROW>
</ROWSET>

<ROWSET TABLE_NAME="CC_INVOICE_LINE">
<ROW NUM="1">
<SALES_STORE_KEY>11</SALES_STORE_KEY>
<INVOICE_DATE>19-Aug-21 10:24:33</INVOICE_DATE>
<CHECKOUT_ID>1</CHECKOUT_ID>
<INVOICE_ID>000060</INVOICE_ID>
<INVOICE_LINE_ID>11</INVOICE_LINE_ID>
<ART_NO>290082</ART_NO>
<SELL_QTY_COLLI>1.0000</SELL_QTY_COLLI>
<GM_COND_TYPE_CD>0</GM_COND_TYPE_CD>
<CONT_SELL_UNIT>2.0000</CONT_SELL_UNIT>
</ROW>

<ROW NUM="2">
<SALES_STORE_KEY>11</SALES_STORE_KEY>
<INVOICE_DATE>19-Aug-21 10:24:33</INVOICE_DATE>
<CHECKOUT_ID>1</CHECKOUT_ID>
<INVOICE_ID>000060</INVOICE_ID>
<INVOICE_LINE_ID>13</INVOICE_LINE_ID>
<ART_NO>290082</ART_NO>
<SELL_QTY_COLLI>1.0000</SELL_QTY_COLLI>
<GM_COND_TYPE_CD>0</GM_COND_TYPE_CD>
<CONT_SELL_UNIT>2.0000</CONT_SELL_UNIT>
</ROW>

<ROW NUM="3">
<SALES_STORE_KEY>11</SALES_STORE_KEY>
<INVOICE_DATE>19-Aug-21 10:24:33</INVOICE_DATE>
<CHECKOUT_ID>1</CHECKOUT_ID>
<INVOICE_ID>000060</INVOICE_ID>
<INVOICE_LINE_ID>15</INVOICE_LINE_ID>
<ART_NO>38825</ART_NO>
<SELL_QTY_COLLI>1.0000</SELL_QTY_COLLI>
<GM_COND_TYPE_CD>0</GM_COND_TYPE_CD>
<CONT_SELL_UNIT>1.0000</CONT_SELL_UNIT>
</ROW>

<ROW NUM="4">
<SALES_STORE_KEY>11</SALES_STORE_KEY>
<INVOICE_DATE>19-Aug-21 10:24:33</INVOICE_DATE>
<CHECKOUT_ID>1</CHECKOUT_ID>
<INVOICE_ID>000060</INVOICE_ID>
<INVOICE_LINE_ID>17</INVOICE_LINE_ID>
<ART_NO>38825</ART_NO>
<SELL_QTY_COLLI>1.0000</SELL_QTY_COLLI>
<GM_COND_TYPE_CD>0</GM_COND_TYPE_CD>
<CONT_SELL_UNIT>1.0000</CONT_SELL_UNIT>
</ROW>

<ROW NUM="5">
<SALES_STORE_KEY>11</SALES_STORE_KEY>
<INVOICE_DATE>19-Aug-21 10:24:33</INVOICE_DATE>
<CHECKOUT_ID>1</CHECKOUT_ID>
<INVOICE_ID>000060</INVOICE_ID>
<INVOICE_LINE_ID>21</INVOICE_LINE_ID>
<ART_NO>38825</ART_NO>
<SELL_QTY_COLLI>1.0000</SELL_QTY_COLLI>
<GM_COND_TYPE_CD>0</GM_COND_TYPE_CD>
<CONT_SELL_UNIT>1.0000</CONT_SELL_UNIT>
</ROW>
</ROWSET>

<ROWSET TABLE_NAME="CC_SETTLEMENT_TYPE">
<ROW NUM="1">
<SALES_STORE_KEY>11</SALES_STORE_KEY>
<INVOICE_DATE>19-Aug-21 10:24:33</INVOICE_DATE>
<CHECKOUT_ID>1</CHECKOUT_ID>
<INVOICE_ID>000060</INVOICE_ID>
<SETTLEMENT_CD>1</SETTLEMENT_CD>
<PAYMENT_MODE_CD>1</PAYMENT_MODE_CD>
<SETTLEMENT_AMOUNT>18133.0000</SETTLEMENT_AMOUNT>
<VAT_FREE_IND>0</VAT_FREE_IND>
<CASHIER_NO>110</CASHIER_NO>
</ROW>
</ROWSET>

<ROWSET TABLE_NAME="CC_VAT_RATE">
<ROW NUM="1">
<SALES_STORE_KEY>11</SALES_STORE_KEY>
<INVOICE_DATE>19-Aug-21 10:24:33</INVOICE_DATE>
<CHECKOUT_ID>1</CHECKOUT_ID>
<INVOICE_ID>000060</INVOICE_ID>
<VAT_ID>11</VAT_ID>
<VAT_REC_TYPE_CD>0</VAT_REC_TYPE_CD>
<VAT_AMOUNT>861.0000</VAT_AMOUNT>
<SELL_VAL_NSP>10760.0000</SELL_VAL_NSP>
<SURTAX_RATE>8.0000</SURTAX_RATE>
</ROW>

<ROW NUM="2">
<SALES_STORE_KEY>11</SALES_STORE_KEY>
<INVOICE_DATE>19-Aug-21 10:24:33</INVOICE_DATE>
<CHECKOUT_ID>1</CHECKOUT_ID>
<INVOICE_ID>000060</INVOICE_ID>
<VAT_ID>41</VAT_ID>
<VAT_REC_TYPE_CD>0</VAT_REC_TYPE_CD>
<VAT_AMOUNT>592.0000</VAT_AMOUNT>
<SELL_VAL_NSP>5920.0000</SELL_VAL_NSP>
<SURTAX_RATE>10.0000</SURTAX_RATE>
</ROW>
</ROWSET>

</DATASET>

</STORESET>

我用来将 xml clob 加载到相应表中的过程代码：

select payload into in_xml from impor_xml where source = 'POS'; /*Source table where xml of above type is receiving*/

IF (in_xml IS NOT NULL) THEN
    FOR c IN (
        SELECT extractvalue(VALUE(xmlseq), 'ROWSET/@TABLE_NAME')  AS TABLE_NAME,
               extract(VALUE(xmlseq), 'ROWSET').getClobVal() AS xml_clob
        FROM   TABLE(XMLSequence(extract(XMLTYPE(in_xml), 'STORESET/DATASET/ROWSET'))) xmlseq
    )
    LOOP
        v_full_table_name := 'CRM'||'.'|| c.Table_Name;

        BEGIN
            v_context := DBMS_XMLStore.newcontext(v_full_table_name);
            v_rows := DBMS_XMLStore.insertXML(v_context, c.xml_clob);
            DBMS_XMLStore.closecontext(v_context);

            UPDATE in_xml set status = 1 where source = 'POS';
        END;

    LOOP END;
END IF ;

您能否让我知道上面的代码是否可以用任何其他方式重建，或者它必须在其内部完成。

太感谢了 ！！

标签： oracleplsql