4 Replies Latest reply on Oct 31, 2015 10:44 AM by PamNL

    Loading xml data file through sqlloader program not working

    3020124

      Hi,

       

      DB Version: 11.2.0.2.0

      Ebs Version: 12.1.3

       

      We are trying to load data from a XML file through sqlloader program but it is giving error and the record is discarded. Below are my table structure, control file and data file structure. The file is loading properly if the data file contains full end date line like "<p1:EndDate>2004-08-01</p1:EndDate>" just below the start date line. But it is not loading properly if there is a null value for end date in the data file which doesn't have a tag in the file.

       

      The error I am receiving is below.

      "Record 1: Rejected - Error on table TEST1, column ENABLED_TO.

      Initial enclosure character not found "

       

      But I need the record to be inserted with end date value as null.

       

      Please someone help me solving the issue.

       

      Table Structure

      ==================

      CREATE TABLE test1

      (

         value                       VARCHAR2(150)

      , description                 VARCHAR2(240)

      , enabled_from                DATE

      , enabled_to                  DATE

      , id                          VARCHAR2(150)

      , type                        VARCHAR2(150)

      );

       

      Data File Structure

      ==================

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

      <p:FinancialReportingCenterList xmlns:p="http://schema.company.com/entities/financial/p/v001">

        <p:FinancialReportingCenter>

          <p:AccountingSystem>COMPANY</p:AccountingSystem>

          <p:FinancialReportingCenterIdentifier>100</p:FinancialReportingCenterIdentifier>

          <p1:LocalizedString locale="en">DC MUNT</p1:LocalizedString>

          <p1:ValidityPeriod>

            <p1:OpenEndedPeriod>

              <p1:StartDate>2004-08-01</p1:StartDate>

            </p1:OpenEndedPeriod>

          </p1:ValidityPeriod>

          <p:LinkedActivityCenterId>660b00b8-9519-4a64-b65f-b3ab036d7ba4</p:LinkedActivityCenterId>

        </p:FinancialReportingCenter>

        <p:FinancialReportingCenter>

          <p:AccountingSystem>COMPANY</p:AccountingSystem>

          <p:FinancialReportingCenterIdentifier>200</p:FinancialReportingCenterIdentifier>

          <p1:LocalizedString locale="en">DC GEEL</p1:LocalizedString>

        </p:FinancialReportingCenter>

      </p:FinancialReportingCenterList>

       

      Control File

      ==================

      load data

      infile 'example.xml' "str '</p:FinancialReportingCenter>'"

      REPLACE

      into table test1

      TRAILING NULLCOLS

      (

      dummy                                 filler char(2000) terminated by "<p:FinancialReportingCenter>",

      dummy2                                filler char(2000) terminated by "</p:AccountingSystem>",

      VALUE                             enclosed by "<p:FinancialReportingCenterIdentifier>" and "</p:FinancialReportingCenterIdentifier>",

      DESCRIPTION                           enclosed by "<p1:LocalizedString locale=\"en\">" and "</p1:LocalizedString>",

      dummy3                                 filler char(2000) terminated by "<p1:ValidityPeriod>",

      dummy4                                 filler char(2000) terminated by "<p1:OpenEndedPeriod>",

      ENABLED_FROM  DATE(10) 'YYYY-MM-DD'   enclosed by "<p1:StartDate>" and "</p1:StartDate>",

      ENABLED_TO DATE(10) 'YYYY-MM-DD'      enclosed by "<p1:EndDate>" and "</p1:EndDate>",

      --dummy5                                 filler char(2000) terminated by "</p1:OpenEndedPeriod>",

      dummy6                                 filler char(2000) terminated by "</p1:ValidityPeriod>",

      ID                                 enclosed by "<p:LinkedActivityCenterId>" and "</p:LinkedActivityCenterId>",

      dummy1                                filler char(2000) terminated by "</p:FinancialReportingCenterList>"

      )

       

      Thanks,

      Krushna

        • 1. Re: Loading xml data file through sqlloader program not working
          Pravin Takpire

          Can you try and add

          ENABLED_TO DATE(10) 'YYYY-MM-DD'      enclosed by "<p1:EndDate>" and "</p1:EndDate>",

          this to end in CTL file instead in between

          regards

          Pravin

          • 2. Re: Loading xml data file through sqlloader program not working
            3020124

            Hi Pravin,

             

            Thanks for your reply.

             

            I tried modifying control file according to your suggestion and removed the enabled_to field from in between and added at the end. It is inserting record into the table but the end date field is populated as NULL even if the data file contains some value.

             

            Please suggest.

             

            Thanks,

            Krushna

            • 3. Re: Loading xml data file through sqlloader program not working
              Pravin Takpire

              Try this in your CTL file in original location

               

              ENABLED_TO DATE(10) 'YYYY-MM-DD'      enclosed by "<p1:EndDate>" and "</p1:EndDate>" NULLIF (age="unknown"),

              or

              ENABLED_TO DATE(10) 'YYYY-MM-DD'      enclosed by "<p1:EndDate>" and "</p1:EndDate>" NULLIF age="unknown",

               

              If this does not work then Raise SR

              regards

              Pravin

              • 4. Re: Loading xml data file through sqlloader program not working
                PamNL

                Maybe this is of some help to you, see Load xml data in Oracle table

                 

                1) Reading headers and details using two separate XMLTables : 

                DECLARE acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') ); BEGIN insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks) select x1.status_code,         x1.status_remarks,         x2.segment_number,         x2.remarks from xmltable(   '/ACCOUNT_HEADER_ACK/HEADER'   passing acct_doc   columns header_no      for ordinality,           status_code    number        path 'STATUS_CODE',           status_remarks varchar2(100) path 'STATUS_REMARKS' ) x1, xmltable(   '$d/ACCOUNT_HEADER_ACK/DETAILS[$hn]/DETAIL'   passing acct_doc as "d",           x1.header_no as "hn"   columns segment_number number        path 'SEGMENT_NUMBER',           remarks        varchar2(100) path 'REMARKS' ) x2 ; END;

                The first one (aliased X1) extracts all headers into separate rows. The generated column HEADER_NO is used to keep track of the header rank inside the document.

                Then we join a second XMLTable (X2), passing it HEADER_NO, so that we can access the corresponding DETAIL elements.

                 

                2) Reading with a single XMLTable, but a little more complex XQuery : 

                DECLARE acct_doc xmltype := xmltype( bfilename('TEST_DIR','acct.xml'), nls_charset_id('AL32UTF8') ); BEGIN insert into xxrp_acct_details (status_code, status_remarks, segment_number, remarks) select x.* from xmltable(   'for $i in /ACCOUNT_HEADER_ACK/HEADER    return     for $j in $i/following-sibling::DETAILS[1]/DETAIL     return element r {$i, $j}'   passing acct_doc   columns status_code    number        path 'HEADER/STATUS_CODE',           status_remarks varchar2(100) path 'HEADER/STATUS_REMARKS',           segment_number number        path 'DETAIL/SEGMENT_NUMBER',           remarks        varchar2(100) path 'DETAIL/REMARKS' ) x ; END;

                 

                 

                Here's the link to the documentation regarding XMLTable and XQuery in Oracle :

                http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28369/xdb_xquery.htm#CBAGCBGJ