This discussion is archived
1 Reply Latest reply: Sep 25, 2013 5:55 PM by Barbara Boehmer RSS

External tables to read xml files

947561 Newbie
Currently Being Moderated

My database version is: Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

 

I want to read following XML file through external table.

 

{code}

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

<addressbook>

<company>Amaze</company>

<comp_no>27648</comp_no>

<contact>

<contact_name>Sam Disuza </contact_name>

<address>Jurong Point, Singapore</address>

<phone>87654321</phone>

<dob>01121980</dob>

</contact>

<contact>

<contact_name>Suzy Disuza</contact_name>

<address>Jurong West, Singapore</address>

<phone>12345678</phone>

<dob>15081983</dob>

</contact>

</addressbook>

{code}

 

I want company and comp_no to be populated with every record. Please let me know how to modify the below code to include company and comp_no.

 

{code}

CREATE TABLE addressbook_ext

(contact_name VARCHAR2(2000),

address VARCHAR2(2000),

phone VARCHAR2(2000),

dob VARCHAR2(2000)

)

ORGANIZATION EXTERNAL

(

TYPE ORACLE_LOADER

DEFAULT DIRECTORY DAT_DIR

ACCESS PARAMETERS

(

records delimited by "</contact>"

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>",

phone char(2000) enclosed by "<phone>" and "</phone>",

dob char(2000) enclosed by "<dob>" and "</dob>"

)

)

LOCATION ('addressbook.xml')

)

PARALLEL

REJECT LIMIT UNLIMITED;

{code}

  • 1. Re: External tables to read xml files
    Barbara Boehmer Oracle ACE
    Currently Being Moderated

    External tables do not support XMLTYPE and aren't intended for parsing XML data.  I would use BFILENAME and XMLTABLE instead, as demonstrated below.

     

     

    SCOTT@orcl12c> HOST TYPE c:\my_oracle_files\addressbook.xml

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

    <addressbook>

    <company>Amaze</company>

    <comp_no>27648</comp_no>

    <contact>

    <contact_name>Sam Disuza </contact_name>

    <address>Jurong Point, Singapore</address>

    <phone>87654321</phone>

    <dob>01121980</dob>

    </contact>

    <contact>

    <contact_name>Suzy Disuza</contact_name>

    <address>Jurong West, Singapore</address>

    <phone>12345678</phone>

    <dob>15081983</dob>

    </contact>

    </addressbook>

     

    SCOTT@orcl12c> CREATE TABLE addressbook_ext

      2    (company  VARCHAR2(7),

      3     comp_no       NUMBER,

      4     contact_name  VARCHAR2(2000),

      5     address       VARCHAR2(2000),

      6     phone         VARCHAR2(2000),

      7     dob           DATE)

      8  /

     

    Table created.

     

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

      2  /

     

    Directory created.

     

    SCOTT@orcl12c> INSERT INTO addressbook_ext

      2    (company, comp_no, contact_name, address, phone, dob)

      3  SELECT t1.company, t1.comp_no,

      4         t2.contact_name, t2.address, t2.phone,

      5         TO_DATE (t2.dob, 'DDMMYYYY')

      6  FROM   (SELECT XMLTYPE

      7                   (BFILENAME

      8                     ('DAT_DIR', 'addressbook.xml'),

      9                    NLS_CHARSET_ID ('AL32UTF8')) xml_data

    10          FROM   DUAL) x,

    11         XMLTABLE

    12           ('addressbook'

    13            PASSING x.xml_data

    14            COLUMNS

    15              company       VARCHAR2(7)     PATH 'company',

    16              comp_no       NUMBER          PATH 'comp_no',

    17              contact       XMLTYPE         PATH 'contact') t1,

    18         XMLTABLE

    19           ('contact'

    20            PASSING t1.contact

    21            COLUMNS

    22              contact_name  VARCHAR2(2000)  PATH 'contact_name',

    23              address       VARCHAR2(2000)  PATH 'address',

    24              phone         VARCHAR2(2000)  PATH 'phone',

    25              dob           VARCHAR2(8)     PATH 'dob') t2

    26  /

     

    2 rows created.

     

    SCOTT@orcl12c> COLUMN contact_name FORMAT A12

    SCOTT@orcl12c> COLUMN address      FORMAT A23

    SCOTT@orcl12c> COLUMN phone        FORMAT A8

    SCOTT@orcl12c> SELECT * FROM addressbook_ext

      2  /

     

    COMPANY    COMP_NO CONTACT_NAME ADDRESS                 PHONE    DOB

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

    Amaze        27648 Sam Disuza   Jurong Point, Singapore 87654321 Mon 01-Dec-1980

    Amaze        27648 Suzy Disuza  Jurong West, Singapore  12345678 Mon 15-Aug-1983

     

    2 rows selected.

Legend

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