Oracle External Tables - Loading XML file
The Oracle external tables feature allows us to access data in external sources as if it is a table in the database. An XML file is an Extensible Markup Language file with markup and content. It consists of markup constructs like Tag, Attribute and Contents or Element.
Oracle can parse a XML file in many ways like the XML as a datatype, DOM parser .
Now to load a xml file in oracle database with the concept of External Table is shown below:
Suppose we take a xml file namely addressbook.xml which contains address information in xml file format as follows:
<?xml version="1.0" encoding="UTF-8"?>
<contact_name>Sam Disuza </contact_name>
<address>Jurong Point, Singapore</address>
<address>Jurong West, Singapore</address>
Now the external table definition goes here:
CREATE TABLE addressbook_ext
DEFAULT DIRECTORY ext_tab_dir
records delimited by "</contact>"
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>"
REJECT LIMIT UNLIMITED;
Now we make a query on the external table:
select * from addressbook_ext;
CONTACT_NAME ADDRESS PHONE DOB
Sam Disuza Jurong Point, Singapore 87654321 01121980
Suzy Disuza Jurong West, Singapore 12345678 15081983
Posted by Saurav Mitra