3 Replies Latest reply: Oct 18, 2013 3:24 AM by odie_63 RSS

    Loading XML data In Oracle Database table

    Debanjan Banerjee

      Hi All ,

       

      I need help on below requirement.

       

      1) I have a XML file which I need to load into database.I dont want to load all data in oracle tables.

      Fields marked in bold are the columns that I want to load.

       

      Please help me write the xmltable code.

       

      Below is the xml file:

       

       

       

       

       

      <?xml version="1.0" encoding="UTF-8"?>

      -<Document xmlns="urn:iso:std:iso:20022:tech:xsd:camt.054.001.02">

           -<BkToCstmrDbtCdtNtfctn>

                -<GrpHdr>

                    <MsgId>2013-07-24-12.00.52.486267</MsgId>

                     <CreDtTm>2013-07-24T10:00:52Z</CreDtTm>

                    -<MsgPgntn>

                           <PgNb>1</PgNb>

                          <LastPgInd>true</LastPgInd>

                     </MsgPgntn>

                </GrpHdr>

                -<Ntfctn>

                          <Id>2013-07-24-12.00.52.473349</Id>

                          <CreDtTm>2013-07-24T10:00:52Z</CreDtTm>

                          -<Acct>

                                 -<Id>

                                     -<Othr>

        <Id>FR7617789000011234567800038</Id>

        </Othr></Id>

      <Ccy>EUR</Ccy>

        -<Svcr>

        -<FinInstnId>

        <BIC>DEUTFRPPXXX</BIC>

        </FinInstnId>

        </Svcr>

        </Acct>

        -<Ntry>

        <NtryRef>2013-07-24-12.00.47.546905</NtryRef>

        <Amt Ccy="EUR">40.46</Amt>

        <CdtDbtInd>DBIT</CdtDbtInd>

        <Sts>PDNG</Sts>

        -<ValDt>

        <Dt>2013-07-24</Dt>

        </ValDt>

        <AcctSvcrRef>ABCD******</AcctSvcrRef>

        <BkTxCd/>

        -<NtryDtls>

        -<TxDtls>

        -<Refs>

        <MsgId>SCOR1307240100200000000000000000019</MsgId>

        <AcctSvcrRef>ABCD******</AcctSvcrRef>

        <PmtInfId>00000000002468</PmtInfId>

        <InstrId>++123000000000049081660000020057772</InstrId>

        <EndToEndId>I0000020057772</EndToEndId>

        <TxId>00441N4201319218659235</TxId>

        <MndtId>++12300000000004908166</MndtId>

        </Refs>

        -<AmtDtls>

        -<InstdAmt>

        <Amt Ccy="EUR">40.46</Amt>

        </InstdAmt>

        -<TxAmt>

        <Amt Ccy="EUR">40.46</Amt>

        </TxAmt>

        </AmtDtls>

        -<RltdPties>

        -<Dbtr>

        <Nm>Client DB France</Nm>

        -<Id>

        -<PrvtId>

        -<Othr>

        <Id>FR14ZZZ123456</Id>

        -<SchmeNm>

        <Prtry>SEPA CdtrSchmeId</Prtry>

        </SchmeNm>

        </Othr>

        </PrvtId>

        </Id>

        </Dbtr>

        -<DbtrAcct>

        -<Id>

        <IBAN>FR7617789000011234567800038</IBAN>

        </Id>

        </DbtrAcct>

        -<Cdtr>

        <Nm>payer DB</Nm>

        -<PstlAdr>

        <Ctry>FR</Ctry>

        </PstlAdr>

        </Cdtr>

        -<CdtrAcct>

        -<Id>

        <IBAN>FR7630003004470005000123479</IBAN>

        </Id>

        </CdtrAcct>

        </RltdPties>

        -<RltdAgts>

        -<DbtrAgt>

        -<FinInstnId>

        <BIC>DEUTFRPPXXX</BIC>

        </FinInstnId>

        </DbtrAgt>

        -<CdtrAgt>

        -<FinInstnId>

        <BIC>SOGEFRPPXXX</BIC>

        </FinInstnId>

        </CdtrAgt>

        </RltdAgts>

        -<RmtInf>

        <Ustrd>-0000020057772 0008022</Ustrd>

        </RmtInf>

        -<RltdDts>

      <IntrBkSttlmDt>2013-07-24</IntrBkSttlmDt>

        </RltdDts>

        +<RtrInf>+---

        </TxDtls>

        </NtryDtls>

        </Ntry>

        </Ntfctn>

        </BkToCstmrDbtCdtNtfctn>

        </Document>                                   

        • 1. Re: Loading XML data In Oracle Database table
          odie_63

          Here's an example for the first three elements, you should be able to get the rest easily :

          SQL> select *

            2  from xmltable(

            3         xmlnamespaces(default 'urn:iso:std:iso:20022:tech:xsd:camt.054.001.02')

            4       , '/Document/BkToCstmrDbtCdtNtfctn'

            5         passing xmltype(bfilename('MY_DIR','my_file.xml'), nls_charset_id('AL32UTF8'))

            6         columns MsgId     varchar2(30)              path 'GrpHdr/MsgId'

            7               , CreDtTm   timestamp with time zone  path 'GrpHdr/CreDtTm'

            8               , Ntfctn_Id varchar2(30)              path 'Ntfctn/Id'

            9       )

          10  ;

           

          MSGID                          CREDTTM                             NTFCTN_ID

          ------------------------------ ----------------------------------- ------------------------------

          2013-07-24-12.00.52.486267     24/07/13 10:00:52,000000 +00:00     2013-07-24-12.00.52.473349

           

          • 2. Re: Loading XML data In Oracle Database table
            Debanjan Banerjee

            Thanks for your reply. I am getting the below error while executing above code:

             

            ORA-22288: file or LOB operation FILEOPEN failed

            No such file or directory

            ORA-06512: at "SYS.DBMS_LOB", line 523

            ORA-06512: at "SYS.XMLTYPE", line 287

            ORA-06512: at line 1

             

            Now My question is :

             

            1) The directory ('MY_DIR') should be created in  Database tier? We dont have access to the DB tier. I created the directory in Concurrent server, but still I am getting the same error.

            • 3. Re: Loading XML data In Oracle Database table
              odie_63

              The directory must point to a location the DB server has access to.

              It could be a mount point, a unc path (for windows OS), etc.