Hi all,
I have a problem and I was hoping someone could advise on appropriate action
I have this very complex XML schema and an XML document that i have to import into the database
and then parse the document and insert the data in different tables. (XML document size >130 MB )
The problem is the process is taking quite long ( 1,5 h ) and I am wondering if I have done all I can to make it as fast as possible.
My database version is 11.2.0.4.
What I have tried so far:
First I registered my schema:
BEGIN
DBMS_XMLSCHEMA.registerSchema(
SCHEMAURL => 'camt.053.001.02.xsd',
SCHEMADOC => bfilename('DB_FOLDER','camt.053.001.02.xsd'),
CSID => nls_charset_id('AL32UTF8'),
LOCAL => TRUE,
GENTYPES => FALSE,
GENBEAN => FALSE,
GENTABLES => FALSE,
FORCE => FALSE,
OPTIONS => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
OWNER=> USER);
END;
If I try to put GENTYPES =>TRUE and GENTABLES=>TRUE then I get the ORA-01792 error.
Then created a table with XMLtype column:
CREATE TABLE ROBI_XML_DATA
(id number, BAG xmltype)
xmltype COLUMN BAG STORE AS BINARY XML
XMLSCHEMA "camt.053.001.02.xsd" ELEMENT "Document";
I then inserted the XML document:
INSERT INTO ROBI_XML_DATA (id, BAG)
VALUES ( 1,
xmltype(
bfilename('DATABASE_DIR', 'example.xml')
, nls_charset_id('AL32UTF8')
)
);
With the query below I then go and parse the XML to get the data:
SELECT /*+ all_rows */
testt ,
test2,
test3,
test4,
test5,
test6,
test7,
test8,
test9
FROM ROBI_XML_DATA x,
XMLTABLE (
XMLNAMESPACES (
DEFAULT 'urn:iso:std:iso:20022:tech:xsd:camt.053.001.02'),
'/Document/BkToCstmrStmt/Stmt/Ntry'
PASSING x.bag
COLUMNS testt VARCHAR2 (100) PATH 'Amt/@Ccy',
test2 VARCHAR2 (100) PATH 'CdtDbtInd',
test3 VARCHAR2 (100) PATH 'BookgDt/Dt',
test4 VARCHAR2 (100) PATH 'ValDt/Dt',
test5 VARCHAR2 (100) PATH 'AcctSvcrRef',
test6 varchar2(100) PATH 'NtryDtls/TxDtls/Refs/InstrId',
test7 varchar2(100) PATH 'NtryDtls/TxDtls/Refs/EndToEndId',
test8 varchar2(100) PATH 'NtryDtls/TxDtls/Refs/TxId',
test9 varchar2(100) PATH 'NtryDtls/TxDtls/RltdPties/DbtrAcct/Id/IBAN'
) t
WHERE x.id = 1;
I have also tried to create some XMLindexes to speed things up but it did not make any difference:
CREATE INDEX XIDX_BAG_01
ON ROBI_XML_DATA(BAG)
INDEXTYPE IS XDB.XMLINDEX
PARAMETERS ('PATHS (INCLUDE (/Document/BkToCstmrStmt/Stmt/Ntry//*))'
)
;
I created an index with specific paths in XML that I query, but then the indexing took about an hour and a half so there was no use in that:
CREATE INDEX XIDX_BAG_01
ON ROBI_XML_DATA(BAG)
INDEXTYPE IS XDB.XMLINDEX
PARAMETERS ('GROUP BAG_VIEW_GROUP
XMLTABLE CONTENT_BAG_SXI_TABLE
XMLNAMESPACES(default ''urn:iso:std:iso:20022:tech:xsd:camt.053.001.02'')
, ''/Document/BkToCstmrStmt/Stmt/Ntry''
COLUMNS testt VARCHAR2(100) PATH ''Amt'',
test2 VARCHAR2(100) PATH ''CdtDbtInd'',
test3 VARCHAR2(100) PATH ''BookgDt/Dt'',
test4 VARCHAR2(100) PATH ''ValDt/Dt'',
test5 VARCHAR2(100) PATH ''AcctSvcrRef''
'
)
;
The partial example of the XML document is as follows (there are multiple Ntry tags in my case - >120.000 ):
<?xml version="1.0" encoding="UTF-8"?>
<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.053.001.02">
<BkToCstmrStmt>
<GrpHdr>
<MsgId>TRK18327680</MsgId>
<CreDtTm>2019-04-18T07:14:38</CreDtTm>
</GrpHdr>
<Stmt>
<Id>SI56XXXXXXXXX-EUR-20190417</Id>
<LglSeqNb>74</LglSeqNb>
<CreDtTm>2019-04-17T00:00:00</CreDtTm>
<Acct>
<Id>
<IBAN>SI56XXXXXXXXX</IBAN>
</Id>
<Ccy>EUR</Ccy>
<Ownr>
<Nm>TEST</Nm>
<PstlAdr>
<Ctry>SI</Ctry>
<AdrLine>TEST 3</AdrLine>
<AdrLine>2319 Test</AdrLine>
</PstlAdr>
</Ownr>
</Acct>
<Bal>
<Tp>
<CdOrPrtry>
<Cd>OPBD</Cd>
</CdOrPrtry>
</Tp>
<Amt Ccy="EUR">531407.79</Amt>
<CdtDbtInd>CRDT</CdtDbtInd>
<Dt>
<Dt>2019-04-17</Dt>
</Dt>
</Bal>
<Bal>
<Tp>
<CdOrPrtry>
<Cd>CLBD</Cd>
</CdOrPrtry>
</Tp>
<Amt Ccy="EUR">535886.8</Amt>
<CdtDbtInd>CRDT</CdtDbtInd>
<Dt>
<Dt>2019-04-17</Dt>
</Dt>
</Bal>
<TxsSummry>
<TtlCdtNtries>
<NbOfNtries>134</NbOfNtries>
<Sum>7402.64</Sum>
</TtlCdtNtries>
<TtlDbtNtries>
<NbOfNtries>9</NbOfNtries>
<Sum>2923.63</Sum>
</TtlDbtNtries>
</TxsSummry>
<Ntry>
<Amt Ccy="EUR">6.54</Amt>
<CdtDbtInd>DBIT</CdtDbtInd>
<Sts>BOOK</Sts>
<BookgDt>
<Dt>2019-04-17</Dt>
</BookgDt>
<ValDt>
<Dt>2019-04-17</Dt>
</ValDt>
<AcctSvcrRef>A89110000560951</AcctSvcrRef>
<BkTxCd>
<Prtry>
<Cd>NOTPROVIDED</Cd>
</Prtry>
</BkTxCd>
<NtryDtls>
<TxDtls>
<Refs>
<InstrId>GH54678303768543</InstrId>
<EndToEndId>HG6845554870457847568</EndToEndId>
<TxId>98756875496754fG</TxId>
</Refs>
<RltdPties>
<Dbtr>
<Nm>TEST</Nm>
<PstlAdr>
<Ctry>SI</Ctry>
<AdrLine>TEST 3</AdrLine>
<AdrLine>2319 Test</AdrLine>
</PstlAdr>
</Dbtr>
<DbtrAcct>
<Id>
<IBAN>GH89508345795706</IBAN>
</Id>
</DbtrAcct>
<Cdtr>
<Nm>TEST.</Nm>
<PstlAdr>
<Ctry>SI</Ctry>
<AdrLine>TEST 3</AdrLine>
<AdrLine>2319 Test</AdrLine>
</PstlAdr>
</Cdtr>
<CdtrAcct>
<Id>
<IBAN>GH89508345795706</IBAN>
</Id>
</CdtrAcct>
</RltdPties>
<RltdAgts>
<DbtrAgt>
<FinInstnId>
<BIC>XXXTEST</BIC>
</FinInstnId>
</DbtrAgt>
<CdtrAgt>
<FinInstnId>
<BIC>XXXTEST</BIC>
</FinInstnId>
</CdtrAgt>
</RltdAgts>
<Purp>
<Cd>OTHR</Cd>
</Purp>
<RmtInf>
<Strd>
<CdtrRefInf>
<Ref>GH4983-5867</Ref>
</CdtrRefInf>
<AddtlRmtInf>Naer tuieh</AddtlRmtInf>
</Strd>
</RmtInf>
</TxDtls>
</NtryDtls>
</Ntry>
</Stmt>
</BkToCstmrStmt>
</Document>