将大量XML数据插入到单个表中

Question

我试图做一个INSERT INTO ..从xmltable中选择。这个过程在没有插入的情况下成功(快速)完成，但是当包含这个代码来插入9999行时，它需要花费时间来完成，但是表中没有任何记录。

文件代码片段:

<xyzcompany xmlns="http://www.xyzcompany.com/Canonical" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:pmt="http://xyzcompany.com/PmtInfo" xmlns:cmn="http://xyzcompany.com/CommonTypes">
<Pmt>
<pmt:Payments>
<pmt:PayInfo>
<pmt:Single>
<pmt:SingExtRef>HH00000066530</pmt:SingExtRef>
<pmt:SingSequence>0001</pmt:SingSequence>
<pmt:SingAddInfo>
   <cmn:AddInfoCmpType>IndivID</cmn:AddInfoCmpType>
   <cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
   <cmn:AddInfoCmpText>30UAT23203</cmn:AddInfoCmpText>
</pmt:SingAddInfo>
<pmt:SingAddInfo>
  <cmn:AddInfoCmpType>Transaction Reference</cmn:AddInfoCmpType>
  <cmn:AddInfoCmpText>062001180000553</cmn:AddInfoCmpText>
</pmt:SingAddInfo>
<pmt:SingAddInfo>
<cmn:AddInfoCmpType>EntryDesc</cmn:AddInfoCmpType>
<cmn:AddInfoCmpText>TRADE PYMT</cmn:AddInfoCmpText>
</pmt:SingAddInfo>
<pmt:SingAddInfo>
<cmn:AddInfoCmpType>ACH Company ID</cmn:AddInfoCmpType>
<cmn:AddInfoCmpText>1444444444</cmn:AddInfoCmpText>
</pmt:SingAddInfo>
<pmt:SingAddInfo>
<cmn:AddInfoCmpType>PmtRelInfo0001</cmn:AddInfoCmpType>
<cmn:AddInfoCmpText>IV</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>UAT9999_00001</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>100.01</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
</pmt:SingAddInfo>
<pmt:SingAddInfo>
<cmn:AddInfoCmpType>PmtRelInfo0002</cmn:AddInfoCmpType>
<cmn:AddInfoCmpText>IV</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>UAT9999_00002</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>100.01</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
<cmn:AddInfoCmpText>NA</cmn:AddInfoCmpText>
</pmt:SingAddInfo>
</pmt:Single>
</pmt:PayInfo>
</pmt:Payments>
</Pmt>
</xyzcompany>

上述文件的PMTRELINFO001最大可达PMTRELINFO9999。我只需要抓取这些数据，而不是其他数据，如INDIVID，并插入到目标表中。我的代码如下:

INSERT INTO ach_addenda(ach_id,rmr_number,rmr_code,invoice_number,rmr_paid_amt,rmr_orig_amt,rmr_disc_amt,rmr_date,ref_code,ref_id,ref_name,ref_instructions,adx_instructions,
        ent_number)
        (SELECT app_data_seq.currval, 
                 sing_d1.sing_info_type,
                 sing_d1.sing_info_text1,
                 sing_d1.sing_info_text2,
                 sing_d1.sing_info_text3,
                 sing_d1.sing_info_text4,
                 sing_d1.sing_info_text5,
                 sing_d1.sing_info_text6,
                 sing_d1.sing_info_text7,
                 sing_d1.sing_info_text8,
                 sing_d1.sing_info_text9,
                 sing_d1.sing_info_text10,
                 sing_d1.sing_info_text11,
                 sing_d1.sing_info_text12

            FROM XMLTABLE (
                    xmlnamespaces (
                       'http://www.xyzcompany.com/Canonical' AS "xyz",
                       'http://xyzcompany.com/PmtInfo' AS "pmt",
                       'http://xyzcompany.com/CommonTypes' AS "cmn",
                       'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
                    '/xyz:xyz/xyz:Pmt/pmt:Payments/pmt:PayInfo/pmt:Single/pmt:SingAddInfo[cmn:AddInfoCmpType = "PMTREL%"]'
                    PASSING payXml
                    COLUMNS sing_info_type VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpType',
                            sing_info_text1 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[1]',
                            sing_info_text2 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[2]',
                            sing_info_text3 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[3]',
                            sing_info_text4 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[4]',
                            sing_info_text5 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[5]',
                            sing_info_text6 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[6]',
                            sing_info_text7 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[7]',
                            sing_info_text8 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[8]',
                            sing_info_text9 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[9]',
                            sing_info_text10 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[10]',
                            sing_info_text11 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[11]',
                            sing_info_text12 VARCHAR2(4000)
                                  PATH 'cmn:AddInfoCmpText[12]'
                            ) sing_d1
                            );

任何提高效率和使插入工作的建议都是值得赞赏的。我也试过用追加提示插入，没有区别。

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0产品
TNS for Solaris: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Answer 1

我在你的查询中看到3个错误。

   XMLTABLE (
                    xmlnamespaces (
                       default 'http://www.xyzcompany.com/Canonical',
                       'http://xyzcompany.com/PmtInfo' AS "pmt",
                       'http://xyzcompany.com/CommonTypes' AS "cmn",
                       'http://www.w3.org/2001/XMLSchema-instance' AS "xsi"),
                    '/xyzcompany/Pmt/pmt:Payments/pmt:PayInfo/pmt:Single/pmt:SingAddInfo[contains(upper-case(cmn:AddInfoCmpType),"PMTREL")]'
                    PASSING a ).

1)根节点为xyzcompany而非xyz

2) http://www.xyzcompany.com/Canonical是默认的命名空间，应该这样装饰default 'http://www.xyzcompany.com/Canonical',

3) cmn:AddInfoCmpType = "PMTREL%"在xquery中没有类似的操作符。可以替换为pmt:SingAddInfo[contains(upper-case(cmn:AddInfoCmpType),"PMTREL")]