1 Reply Latest reply: Sep 25, 2013 7:55 PM by Barbara Boehmer RSS

    External tables to read xml files

    947561

      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

          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.