This discussion is archived
1 2 Previous Next 16 Replies Latest reply: Dec 13, 2012 1:23 AM by 976707 RSS

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

976707 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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
    MarcoGralike Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points