1 2 Previous Next 16 Replies Latest reply: Dec 13, 2012 3:23 AM by 976707 RSS

    Import XML with more than 64k occurences of the same node

    976707
      I am trying various ways of importing data from a fairly large XML file into database tables and I'm countinuously hitting the wall of 2^16 node occurences, which generates an error.
      Has anyone ran into the same problem and found a workaround?

      The content is an ISO20022 formatted file with a bank-to-customer statement (camt53) with 70k+ transactions. One upper lever being the statement and 70k+ entries for that statement.

      My first approach was:
      1. load the XML file in a table with a CLOB column
      2. "load" the CLOB content into a xmltype variable
      declare
      l_xml_file xmltype;
      l_xml_package xmltype;
      l_xmlns varchar2(200); --namespace, logic not shown in example - irrelevant
      begin
      select t.clob_content
              into l_clob_temp
              from clob_table t
              where id = p_id
              for update
              ;
              l_xml_file := xmltype.createxml(l_clob_temp);
      l_xml_package := l_xml_file.extract('/Document/BkToCstmrStmt/*', l_xmlns);
      end;
      3. outer loop though the statements (1 occurence for now)
      loop
      exit when l_xml_package.existsnode('/Stmt['||l_stmt_counter||']', l_xmlns) = 0;
      ...
      /*fetch data using:*/
      l_xml_package.extract('/Stmt['||l_stmt_counter||']/LglSeqNb/text()', l_xmlns).getstringval();
      ...
      /*<inner loop through the >70k entries>*/
      end loop;
      4. inner loop through the entries, located on the end of the outer loop as shown above
      loop
      exit when l_xml_package.existsnode('/Stmt['||l_stmt_counter||']/Ntry['||l_ntry_counter||']', l_xmlns) = 0;
      ...
      /*fetch data using:*/
      l_xml_package.extract('/Stmt['||l_stmt_counter||']/Ntry['||l_ntry_counter||']/AcctSvcrRef/text()', l_xmlns).getstringval();
      ...
      /*insert data into table*/
      end loop;
      This approach was working fine while the number of entries was moderate - up to 10k, but when the number of entries grew, the processing time grew exponentially, ie. 5.000 rows in 5 minutes, 70.000 rows in an estimated 8h, I didn't wait for it to finish.

      To avoid this bottleneck I tried approach 2:
      1. created a table of xmltype
      2. converted my clob into xmltype:
      insert into xmltype_table(xml_content)
      select xmltype(clob_content)
      from clob_table
      3. created an index on xmltype_table.xml_content
      4. same loops and fetch as in the first approach, but from the xmltype table, hoping that the indexes and lack of conversion clob->xml would speed thing up

      The result was exactly the same as in approach 1 :-( , but with a twist!:
      If I fetched the n-th occurence, Oracle returned the values of the n-th occurence concatenated with the value of the (n+2^16)th occurence :-O

      Approach 3:
      1. created a table of xmltype
      2. converted my clob into xmltype:
      insert into xmltype_table(xml_content)
      select xmltype(clob_content)
      from clob_table
      3. created an index on xmltype_table.xml_content
      4. cursors using xmltable:
      cursor c_stmt (b_t_id number) is
      select x.LglSeqNb
      ,        x.Id
      from xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
                   ,'/Document/BkToCstmrStmt/Stmt'
                   passing (select t.xml_content from xmltype_table t where t.id = b_t_id)
                   columns LglSeqNb path 'LglSeqNb'
                   ,           Id path 'Id'
                   ) x
      ;
      cursor c_ntry(b_t_id number, b_stmt_id varchar2) is
      select x.Amt
      from xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
                   ,'/Ntry'
                   passing (select extract(t.xml_content, '/Document/BkToCstmrStmt/Stmt/Ntry', 'xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02"')
                            from xmltype_table t
                            where t.id = b_t_id
                            and   extractvalue(t.xml_content
                                              ,'/Document/BkToCstmrStmt/Stmt/Id'
                                              ,'xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02"'
                                              ) = b_stmt_id
                           )
                   columns Amt path 'Amt'
                   ) x
      ;
      5. loop through the cursors:
      for r_stmt in c_stmt loop
          --insert statement data into statement table
          for r_ntry in c_ntry loop
              --insert entry data into entry table
          end loop;
      end loop;
      As soon as the inner loop reaches 2^16th row it crashes:
      ORA-04030: out of process memory when trying to allocate 55600 bytes (kxs-heap-w,qmemNextBuf:Large Alloc)

      Approach 4:
      Same as approach 3, but using bulk collect limited to 1000 records at a time, same result.

      Approach 5:
      Instead of using cursors I used sql:
      insert into target_table (columns)
      ( select as in the cursors above )
      Same error.

      I'm running out of ideas, any suggestions?

      Database version:
      11.2.0.3.0, 64bit

      Edited by: 973704 on 28.11.2012 5:12

      Edited by: JurijC on Nov 28, 2012 2:20 PM - discovered /code/ formatting tags :-)
        • 1. Re: Import XML with more than 64k occurences of the same node
          odie_63
          Hi,

          Forget about CLOB and cursor loops. Use plain SQL over the XMLType table.

          Do you want to save Stmt and Ntry infos in two separate tables?
          What would be their structures (DDL)?

          See for example : http://odieweblog.wordpress.com/2012/05/10/how-to-load-xml-data-into-multiple-tables/
          • 2. Re: Import XML with more than 64k occurences of the same node
            976707
            That was my "Approach 5", it threw the above mentioned error:
            ORA-04030: out of process memory when trying to allocate 55600 bytes (kxs-heap-w,qmemNextBuf:Large Alloc)

            I'm trying to insert data into 2 tables, one master (inserting 1 record, works fine), one detail (inserting 70k records).

            I'll write an example and post the script.

            Edited by: 973704 on 28.11.2012 6:15
            • 3. Re: Import XML with more than 64k occurences of the same node
              976707
              I have just written a small example with 76.000 node occurences and it works normally, evidently the problem is in the server resources. I have been trying this on a test environment with inferior hardware, I'll try importing the data on a better server.
              I would have never imagined that a 84MB XML would be too much to hande in this day and age.
              • 4. Re: Import XML with more than 64k occurences of the same node
                odie_63
                I would have never imagined that a 84MB XML would be too much to hande in this day and age.
                It's not too much if you use the appropriate method.

                XML DB can load files up to several GBs.

                Check this example, I would do it like this :

                Set up :
                SQL> create table tmp_xml of xmltype
                  2  ;
                
                Table created.
                
                SQL> create table camt053_stmt (
                  2    Id       varchar2(30)
                  3  , LglSeqNb number
                  4  );
                
                Table created.
                
                SQL> create table camt053_ntry (
                  2    StmtId    varchar2(30)
                  3  , Amt       number
                  4  , Amt_Ccy   varchar2(10)
                  5  , CdtDbtInd varchar2(10)
                  6  , DtTm      timestamp with time zone
                  7  );
                
                Table created.
                Test :
                SQL> set timing on
                SQL> insert into tmp_xml values(
                  2   xmltype(bfilename('TEST_DIR','camt053.xml'),nls_charset_id('AL32UTF8'))
                  3  );
                
                1 row created.
                
                Elapsed: 00:00:08.89
                SQL> commit;
                
                Commit complete.
                
                Elapsed: 00:00:00.00
                SQL> insert all
                  2    when ( NtrySeq = 1 )
                  3      then into camt053_stmt (id, lglseqnb)
                  4                values (StmtId, LglSeqNb)
                  5    when ( NtrySeq is not null )
                  6       then into camt053_ntry (stmtid, amt, amt_ccy, cdtdbtind, dttm)
                  7                 values (StmtId, Amt, Amt_Ccy, CdtDbtInd, DtTm)
                  8  select x1.Id as StmtId
                  9       , x1.LglSeqNb
                 10       , row_number() over(partition by x1.Id order by null) NtrySeq
                 11       , x2.Amt
                 12       , x2.Amt_Ccy
                 13       , x2.CdtDbtInd
                 14       , x2.DtTm
                 15  from tmp_xml t
                 16     , xmltable(
                 17         xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
                 18       , '/Document/BkToCstmrStmt/Stmt'
                 19         passing t.object_value
                 20         columns LglSeqNb  number       path 'LglSeqNb'
                 21               , Id        varchar2(30) path 'Id'
                 22               , Ntry_COLL XMLType      path 'Ntry'
                 23       ) x1
                 24     , xmltable(
                 25         xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
                 26       , '/Ntry'
                 27         passing x1.Ntry_COLL
                 28         columns --NtrySeq   for ordinality,
                 29                 Amt       number                   path 'Amt'
                 30               , Amt_Ccy   varchar2(10)             path 'Amt/@Ccy'
                 31               , CdtDbtInd varchar2(10)             path 'CdtDbtInd'
                 32               , DtTm      timestamp with time zone path 'BookgDt/DtTm'
                 33       ) x2
                 34  ;
                
                93421 rows created.
                
                Elapsed: 00:02:47.21
                Done! 93420 rows loaded in CAMT053_NTRY and 1 row in CAMT053_STMT.

                Edited by: odie_63 on 28 nov. 2012 16:34
                • 5. Re: Import XML with more than 64k occurences of the same node
                  Marco Gralike
                  Its probably good to mention that Odie used an XMLType table create statement in 11.2.0.x which defaults to securefile Binary XML (XMLType). The underlying storage, that is picking the right one for your use-case, makes a HUGE difference regarding performance.

                  Have a look at "*Oracle XML DB : Choosing the Best XMLType Storage Option for Your Use Case* (PDF) Jan 2010" on http://www.oracle.com/technetwork/database/features/xmldb/index.html and bare in mind that your storage, almost certain is XMLType Basicfile/CLOB storage, will be deprecated in 12C and with reason...

                  Edited by: Marco Gralike on Nov 28, 2012 7:18 PM
                  • 6. Re: Import XML with more than 64k occurences of the same node
                    976707
                    Thank you for the suggestion, I have already read the recommendations and chose the Binary XML storage.
                    I had to leave for a meeting and will try Odie's insert statement tomorrow. I have already tried inserting data this way, but master/detail separately, I will try this option too.
                    I suspect that the test server might not be up to the challenge, I don't know what kind of hardware it is based on. If I won't achieve better results with Odie's insert (and I suspect I won't), I'll try running the whole thing on a more powerful server.

                    I pointed the finger at Oracle first because of a very strange behaviour when trying to extract data from xmltype tables, if you have more than 2 ^16^ node occurences using the extract function returns the n ^th^ value AND the (n+2 ^16^ ) ^th^ value. I need to try this out on a different machine to exclude the client etc., but that's a whole different story, this approach is not suitable to retrieve large quantities of data anyway.
                    • 7. Re: Import XML with more than 64k occurences of the same node
                      Marco Gralike
                      Lets hope you will be presently surprised.

                      As said, choosing the right XMLType storage option, will make a huge difference.
                      • 8. Re: Import XML with more than 64k occurences of the same node
                        976707
                        Yes, I have noticed, CLOB based storage worked horribly slow, xmltype binary storage was about 1000x faster, unfortunately it threw the "out of memory" error while inserting using SQL/xmltable in less than 5 minutes :-/
                        I'll try Odie's insert and call the client's DBA about the server's HW detail, hoping to find out that the problem resides there so this problem won't occur in the production enviroinment.
                        • 9. Re: Import XML with more than 64k occurences of the same node
                          odie_63
                          I suspect that the test server might not be up to the challenge
                          Possibly... but just for you to know I tested my sample code on a simple laptop PC with 2GB of RAM, and Oracle 11g XE.

                          The two-step approach (master first, then details) might be actually faster than the multitable INSERT because of the overhead introduced by the analytic function (I used ROW_NUMBER to workaround a bug with the FOR ORDINALITY clause).
                          Will test that tomorrow too.
                          I pointed the finger at Oracle first because of a very strange behaviour when trying to extract data from xmltype tables, if you have more than 2 ^16^ node occurences using the extract function returns the n ^th^ value AND the (n+2 ^16^ ) ^th^ value.
                          As Marco pointed out, storage strategy is the key.
                          As soon as you move to PL/SQL processing, i.e. involving transient XMLType instances, Oracle can't use streaming evaluation anymore and goes to DOM processing.

                          Always check explain plans for your queries. If you see "XPATH EVALUATION" step, you're on the right track.
                          • 10. Re: Import XML with more than 64k occurences of the same node
                            Marco Gralike
                            From the same URL as shown above, try applying the techniques as shown/demonstrated in "Oracle XML DB : Best Practices to Get Optimal Performance out of XML Queries (PDF) Nov 2011" combined with XMLType binary XML and get rid of the XML/SQL operators and functions (extract, extractvalue, etc). Start using xmlexists etc. instead as mentioned in the "new features/_DEPRECATED_" section of the 11.2 manual.
                            • 11. Re: Import XML with more than 64k occurences of the same node
                              976707
                              I have narrowed down the problem - it's in the data!
                              As soon as I eliminate all the fields that contain non-ASCII-7 characters everything works fine. If I select a single column containing special characters, the out-of-memory error is back, even in a select-only situation, no insert needed at all.
                              The problem might be that the data is in UTF8 format and our database uses EE8MSWIN1250 charset, I intended to convert the data after retrieving it with an xmltable select, but I might have to convert it before importing it in the database, I'll try this out to further narrow down the problem.
                              • 12. Re: Import XML with more than 64k occurences of the same node
                                976707
                                I correct myself, a single column does not produce an error, there has to be at least one more column, no matter the content type of the second column, it can be a number.
                                • 13. Re: Import XML with more than 64k occurences of the same node
                                  976707
                                  After extensive testing I have to discard my hypothesis that the problem resides in UTF8 encoded special characters. I have created a clean source XML with 100.000 entries end tried to import it into a table resulting in an error every time, on 3 different servers, all running 11.2.0.3 on 64bit linux.

                                  Could you please take a look at the test case if you spot an obvious error in my insert script and run the test case on your server?

                                  Thank you!

                                  Prepare source data, fictional 100.000 transactions:
                                  --create a table for the XML data
                                  create table test_table of xmltype xmltype store as securefile binary xml
                                  /
                                  
                                  --create a table for source data
                                  create table test_source (amt number
                                                           ,dbtr_bic varchar2(11)
                                                           ,dbtr_iban varchar2(50)
                                                           ,dbtr_adrl1 varchar2(70)
                                                           ,dbtr_adrl2 varchar2(70)
                                                           ,dbtr_ctry varchar2(2)
                                                           ,dbtr_nm varchar2(70)
                                                           ,txid varchar2(50)
                                                           ,EndToEndId varchar2(50)
                                                           )
                                  /
                                  
                                  --fill the source data, fictional accounts, persons etc.
                                  begin
                                  delete from test_source
                                  ;
                                  for i in 1..100000 loop
                                      insert into test_source(amt, dbtr_bic, dbtr_iban
                                                             ,dbtr_adrl1, dbtr_adrl2, dbtr_ctry, dbtr_nm
                                                             ,txid, EndToEndId
                                                             )
                                             values (10, 'TESTBK21XXX', 'XX'||lpad(i, 23, '0')
                                                    ,'TEST RD. '||i, '1203 TESTVILLE', 'SI', 'BIG DEBTOR'
                                                    ,'RFAB34567890123456', 'NOTPROVIDED'
                                                    )
                                             ;
                                  end loop;
                                  commit;
                                  end;
                                  /
                                  
                                  
                                  --create XML
                                  begin
                                  delete from test_table
                                  ;
                                  insert into test_table(object_value)
                                  (select xmlelement("Document"
                                                    ,xmlattributes('urn:iso:std:iso:20022:tech:xsd:camt.053.001.02' as "xmlns"
                                                                  ,'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
                                                                  ,'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02 camt.053.001.02.xsd' as "xsi:schemaLocation"
                                                                  )
                                                    ,xmlelement("BkToCstmrStmt"
                                                               ,xmlelement("GrpHdr"
                                                                          ,xmlelement("MsgId", '2012-11-28T09:46:48/039199')
                                                                          ,xmlelement("CreDtTm", '2012-11-28T09:46:48')
                                                                          )
                                                               ,xmlelement("Stmt"
                                                                          ,xmlelement("Id", 'XX12345678901234567/20121123/EUR')
                                                                          ,xmlelement("LglSeqNb", 226)
                                                                          ,xmlelement("CreDtTm", '2012-11-28T09:46:48')
                                                                          ,xmlelement("Acct"
                                                                                     ,xmlelement("Id"
                                                                                                ,xmlelement("IBAN", 'XX12345678901234567')
                                                                                                )
                                                                                     ,xmlelement("Ownr"
                                                                                                ,xmlelement("Nm", 'TEST CREDITOR')
                                                                                                ,xmlelement("PstlAdr"
                                                                                                           ,xmlelement("AdrLine", 'TEST ST. 1')
                                                                                                           ,xmlelement("AdrLine", '1000 TESTVILLE')
                                                                                                           )
                                                                                                )
                                                                                     )
                                                                          ,xmlelement("Bal"
                                                                                     ,xmlelement("Tp"
                                                                                                ,xmlelement("CdOrPrtry"
                                                                                                           ,xmlelement("Cd", 'OPBD')
                                                                                                           )
                                                                                                )
                                                                                     ,xmlelement("Amt"
                                                                                                ,xmlattributes('EUR' as "Ccy")
                                                                                                ,1000
                                                                                                )
                                                                                     ,xmlelement("CdtDbtInd", 'CRDT')
                                                                                     ,xmlelement("Dt"
                                                                                                ,xmlelement("Dt", '2012-11-15')
                                                                                                )
                                                                                     )
                                                                          ,xmlelement("Bal"
                                                                                     ,xmlelement("Tp"
                                                                                                ,xmlelement("CdOrPrtry"
                                                                                                           ,xmlelement("Cd", 'CLBD')
                                                                                                           )
                                                                                                )
                                                                                     ,xmlelement("Amt"
                                                                                                ,xmlattributes('EUR' as "Ccy")
                                                                                                ,1001000
                                                                                                )
                                                                                     ,xmlelement("CdtDbtInd", 'CRDT')
                                                                                     ,xmlelement("Dt"
                                                                                                ,xmlelement("Dt", '2012-11-28')
                                                                                                )
                                                                                     )
                                                                          ,xmlelement("TxsSummry"
                                                                                     ,xmlelement("TtlCdtNtries"
                                                                                                ,xmlelement("NbOfNtries", 100000)
                                                                                                ,xmlelement("Sum", 1000000)
                                                                                                )
                                                                                     ,xmlelement("TtlDbtNtries"
                                                                                                ,xmlelement("NbOfNtries", 0)
                                                                                                ,xmlelement("Sum", 0)
                                                                                                )
                                                                                     )
                                                                          ,(select xmlagg(xmlelement("Ntry"
                                                                                                    ,xmlelement("Amt"
                                                                                                               ,xmlattributes('EUR' as "Ccy")
                                                                                                               ,t.amt
                                                                                                               )
                                                                                                    ,xmlelement("CdtDbtInd", 'CRDT')
                                                                                                    ,xmlelement("RvslInd", 'false')
                                                                                                    ,xmlelement("Sts", 'BOOK')
                                                                                                    ,xmlelement("BookgDt"
                                                                                                               ,xmlelement("Dt", '2012-11-16')
                                                                                                               )
                                                                                                    ,xmlelement("ValDt"
                                                                                                               ,xmlelement("Dt", '2012-11-16')
                                                                                                               )
                                                                                                    ,xmlelement("BkTxCd"
                                                                                                               ,xmlelement("Prtry"
                                                                                                                          ,xmlelement("Cd", 'SP01')
                                                                                                                          )
                                                                                                               )
                                                                                                    ,xmlelement("NtryDtls"
                                                                                                               ,xmlelement("TxDtls"
                                                                                                                          ,xmlelement("Refs"
                                                                                                                                     ,xmlelement("EndToEndId", t.EndToEndId)
                                                                                                                                     ,xmlelement("TxId", t.txid)
                                                                                                                                     )
                                                                                                                          ,xmlelement("RltdPties"
                                                                                                                                     ,xmlelement("Dbtr"
                                                                                                                                                ,xmlelement("Nm", t.dbtr_nm)
                                                                                                                                                ,xmlelement("PstlAdr"
                                                                                                                                                           ,xmlelement("Ctry", t.dbtr_ctry)
                                                                                                                                                           ,xmlelement("AdrLine", t.dbtr_adrl1)
                                                                                                                                                           ,xmlelement("AdrLine", t.dbtr_adrl2)
                                                                                                                                                           )
                                                                                                                                                )
                                                                                                                                     ,xmlelement("DbtrAcct"
                                                                                                                                                ,xmlelement("Id"
                                                                                                                                                           ,xmlelement("IBAN", t.dbtr_iban)
                                                                                                                                                           )
                                                                                                                                                )
                                                                                                                                     ,xmlelement("Cdtr"
                                                                                                                                                ,xmlelement("Nm", 'TEST CREDITOR')
                                                                                                                                                ,xmlelement("PstlAdr"
                                                                                                                                                           ,xmlelement("Ctry", 'SI')
                                                                                                                                                           ,xmlelement("AdrLine", 'TEST ST. 1')
                                                                                                                                                           ,xmlelement("AdrLine", '1000 TESTVILLE')
                                                                                                                                                           )
                                                                                                                                                )
                                                                                                                                     ,xmlelement("CdtrAcct"
                                                                                                                                                ,xmlelement("Id"
                                                                                                                                                           ,xmlelement("IBAN", 'XX12345678901234567')
                                                                                                                                                           )
                                                                                                                                                )
                                                                                                                                     )
                                                                                                                          ,xmlelement("RltdAgts"
                                                                                                                                     ,xmlelement("DbtrAgt"
                                                                                                                                                ,xmlelement("FinInstnId"
                                                                                                                                                           ,xmlelement("BIC",t.dbtr_bic)
                                                                                                                                                           )
                                                                                                                                                )
                                                                                                                                     ,xmlelement("CdtrAgt"
                                                                                                                                                ,xmlelement("FinInstnId"
                                                                                                                                                           ,xmlelement("BIC",'BAKOSI21XXX')
                                                                                                                                                           )
                                                                                                                                                )
                                                                                                                                     )
                                                                                                                          ,xmlelement("Purp"
                                                                                                                                     ,xmlelement("Cd", 'OTHR')
                                                                                                                                     )
                                                                                                                          ,xmlelement("RmtInf"
                                                                                                                                     ,xmlelement("Ustrd", 'TEST 123')
                                                                                                                                     )
                                                                                                                          ,xmlelement("RltdDts"
                                                                                                                                     ,xmlelement("IntrBkSttlmDt", '2012-11-16')
                                                                                                                                     )
                                                                                                                          )
                                                                                                               )
                                                                                                    )
                                                                                         )
                                                                           from test_source t
                                                                          )
                                                              )
                                                    )
                                         )
                                  from dual
                                  )
                                  ;
                                  commit;
                                  end;
                                  /
                                  Simulate loading the data in the target table:
                                  --create the target table where the data will be loaded
                                  create table target_table (BookgDt_Dt date
                                                            ,ValDt_Dt date
                                                            ,TxDtls_RltdPties_Dbtr_Nm varchar2(70)
                                                            ,TD_RP_Dbtr_PstlAdr_AdrLine_1 varchar2(70)
                                                            ,TD_RP_Dbtr_PstlAdr_AdrLine_2 varchar2(70)
                                                            ,TD_RP_Dbtr_PstlAdr_Ctry varchar2(2)
                                                            ,TD_RP_Cdtr_Nm varchar2(70)
                                                            ,TD_RP_Cdtr_PstlAdr_AdrLine_1 varchar2(70)
                                                            ,TD_RP_Cdtr_PstlAdr_AdrLine_2 varchar2(70)
                                                            ,TD_RP_Cdtr_PstlAdr_Ctry varchar2(2)
                                                            ,TD_RmtInf_Strd_AddtlRmtInf varchar2(200)
                                                            ,AcctSvcrRef varchar2(50)
                                                            ,TxDtls_Refs_EndToEndId varchar2(50)
                                                            ,TD_RmtInf_Strd_CdtrRefInf_Ref varchar2(50)
                                                            ,Amt_Ccy varchar2(3)
                                                            ,Amt number(18,2)
                                                            ,TD_RP_DbtrAcct_Id_IBAN varchar2(50)
                                                            ,TD_RA_DbtrAgt_FinInstnId_BIC varchar2(11)
                                                            ,TD_RP_CdtrAcct_Id_IBAN varchar2(50)
                                                            ,TD_RA_CdtrAgt_FinInstnId_BIC varchar2(11)
                                                            ,TxDtls_Purp_Cd varchar2(4)
                                                            ,TxDtls_Refs_TxId varchar2(50)
                                                            )
                                  /
                                  
                                  --fill the target table
                                  begin
                                  delete from target_table;
                                  insert into target_table(BookgDt_Dt
                                                          ,ValDt_Dt
                                                          ,TxDtls_RltdPties_Dbtr_Nm
                                                          ,TD_RP_Dbtr_PstlAdr_AdrLine_1
                                                          ,TD_RP_Dbtr_PstlAdr_AdrLine_2
                                                          ,TD_RP_Dbtr_PstlAdr_Ctry
                                                          ,TD_RP_Cdtr_Nm
                                                          ,TD_RP_Cdtr_PstlAdr_AdrLine_1
                                                          ,TD_RP_Cdtr_PstlAdr_AdrLine_2
                                                          ,TD_RP_Cdtr_PstlAdr_Ctry
                                                          ,TD_RmtInf_Strd_AddtlRmtInf
                                                          ,AcctSvcrRef
                                                          ,TxDtls_Refs_EndToEndId
                                                          ,TD_RmtInf_Strd_CdtrRefInf_Ref
                                                          ,Amt_Ccy
                                                          ,Amt
                                                          ,TD_RP_DbtrAcct_Id_IBAN
                                                          ,TD_RA_DbtrAgt_FinInstnId_BIC
                                                          ,TD_RP_CdtrAcct_Id_IBAN
                                                          ,TD_RA_CdtrAgt_FinInstnId_BIC
                                                          ,TxDtls_Purp_Cd
                                                          ,TxDtls_Refs_TxId
                                                          )
                                                     (select to_date(x.BookgDt_Dt, 'yyyy-mm-dd')
                                                      ,      to_date(x.ValDt_Dt, 'yyyy-mm-dd')
                                                      ,      x.TxDtls_RltdPties_Dbtr_Nm
                                                      ,      x.TD_RP_Dbtr_PstlAdr_AdrLine_1
                                                      ,      x.TD_RP_Dbtr_PstlAdr_AdrLine_2
                                                      ,      x.TD_RP_Dbtr_PstlAdr_Ctry
                                                      ,      x.TD_RP_Cdtr_Nm
                                                      ,      x.TD_RP_Cdtr_PstlAdr_AdrLine_1
                                                      ,      x.TD_RP_Cdtr_PstlAdr_AdrLine_2
                                                      ,      x.TD_RP_Cdtr_PstlAdr_Ctry
                                                      ,      x.TD_RmtInf_Strd_AddtlRmtInf
                                                      ,      x.AcctSvcrRef
                                                      ,      x.TxDtls_Refs_EndToEndId
                                                      ,      x.TD_RmtInf_Strd_CdtrRefInf_Ref
                                                      ,      x.Amt_Ccy
                                                      ,      decode(upper(x.RvslInd)
                                                                   ,'TRUE', -abs(x.Amt)
                                                                   ,x.Amt
                                                                   )
                                                      ,      nvl(x.TD_RP_DbtrAcct_Id_IBAN, x.TD_RP_DbtrAcct_Id_Othr_Id)
                                                      ,      x.TD_RA_DbtrAgt_FinInstnId_BIC
                                                      ,      x.TD_RP_CdtrAcct_Id_IBAN
                                                      ,      x.TD_RA_CdtrAgt_FinInstnId_BIC
                                                      ,      x.TxDtls_Purp_Cd
                                                      ,      x.TxDtls_Refs_TxId
                                                      from xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
                                                                   ,'/Document/BkToCstmrStmt/Stmt[1]/Ntry'
                                                                   passing (select t.object_value
                                                                            from test_table t
                                                                           )
                                                                   columns Amt path 'Amt'
                                                                   ,       RvslInd path 'RvslInd'
                                                                   ,       CdtDbtInd path 'CdtDbtInd'
                                                                   ,       BookgDt_Dt path 'BookgDt/Dt'
                                                                   ,       ValDt_Dt path 'ValDt/Dt'
                                                                   ,       AcctSvcrRef path 'AcctSvcrRef'
                                                                   ,       TxDtls_Refs_TxId path 'NtryDtls/TxDtls/Refs/TxId'
                                                                   ,       TxDtls_Refs_EndToEndId path 'NtryDtls/TxDtls/Refs/EndToEndId'
                                                                   ,       TxDtls_RltdPties_Dbtr_Nm path 'NtryDtls/TxDtls/RltdPties/Dbtr/Nm'
                                                                   ,       TD_RP_Dbtr_PstlAdr_AdrLine_1 path 'NtryDtls/TxDtls/RltdPties/Dbtr/PstlAdr/AdrLine[1]'
                                                                   ,       TD_RP_Dbtr_PstlAdr_AdrLine_2 path 'NtryDtls/TxDtls/RltdPties/Dbtr/PstlAdr/AdrLine[2]'
                                                                   ,       TD_RP_Dbtr_PstlAdr_Ctry path 'NtryDtls/TxDtls/RltdPties/Dbtr/PstlAdr/Ctry'
                                                                   ,       TD_RP_DbtrAcct_Id_IBAN path 'NtryDtls/TxDtls/RltdPties/DbtrAcct/Id/IBAN'
                                                                   ,       TD_RP_DbtrAcct_Id_Othr_Id path 'NtryDtls/TxDtls/RltdPties/DbtrAcct/Id/Othr/Id'
                                                                   ,       TD_RA_DbtrAgt_FinInstnId_BIC path 'NtryDtls/TxDtls/RltdAgts/DbtrAgt/FinInstnId/BIC'
                                                                   ,       TD_RP_Cdtr_Nm path 'NtryDtls/TxDtls/RltdPties/Cdtr/Nm'
                                                                   ,       TD_RP_Cdtr_PstlAdr_AdrLine_1 path 'NtryDtls/TxDtls/RltdPties/Cdtr/PstlAdr/AdrLine[1]'
                                                                   ,       TD_RP_Cdtr_PstlAdr_AdrLine_2 path 'NtryDtls/TxDtls/RltdPties/Cdtr/PstlAdr/AdrLine[2]'
                                                                   ,       TD_RP_Cdtr_PstlAdr_Ctry path 'NtryDtls/TxDtls/RltdPties/Cdtr/PstlAdr/Ctry'
                                                                   ,       TD_RP_CdtrAcct_Id_IBAN path 'NtryDtls/TxDtls/RltdPties/CdtrAcct/Id/IBAN'
                                                                   ,       TD_RA_CdtrAgt_FinInstnId_BIC path 'NtryDtls/TxDtls/RltdAgts/CdtrAgt/FinInstnId/BIC'
                                                                   ,       TD_RmtInf_Strd_CdtrRefInf_Ref path 'NtryDtls/TxDtls/RmtInf/Strd/CdtrRefInf/Ref'
                                                                   ,       TD_RmtInf_Strd_AddtlRmtInf path 'NtryDtls/TxDtls/RmtInf/Strd/AddtlRmtInf'
                                                                   ,       TxDtls_RmtInf_Ustrd path 'NtryDtls/TxDtls/RmtInf/Ustrd'
                                                                   ,       Amt_Ccy path 'Amt/@Ccy'
                                                                   ,       TxDtls_Purp_Cd path 'NtryDtls/TxDtls/Purp/Cd'
                                                                   ) x
                                                                   --where rownum <= 65350 --experimenting with the number of rows, errors given around this number of rows
                                                     )
                                                     ;
                                  commit;
                                  end;
                                  /
                                  • 14. Re: Import XML with more than 64k occurences of the same node
                                    mdrake-Oracle
                                    This seems to work for me
                                    C:\xdb\customers\ISO20022>sqlplus /nolog @testcase
                                    
                                    SQL*Plus: Release 11.2.0.3.0 Production on Fri Nov 30 07:59:46 2012
                                    
                                    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
                                    
                                    SQL> spool testcase.log
                                    SQL> --
                                    SQL> connect sys/oracle as sysdba
                                    Connected.
                                    SQL> --
                                    SQL> set define on
                                    SQL> set timing on
                                    SQL> --
                                    SQL> def USERNAME = ISO20022
                                    SQL> --
                                    SQL> def PASSWORD = &USERNAME
                                    SQL> --
                                    SQL> def USER_TABLESPACE = USERS
                                    SQL> --
                                    SQL> def TEMP_TABLESPACE = TEMP
                                    SQL> --
                                    SQL> drop user &USERNAME cascade
                                      2  /
                                    old   1: drop user &USERNAME cascade
                                    new   1: drop user ISO20022 cascade
                                    drop user ISO20022 cascade
                                              *
                                    ERROR at line 1:
                                    ORA-01918: user 'ISO20022' does not exist
                                    
                                    
                                    Elapsed: 00:00:00.01
                                    SQL> grant create any directory, drop any directory, connect, resource, alter session, create view, unlimited tablespace to &USERNAME identified by &PASSWORD
                                      2  /
                                    old   1: grant create any directory, drop any directory, connect, resource, alter session, create view, unlimited tablespace to &USERNAME identified by &PASSWOR
                                    D
                                    new   1: grant create any directory, drop any directory, connect, resource, alter session, create view, unlimited tablespace to ISO20022 identified by ISO20022
                                    
                                    Grant succeeded.
                                    
                                    Elapsed: 00:00:00.13
                                    SQL> alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
                                      2  /
                                    old   1: alter user &USERNAME default tablespace &USER_TABLESPACE temporary tablespace &TEMP_TABLESPACE
                                    new   1: alter user ISO20022 default tablespace USERS temporary tablespace TEMP
                                    
                                    User altered.
                                    
                                    Elapsed: 00:00:00.00
                                    SQL> set long 100000 pages 0 lines 256 trimspool on timing on
                                    SQL> --
                                    SQL> connect &USERNAME/&PASSWORD
                                    Connected.
                                    SQL> --
                                    SQL> create table test_table of xmltype xmltype store as securefile binary xml
                                      2  /
                                    
                                    Table created.
                                    
                                    Elapsed: 00:00:00.24
                                    SQL>
                                    SQL> --create a table for source data
                                    SQL> create table test_source (amt number
                                      2                           ,dbtr_bic varchar2(11)
                                      3                           ,dbtr_iban varchar2(50)
                                      4                           ,dbtr_adrl1 varchar2(70)
                                      5                           ,dbtr_adrl2 varchar2(70)
                                      6                           ,dbtr_ctry varchar2(2)
                                      7                           ,dbtr_nm varchar2(70)
                                      8                           ,txid varchar2(50)
                                      9                           ,EndToEndId varchar2(50)
                                     10                           )
                                     11  /
                                    
                                    Table created.
                                    
                                    Elapsed: 00:00:00.00
                                    SQL>
                                    SQL> --fill the source data, fictional accounts, persons etc.
                                    SQL> begin
                                      2  delete from test_source
                                      3  ;
                                      4  for i in 1..100000 loop
                                      5      insert into test_source(amt, dbtr_bic, dbtr_iban
                                      6                             ,dbtr_adrl1, dbtr_adrl2, dbtr_ctry, dbtr_nm
                                      7                             ,txid, EndToEndId
                                      8                             )
                                      9             values (10, 'TESTBK21XXX', 'XX'||lpad(i, 23, '0')
                                     10                    ,'TEST RD. '||i, '1203 TESTVILLE', 'SI', 'BIG DEBTOR'
                                     11                    ,'RFAB34567890123456', 'NOTPROVIDED'
                                     12                    )
                                     13             ;
                                     14  end loop;
                                     15  commit;
                                     16  end;
                                     17  /
                                    
                                    PL/SQL procedure successfully completed.
                                    
                                    Elapsed: 00:00:03.81
                                    SQL>
                                    SQL>
                                    SQL> --create XML
                                    SQL> begin
                                      2  delete from test_table
                                      3  ;
                                      4  insert into test_table(object_value)
                                      5  (select xmlelement("Document"
                                      6                    ,xmlattributes('urn:iso:std:iso:20022:tech:xsd:camt.053.001.02' as "xmlns"
                                      7                                  ,'http://www.w3.org/2001/XMLSchema-instance' as "xmlns:xsi"
                                      8                                  ,'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02 camt.053.001.02.xsd' as "xsi:schemaLocation"
                                      9                                  )
                                     10                    ,xmlelement("BkToCstmrStmt"
                                     11                               ,xmlelement("GrpHdr"
                                     12                                          ,xmlelement("MsgId", '2012-11-28T09:46:48/039199')
                                     13                                          ,xmlelement("CreDtTm", '2012-11-28T09:46:48')
                                     14                                          )
                                     15                               ,xmlelement("Stmt"
                                     16                                          ,xmlelement("Id", 'XX12345678901234567/20121123/EUR')
                                     17                                          ,xmlelement("LglSeqNb", 226)
                                     18                                          ,xmlelement("CreDtTm", '2012-11-28T09:46:48')
                                     19                                          ,xmlelement("Acct"
                                     20                                                     ,xmlelement("Id"
                                     21                                                                ,xmlelement("IBAN", 'XX12345678901234567')
                                     22                                                                )
                                     23                                                     ,xmlelement("Ownr"
                                     24                                                                ,xmlelement("Nm", 'TEST CREDITOR')
                                     25                                                                ,xmlelement("PstlAdr"
                                     26                                                                           ,xmlelement("AdrLine", 'TEST ST. 1')
                                     27                                                                           ,xmlelement("AdrLine", '1000 TESTVILLE')
                                     28                                                                           )
                                     29                                                                )
                                     30                                                     )
                                     31                                          ,xmlelement("Bal"
                                     32                                                     ,xmlelement("Tp"
                                     33                                                                ,xmlelement("CdOrPrtry"
                                     34                                                                           ,xmlelement("Cd", 'OPBD')
                                     35                                                                           )
                                     36                                                                )
                                     37                                                     ,xmlelement("Amt"
                                     38                                                                ,xmlattributes('EUR' as "Ccy")
                                     39                                                                ,1000
                                     40                                                                )
                                     41                                                     ,xmlelement("CdtDbtInd", 'CRDT')
                                     42                                                     ,xmlelement("Dt"
                                     43                                                                ,xmlelement("Dt", '2012-11-15')
                                     44                                                                )
                                     45                                                     )
                                     46                                          ,xmlelement("Bal"
                                     47                                                     ,xmlelement("Tp"
                                     48                                                                ,xmlelement("CdOrPrtry"
                                     49                                                                           ,xmlelement("Cd", 'CLBD')
                                     50                                                                           )
                                     51                                                                )
                                     52                                                     ,xmlelement("Amt"
                                     53                                                                ,xmlattributes('EUR' as "Ccy")
                                     54                                                                ,1001000
                                     55                                                                )
                                     56                                                     ,xmlelement("CdtDbtInd", 'CRDT')
                                     57                                                     ,xmlelement("Dt"
                                     58                                                                ,xmlelement("Dt", '2012-11-28')
                                     59                                                                )
                                     60                                                     )
                                     61                                          ,xmlelement("TxsSummry"
                                     62                                                     ,xmlelement("TtlCdtNtries"
                                     63                                                                ,xmlelement("NbOfNtries", 100000)
                                     64                                                                ,xmlelement("Sum", 1000000)
                                     65                                                                )
                                     66                                                     ,xmlelement("TtlDbtNtries"
                                     67                                                                ,xmlelement("NbOfNtries", 0)
                                     68                                                                ,xmlelement("Sum", 0)
                                     69                                                                )
                                     70                                                     )
                                     71                                          ,(select xmlagg(xmlelement("Ntry"
                                     72                                                                    ,xmlelement("Amt"
                                     73                                                                               ,xmlattributes('EUR' as "Ccy")
                                     74                                                                               ,t.amt
                                     75                                                                               )
                                     76                                                                    ,xmlelement("CdtDbtInd", 'CRDT')
                                     77                                                                    ,xmlelement("RvslInd", 'false')
                                     78                                                                    ,xmlelement("Sts", 'BOOK')
                                     79                                                                    ,xmlelement("BookgDt"
                                     80                                                                               ,xmlelement("Dt", '2012-11-16')
                                     81                                                                               )
                                     82                                                                    ,xmlelement("ValDt"
                                     83                                                                               ,xmlelement("Dt", '2012-11-16')
                                     84                                                                               )
                                     85                                                                    ,xmlelement("BkTxCd"
                                     86                                                                               ,xmlelement("Prtry"
                                     87                                                                                          ,xmlelement("Cd", 'SP01')
                                     88                                                                                          )
                                     89                                                                               )
                                     90                                                                    ,xmlelement("NtryDtls"
                                     91                                                                               ,xmlelement("TxDtls"
                                     92                                                                                          ,xmlelement("Refs"
                                     93                                                                                                     ,xmlelement("EndToEndId", t.EndToEndId)
                                     94                                                                                                     ,xmlelement("TxId", t.txid)
                                     95                                                                                                     )
                                     96                                                                                          ,xmlelement("RltdPties"
                                     97                                                                                                     ,xmlelement("Dbtr"
                                     98                                                                                                            ,xmlelement("Nm", t.dbtr_nm)
                                     99                                                                                                            ,xmlelement("PstlAdr"
                                    100                                                                                                                   ,xmlelement("Ctry", t.dbtr_ctry)
                                    101                                                                                                                   ,xmlelement("AdrLine", t.dbtr_adrl1)
                                    102                                                                                                                   ,xmlelement("AdrLine", t.dbtr_adrl2)
                                    103                                                                                                                   )
                                    104                                                                                                            )
                                    105                                                                                                     ,xmlelement("DbtrAcct"
                                    106                                                                                                            ,xmlelement("Id"
                                    107                                                                                                                   ,xmlelement("IBAN", t.dbtr_iban)
                                    108                                                                                                                   )
                                    109                                                                                                            )
                                    110                                                                                                     ,xmlelement("Cdtr"
                                    111                                                                                                            ,xmlelement("Nm", 'TEST CREDITOR')
                                    112                                                                                                            ,xmlelement("PstlAdr"
                                    113                                                                                                                   ,xmlelement("Ctry", 'SI')
                                    114                                                                                                                   ,xmlelement("AdrLine", 'TEST ST. 1')
                                    115                                                                                                                   ,xmlelement("AdrLine", '1000 TESTVILLE')
                                    116                                                                                                                   )
                                    117                                                                                                            )
                                    118                                                                                                     ,xmlelement("CdtrAcct"
                                    119                                                                                                            ,xmlelement("Id"
                                    120                                                                                                                   ,xmlelement("IBAN", 'XX12345678901234567')
                                    
                                    121                                                                                                                   )
                                    122                                                                                                            )
                                    123                                                                                                     )
                                    124                                                                                          ,xmlelement("RltdAgts"
                                    125                                                                                                     ,xmlelement("DbtrAgt"
                                    126                                                                                                            ,xmlelement("FinInstnId"
                                    127                                                                                                                   ,xmlelement("BIC",t.dbtr_bic)
                                    128                                                                                                                   )
                                    129                                                                                                            )
                                    130                                                                                                     ,xmlelement("CdtrAgt"
                                    131                                                                                                            ,xmlelement("FinInstnId"
                                    132                                                                                                                   ,xmlelement("BIC",'BAKOSI21XXX')
                                    133                                                                                                                   )
                                    134                                                                                                            )
                                    135                                                                                                     )
                                    136                                                                                          ,xmlelement("Purp"
                                    137                                                                                                     ,xmlelement("Cd", 'OTHR')
                                    138                                                                                                     )
                                    139                                                                                          ,xmlelement("RmtInf"
                                    140                                                                                                     ,xmlelement("Ustrd", 'TEST 123')
                                    141                                                                                                     )
                                    142                                                                                          ,xmlelement("RltdDts"
                                    143                                                                                                     ,xmlelement("IntrBkSttlmDt", '2012-11-16')
                                    144                                                                                                     )
                                    145                                                                                          )
                                    146                                                                               )
                                    147                                                                    )
                                    148                                                         )
                                    149                                           from test_source t
                                    150                                          )
                                    151                              )
                                    152                    )
                                    153         )
                                    154  from dual
                                    155  )
                                    156  ;
                                    157  commit;
                                    158  end;
                                    159  /
                                    
                                    PL/SQL procedure successfully completed.
                                    
                                    Elapsed: 00:00:35.58
                                    SQL>
                                    SQL>
                                    SQL>
                                    SQL> Simulate loading the data in the target table:
                                    SP2-0734: unknown command beginning "Simulate l..." - rest of line ignored.
                                    SQL>
                                    SQL> --create the target table where the data will be loaded
                                    SQL> create table target_table (BookgDt_Dt date
                                      2                            ,ValDt_Dt date
                                      3                            ,TxDtls_RltdPties_Dbtr_Nm varchar2(70)
                                      4                            ,TD_RP_Dbtr_PstlAdr_AdrLine_1 varchar2(70)
                                      5                            ,TD_RP_Dbtr_PstlAdr_AdrLine_2 varchar2(70)
                                      6                            ,TD_RP_Dbtr_PstlAdr_Ctry varchar2(2)
                                      7                            ,TD_RP_Cdtr_Nm varchar2(70)
                                      8                            ,TD_RP_Cdtr_PstlAdr_AdrLine_1 varchar2(70)
                                      9                            ,TD_RP_Cdtr_PstlAdr_AdrLine_2 varchar2(70)
                                     10                            ,TD_RP_Cdtr_PstlAdr_Ctry varchar2(2)
                                     11                            ,TD_RmtInf_Strd_AddtlRmtInf varchar2(200)
                                     12                            ,AcctSvcrRef varchar2(50)
                                     13                            ,TxDtls_Refs_EndToEndId varchar2(50)
                                     14                            ,TD_RmtInf_Strd_CdtrRefInf_Ref varchar2(50)
                                     15                            ,Amt_Ccy varchar2(3)
                                     16                            ,Amt number(18,2)
                                     17                            ,TD_RP_DbtrAcct_Id_IBAN varchar2(50)
                                     18                            ,TD_RA_DbtrAgt_FinInstnId_BIC varchar2(11)
                                     19                            ,TD_RP_CdtrAcct_Id_IBAN varchar2(50)
                                     20                            ,TD_RA_CdtrAgt_FinInstnId_BIC varchar2(11)
                                     21                            ,TxDtls_Purp_Cd varchar2(4)
                                     22                            ,TxDtls_Refs_TxId varchar2(50)
                                     23                            )
                                     24  /
                                    
                                    Table created.
                                    
                                    Elapsed: 00:00:00.01
                                    SQL>
                                    SQL> --fill the target table
                                    SQL> begin
                                      2  delete from target_table;
                                      3  insert into target_table(BookgDt_Dt
                                      4                          ,ValDt_Dt
                                      5                          ,TxDtls_RltdPties_Dbtr_Nm
                                      6                          ,TD_RP_Dbtr_PstlAdr_AdrLine_1
                                      7                          ,TD_RP_Dbtr_PstlAdr_AdrLine_2
                                      8                          ,TD_RP_Dbtr_PstlAdr_Ctry
                                      9                          ,TD_RP_Cdtr_Nm
                                     10                          ,TD_RP_Cdtr_PstlAdr_AdrLine_1
                                     11                          ,TD_RP_Cdtr_PstlAdr_AdrLine_2
                                     12                          ,TD_RP_Cdtr_PstlAdr_Ctry
                                     13                          ,TD_RmtInf_Strd_AddtlRmtInf
                                     14                          ,AcctSvcrRef
                                     15                          ,TxDtls_Refs_EndToEndId
                                     16                          ,TD_RmtInf_Strd_CdtrRefInf_Ref
                                     17                          ,Amt_Ccy
                                     18                          ,Amt
                                     19                          ,TD_RP_DbtrAcct_Id_IBAN
                                     20                          ,TD_RA_DbtrAgt_FinInstnId_BIC
                                     21                          ,TD_RP_CdtrAcct_Id_IBAN
                                     22                          ,TD_RA_CdtrAgt_FinInstnId_BIC
                                     23                          ,TxDtls_Purp_Cd
                                     24                          ,TxDtls_Refs_TxId
                                     25                          )
                                     26                     (select to_date(x.BookgDt_Dt, 'yyyy-mm-dd')
                                     27                      ,      to_date(x.ValDt_Dt, 'yyyy-mm-dd')
                                     28                      ,      x.TxDtls_RltdPties_Dbtr_Nm
                                     29                      ,      x.TD_RP_Dbtr_PstlAdr_AdrLine_1
                                     30                      ,      x.TD_RP_Dbtr_PstlAdr_AdrLine_2
                                     31                      ,      x.TD_RP_Dbtr_PstlAdr_Ctry
                                     32                      ,      x.TD_RP_Cdtr_Nm
                                     33                      ,      x.TD_RP_Cdtr_PstlAdr_AdrLine_1
                                     34                      ,      x.TD_RP_Cdtr_PstlAdr_AdrLine_2
                                     35                      ,      x.TD_RP_Cdtr_PstlAdr_Ctry
                                     36                      ,      x.TD_RmtInf_Strd_AddtlRmtInf
                                     37                      ,      x.AcctSvcrRef
                                     38                      ,      x.TxDtls_Refs_EndToEndId
                                     39                      ,      x.TD_RmtInf_Strd_CdtrRefInf_Ref
                                     40                      ,      x.Amt_Ccy
                                     41                      ,      decode(upper(x.RvslInd)
                                     42                                   ,'TRUE', -abs(x.Amt)
                                     43                                   ,x.Amt
                                     44                                   )
                                     45                      ,      nvl(x.TD_RP_DbtrAcct_Id_IBAN, x.TD_RP_DbtrAcct_Id_Othr_Id)
                                     46                      ,      x.TD_RA_DbtrAgt_FinInstnId_BIC
                                     47                      ,      x.TD_RP_CdtrAcct_Id_IBAN
                                     48                      ,      x.TD_RA_CdtrAgt_FinInstnId_BIC
                                     49                      ,      x.TxDtls_Purp_Cd
                                     50                      ,      x.TxDtls_Refs_TxId
                                     51                      from xmltable(xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02')
                                     52                                   ,'/Document/BkToCstmrStmt/Stmt[1]/Ntry'
                                     53                                   passing (select t.object_value
                                     54                                            from test_table t
                                     55                                           )
                                     56                                   columns Amt path 'Amt'
                                     57                                   ,       RvslInd path 'RvslInd'
                                     58                                   ,       CdtDbtInd path 'CdtDbtInd'
                                     59                                   ,       BookgDt_Dt path 'BookgDt/Dt'
                                     60                                   ,       ValDt_Dt path 'ValDt/Dt'
                                     61                                   ,       AcctSvcrRef path 'AcctSvcrRef'
                                     62                                   ,       TxDtls_Refs_TxId path 'NtryDtls/TxDtls/Refs/TxId'
                                     63                                   ,       TxDtls_Refs_EndToEndId path 'NtryDtls/TxDtls/Refs/EndToEndId'
                                     64                                   ,       TxDtls_RltdPties_Dbtr_Nm path 'NtryDtls/TxDtls/RltdPties/Dbtr/Nm'
                                     65                                   ,       TD_RP_Dbtr_PstlAdr_AdrLine_1 path 'NtryDtls/TxDtls/RltdPties/Dbtr/PstlAdr/AdrLine[1]'
                                     66                                   ,       TD_RP_Dbtr_PstlAdr_AdrLine_2 path 'NtryDtls/TxDtls/RltdPties/Dbtr/PstlAdr/AdrLine[2]'
                                     67                                   ,       TD_RP_Dbtr_PstlAdr_Ctry path 'NtryDtls/TxDtls/RltdPties/Dbtr/PstlAdr/Ctry'
                                     68                                   ,       TD_RP_DbtrAcct_Id_IBAN path 'NtryDtls/TxDtls/RltdPties/DbtrAcct/Id/IBAN'
                                     69                                   ,       TD_RP_DbtrAcct_Id_Othr_Id path 'NtryDtls/TxDtls/RltdPties/DbtrAcct/Id/Othr/Id'
                                     70                                   ,       TD_RA_DbtrAgt_FinInstnId_BIC path 'NtryDtls/TxDtls/RltdAgts/DbtrAgt/FinInstnId/BIC'
                                     71                                   ,       TD_RP_Cdtr_Nm path 'NtryDtls/TxDtls/RltdPties/Cdtr/Nm'
                                     72                                   ,       TD_RP_Cdtr_PstlAdr_AdrLine_1 path 'NtryDtls/TxDtls/RltdPties/Cdtr/PstlAdr/AdrLine[1]'
                                     73                                   ,       TD_RP_Cdtr_PstlAdr_AdrLine_2 path 'NtryDtls/TxDtls/RltdPties/Cdtr/PstlAdr/AdrLine[2]'
                                     74                                   ,       TD_RP_Cdtr_PstlAdr_Ctry path 'NtryDtls/TxDtls/RltdPties/Cdtr/PstlAdr/Ctry'
                                     75                                   ,       TD_RP_CdtrAcct_Id_IBAN path 'NtryDtls/TxDtls/RltdPties/CdtrAcct/Id/IBAN'
                                     76                                   ,       TD_RA_CdtrAgt_FinInstnId_BIC path 'NtryDtls/TxDtls/RltdAgts/CdtrAgt/FinInstnId/BIC'
                                     77                                   ,       TD_RmtInf_Strd_CdtrRefInf_Ref path 'NtryDtls/TxDtls/RmtInf/Strd/CdtrRefInf/Ref'
                                     78                                   ,       TD_RmtInf_Strd_AddtlRmtInf path 'NtryDtls/TxDtls/RmtInf/Strd/AddtlRmtInf'
                                     79                                   ,       TxDtls_RmtInf_Ustrd path 'NtryDtls/TxDtls/RmtInf/Ustrd'
                                     80                                   ,       Amt_Ccy path 'Amt/@Ccy'
                                     81                                   ,       TxDtls_Purp_Cd path 'NtryDtls/TxDtls/Purp/Cd'
                                     82                                   ) x
                                     83                                   --where rownum <= 65350 --experimenting with the number of rows, errors given around this number of rows
                                     84                     )
                                     85                     ;
                                     86  commit;
                                     87  end;
                                     88  /
                                    
                                    PL/SQL procedure successfully completed.
                                    
                                    Elapsed: 00:00:17.49
                                    1 2 Previous Next