This discussion is archived
3 Replies Latest reply: Sep 3, 2013 6:29 PM by Barbara Boehmer RSS

External Table with XML File issue

user8007837 Newbie
Currently Being Moderated

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 Newbie
    Currently Being Moderated

    Any ideas?

     

    Thanks in advance.

  • 2. Re: External Table with XML File issue
    DK2010 Guru
    Currently Being Moderated

    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 Oracle ACE
    Currently Being Moderated

    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points