3 Replies Latest reply: Sep 3, 2013 8:29 PM by Barbara Boehmer RSS

    External Table with XML File issue

    user8007837

      Hi,

       

      I am using an External table to retreive data from XML file (Oracle 11g database), which is working fine when all columns get data and the issue comes when one of these columns are NULL. When a record is NULL, it comes with xsi:nil="true" tag and that record is excluded from the External Table data. Please have a look at my External table script and the sample xml file.

       

      Can someone please let me know how to handle xsi:nil="true" tags in External table? Thanks in advance.

       

      External Table Script:

       

      CREATE TABLE ACCOUNTS_XML_EXT"

        (

          "COLUMN1" VARCHAR2(255 BYTE),

          "COLUMN2" VARCHAR2(255 BYTE),

          "COLUMN3" VARCHAR2(255 BYTE)

        )

        ORGANIZATION EXTERNAL

          (

          TYPE ORACLE_LOADER DEFAULT DIRECTORY "CUST_PROF_DIR" ACCESS PARAMETERS ( RECORDS DELIMITED BY "</records>" FIELDS MISSING FIELD VALUES ARE NULL (

      dummy1 CHAR(2000) terminated BY "</type>" ,

      "COLUMN1" CHAR(255) enclosed BY "<Id>" AND "</Id>" ,

      "COLUMN2" CHAR(255) enclosed BY "<IsActive>"   AND "</IsActive>" ,

      "COLUMN3" CHAR(255) enclosed BY "<Address>" AND "</Address>" ,

      dummy2    CHAR(2000) terminated BY "</queryResult>" ) ) LOCATION ( "CUST_DIR":'cust.xml' )

        )

        REJECT LIMIT UNLIMITED PARALLEL ;

      XML File:

       

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

      - <queryResult xmlns="http://www.example.com/dataload" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

      - <records xsi:type="sObject">

        <type>Account</type>

        <Id>001</Id>

        <IsActive>false</IsActive>

        <Address>1 Test Road</Address>

        </records>

      - <records xsi:type="sObject">

        <type>Account</type>

        <Id>002</Id>

        <IsActive>false</IsActive>

        <Address xsi:nil="true" />

      </records>

      </queryResult>

       

      In the above example, 2nd record is exclueded from the External Table.

        • 1. Re: External Table with XML File issue
          user8007837

          Any ideas?

           

          Thanks in advance.

          • 2. Re: External Table with XML File issue
            DK2010

            Hi,

             

            seems Address is not in correct format for second record

            try something like

             

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

            - <queryResult xmlns="http://www.example.com/dataload" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

            - <records xsi:type="sObject">

              <type>Account</type>

              <Id>001</Id>

              <IsActive>false</IsActive>

              <Address>1 Test Road</Address>

              </records>

            - <records xsi:type="sObject">

              <type>Account</type>

              <Id>002</Id>

              <IsActive>false</IsActive>

              <Address> A </Address>

              --<Address xsi:nil="true" /> </Address>

            </records>

            </queryResult>

            HTH

            • 3. Re: External Table with XML File issue
              Barbara Boehmer

              External tables are not intended to support xmltype or clob and trying to workaround that and parse an xml file has its problems, as you have discovered.  A better method is to use bfilename and xmltable, as demonstrated below.

               

              SCOTT@orcl12c> HOST TYPE cust.xml

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

              <queryResult xmlns="http://www.example.com/dataload" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">

              <records xsi:type="sObject">

              <type>Account</type>

              <Id>001</Id>

              <IsActive>false</IsActive>

              <Address>1 Test Road</Address>

              </records>

              <records xsi:type="sObject">

              <type>Account</type>

              <Id>002</Id>

              <IsActive>false</IsActive>

              <Address xsi:nil="true" />

              </records>

              </queryResult>

               

              SCOTT@orcl12c> CREATE OR REPLACE DIRECTORY cust_dir AS 'c:\my_oracle_files'

                2  /

               

              Directory created.

               

              SCOTT@orcl12c> SELECT *

                2  FROM   XMLTABLE

                3           (XMLNAMESPACES (DEFAULT 'http://www.example.com/dataload'),

                4            'queryResult/records'

                5            PASSING XMLTYPE (BFILENAME ('CUST_DIR', 'cust.xml'), NLS_CHARSET_ID ('WE8ISO8859P1'))

                6            COLUMNS

                7              column1 VARCHAR2( 7) PATH 'Id',

                8              column2 VARCHAR2( 7) PATH 'IsActive',

                9              column3 VARCHAR2(30) PATH 'Address')

              10  /

               

              COLUMN1 COLUMN2 COLUMN3

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

              001     false   1 Test Road

              002     false

               

              2 rows selected.