9 Replies Latest reply: Dec 7, 2012 2:46 AM by BluShadow RSS

    external table for reading xml file

    user808700
      Hi experts,
      I have an xml file and I want to read it with an external table.
      But when I select from the external table, no rows come.

      Below are the external table create script and xml file. My directory definition is correct(I checked).

      drop TABLE addressbook_ext

      CREATE TABLE addressbook_ext
      (contact_name VARCHAR2(2000),
      address VARCHAR2(2000)
      )
      ORGANIZATION EXTERNAL
      (
      TYPE ORACLE_LOADER
      DEFAULT DIRECTORY ext
      ACCESS PARAMETERS
      (
      records delimited by "</contact>"
      BADFILE 'bad.bad'
      LOGFILE 'log.log'
      fields
      (
      dummy1 char(2000) terminated by "<contact>",
      contact_name char(2000) enclosed by "<contact_name>" and "</contact_name>",
      address char(2000) enclosed by "<address>" and "</address>"
      )
      )
      LOCATION ('contact.xml')
      )
      PARALLEL
      REJECT LIMIT UNLIMITED;


      Here is the xml file content:
      <?xml version="1.0" encoding="UTF-8"?>
      <start>
      <contact><contact_name>Sam Disuza</contact_name><address>11</address></contact>
      <contact><contact_name>Sam Disuza3</contact_name><address>22</address></contact>
      </start>


      And here is the error message in the log file.
      Field Definitions for table ADDRESSBOOK_EXT
      Record format DELIMITED, delimited by </contact>
      Data in file has same endianness as the platform
      Rows with all null fields are accepted

      Fields in Data Source:

      DUMMY1 CHAR (2000)
      Terminated by "<contact>"
      Trim whitespace same as SQL Loader
      CONTACT_NAME CHAR (2000)
      Enclosed by "<contact_name>" and "</contact_name>"
      Trim whitespace same as SQL Loader
      ADDRESS CHAR (2000)
      Enclosed by "<address>" and "</address>"
      Trim whitespace same as SQL Loader
      KUP-04021: field formatting error for field ADDRESS
      KUP-04035: beginning enclosing delimiter not found
      KUP-04101: record 1 rejected in file /home/oracle/temp/contact.xml
      KUP-04021: field formatting error for field ADDRESS
      KUP-04035: beginning enclosing delimiter not found
      KUP-04101: record 2 rejected in file /home/oracle/temp/contact.xml
      KUP-04021: field formatting error for field CONTACT_NAME
      KUP-04023: field start is after end of record
      KUP-04101: record 3 rejected in file /home/oracle/temp/contact.xml


      Oracle version: 11gr2 on Linux on VMbox.

      Any ideas on what the problem might be ?

      Thanks...
        • 1. Re: external table for reading xml file
          Solomon Yakobson
          SQL> CREATE TABLE addressbook_ext(
            2                               contact_name VARCHAR2(2000),
            3                               address VARCHAR2(2000)
            4                              )
            5    ORGANIZATION EXTERNAL(
            6                          TYPE ORACLE_LOADER
            7                          DEFAULT DIRECTORY TEMP
            8                          ACCESS PARAMETERS(
            9                                            RECORDS DELIMITED BY "</contact>"
           10                                            FIELDS MISSING FIELD VALUES ARE NULL
           11                                                  (
           12                                                   dummy1 char(2000) terminated by "<contact>",
           13                                                   contact_name char(2000) enclosed by "<contact_name>" and "</contact_name>",
           14                                                   address char(2000) enclosed by "<address>" and "</address>",
           15                                                   dummy2 char(2000) terminated by "</start>"
           16                                                  )
           17                                           )
           18                          LOCATION ('contact.xml')
           19                         )
           20    PARALLEL
           21    REJECT LIMIT UNLIMITED
           22  /
          
          Table created.
          
          SQL> SET LINESIZE 132
          SQL> COLUMN CONTACT_NAME FORMAT A20
          SQL> COLUMN ADDRESS FORMAT A20
          SQL> SELECT * FROM addressbook_ext
            2  /
          
          CONTACT_NAME         ADDRESS
          -------------------- --------------------
          Sam Disuza           11
          Sam Disuza3          22
          
          
          SQL>   
          SY.

          Edited by: Solomon Yakobson on Jul 14, 2011 4:20 PM
          • 2. Re: external table for reading xml file
            user808700
            I got this error in the log file with your external table statement:

            LOG file opened at 07/15/11 10:26:23

            Field Definitions for table ADDRESSBOOK_EXT
            Record format DELIMITED, delimited by </contact>
            Data in file has same endianness as the platform
            Rows with all null fields are accepted

            Fields in Data Source:

            DUMMY1 CHAR (2000)
            Terminated by "<contact>"
            Trim whitespace same as SQL Loader
            CONTACT_NAME CHAR (2000)
            Enclosed by "<contact_name>" and "</contact_name>"
            Trim whitespace same as SQL Loader
            ADDRESS CHAR (2000)
            Enclosed by "<address>" and "</address>"
            Trim whitespace same as SQL Loader
            DUMMY2 CHAR (2000)
            Terminated by "</start>"
            Trim whitespace same as SQL Loader
            KUP-04021: field formatting error for field ADDRESS
            KUP-04035: beginning enclosing delimiter not found
            KUP-04101: record 1 rejected in file /home/oracle/temp/contact.xml
            KUP-04021: field formatting error for field ADDRESS
            KUP-04035: beginning enclosing delimiter not found
            KUP-04101: record 2 rejected in file /home/oracle/temp/contact.xml
            • 3. Re: external table for reading xml file
              odie_63
              Hi,
              I have an xml file and I want to read it with an external table.
              External tables are not XML parsers.
              Use the right tool for the job, for example :
              SQL> SELECT *
                2  FROM XMLTable('/start/contact'
                3        passing xmltype(
                4                  bfilename('XML_DIR','contact.xml')
                5                , nls_charset_id('AL32UTF8')
                6                )
                7        columns contact_name varchar2(2000) path 'contact_name'
                8              , address      varchar2(2000) path 'address'
                9       )
               10  ;
               
              CONTACT_NAME                ADDRESS
              --------------------------- -----------------------
              Sam Disuza                  11
              Sam Disuza3                 22
               
              You can create a parameterized view over that query, so that you can use it like an external table and change the file name at runtime if necessary.
              • 4. Re: external table for reading xml file
                Solomon Yakobson
                user808700 wrote:
                I got this error in the log file with your external table statement:
                Works fine on my 11g:
                SQL> SELECT  *
                  2    FROM  v$version
                  3  /
                
                BANNER
                --------------------------------------------------------------------------------
                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
                PL/SQL Release 11.2.0.1.0 - Production
                CORE    11.2.0.1.0      Production
                TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
                NLSRTL Version 11.2.0.1.0 - Production
                
                CREATE TABLE addressbook_ext(
                                             contact_name VARCHAR2(2000),
                                             address VARCHAR2(2000)
                                            )
                  ORGANIZATION EXTERNAL(
                                        TYPE ORACLE_LOADER
                                        DEFAULT DIRECTORY TEMP
                                        ACCESS PARAMETERS(
                                                          RECORDS DELIMITED BY "</contact>"
                                                          FIELDS MISSING FIELD VALUES ARE NULL
                                                                (
                                                                 dummy1 char(2000) terminated by "<contact>",
                                                                 contact_name char(2000) enclosed by "<contact_name>" and "</contact_name>",
                                                                 address char(2000) enclosed by "<address>" and "</address>",
                                                                 dummy2 char(2000) terminated by "</start>"
                                                                )
                                                         )
                                        LOCATION ('contact.xml')
                                       )
                  PARALLEL
                  REJECT LIMIT UNLIMITED
                / 
                
                Table created.
                
                SQL> SET LINESIZE 132
                SQL> COLUMN CONTACT_NAME FORMAT A20
                SQL> COLUMN ADDRESS FORMAT A20
                SELECT * FROM addressbook_ext
                / 
                
                CONTACT_NAME         ADDRESS
                -------------------- --------------------
                Sam Disuza           11
                Sam Disuza3          22
                
                
                SQL> 
                File contact.xml:
                <?xml version="1.0" encoding="UTF-8"?>
                <start>
                <contact><contact_name>Sam Disuza</contact_name><address>11</address></contact>
                <contact><contact_name>Sam Disuza3</contact_name><address>22</address></contact>
                </start>
                Log file:
                 LOG file opened at 07/15/11 06:21:40
                
                Field Definitions for table ADDRESSBOOK_EXT
                  Record format DELIMITED, delimited by </contact>
                  Data in file has same endianness as the platform
                  Rows with all null fields are accepted
                
                  Fields in Data Source: 
                
                    DUMMY1                          CHAR (2000)
                      Terminated by "<contact>"
                      Trim whitespace same as SQL Loader
                    CONTACT_NAME                    CHAR (2000)
                      Enclosed by "<contact_name>" and "</contact_name>"
                      Trim whitespace same as SQL Loader
                    ADDRESS                         CHAR (2000)
                      Enclosed by "<address>" and "</address>"
                      Trim whitespace same as SQL Loader
                    DUMMY2                          CHAR (2000)
                      Terminated by "</start>"
                      Trim whitespace same as SQL Loader
                SY.
                • 5. Re: external table for reading xml file
                  Solomon Yakobson
                  user808700 wrote:

                  Oracle version: 11gr2 on Linux on VMbox*.
                  Oracle doesn't certify VM. To be precise, Oracle will provide support VM if issue you encountered on VM occurs on non-VM Oracle install.

                  SY.
                  • 6. Re: external table for reading xml file
                    Hoek
                    +1000 for that great reminder! You saved my day with that example, Marc :)
                    • 7. Re: external table for reading xml file
                      user808700
                      Hi again,
                      I used the same sql script:

                      drop TABLE addressbook_ext;

                      CREATE TABLE addressbook_ext(
                           contact_name VARCHAR2(2000),
                                address VARCHAR2(2000)
                                )
                      ORGANIZATION EXTERNAL(
                           TYPE ORACLE_LOADER
                           DEFAULT DIRECTORY EXT
                           ACCESS PARAMETERS(
                                               RECORDS DELIMITED BY "</contact>"
                                               FIELDS MISSING FIELD VALUES ARE NULL
                                                    (
                                                         dummy1 char(2000) terminated by "<contact>",
                                                         contact_name char(2000) enclosed by "<contact_name>" and "</contact_name>",
                                                         address char(2000) enclosed by "<address>" and "</address>",
                                                         dummy2 char(2000) terminated by "</start>"
                                                    )
                                               )
                      LOCATION ('contact.xml')
                      )
                      PARALLEL
                      REJECT LIMIT UNLIMITED
                      ;


                      SET LINESIZE 132;
                      COLUMN CONTACT_NAME FORMAT A20;
                      COLUMN ADDRESS FORMAT A20;


                      I switched to an Oracle Virtual Box, Windows 64 bit Oracle.
                      The only difference btw environments is, mine 64 bit yours 32 bit.



                      SQL> select * from v$version;

                      BANNER
                      -----------------------------------------------------------------------------

                      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                      PL/SQL Release 11.2.0.1.0 - Production
                      CORE 11.2.0.1.0 Production
                      TNS for 64-bit Windows: Version 11.2.0.1.0 - Production
                      NLSRTL Version 11.2.0.1.0 - Production


                      Log message below:
                      LOG file opened at 07/18/11 21:57:47

                      Field Definitions for table ADDRESSBOOK_EXT
                      Record format DELIMITED, delimited by </contact>
                      Data in file has same endianness as the platform
                      Rows with all null fields are accepted

                      Fields in Data Source:

                      DUMMY1 CHAR (2000)
                      Terminated by "<contact>"
                      Trim whitespace same as SQL Loader
                      CONTACT_NAME CHAR (2000)
                      Enclosed by "<contact_name>" and "</contact_name>"
                      Trim whitespace same as SQL Loader
                      ADDRESS CHAR (2000)
                      Enclosed by "<address>" and "</address>"
                      Trim whitespace same as SQL Loader
                      DUMMY2 CHAR (2000)
                      Terminated by "</start>"
                      Trim whitespace same as SQL Loader
                      KUP-04021: field formatting error for field ADDRESS
                      KUP-04035: beginning enclosing delimiter not found
                      KUP-04101: record 1 rejected in file c:\temp\contact.xml
                      KUP-04021: field formatting error for field ADDRESS
                      KUP-04035: beginning enclosing delimiter not found
                      KUP-04101: record 2 rejected in file c:\temp\contact.xml
                      • 8. Re: external table for reading xml file
                        Arul Kumar Singaravelu
                        conclusion..? i'm facing the same problem..
                        • 9. Re: external table for reading xml file
                          BluShadow
                          Arul Kumar Singaravelu wrote:
                          conclusion..? i'm facing the same problem..
                          Then please start a thread with your own question giving details of what your problem is exactly.

                          {message:id=9360002}

                          As already mentioned above, using External Tables for reading XML files is not the ideal way to do it as External Tables are not really designed for XML processing.
                          The XML file should be read in CLOBwise and then processed as XML using the in-built XML functionality of the database as odie demonstrated above.