Oracle Utilities - Mapping XML files as External Tables

Author: Packt Publishing Published on: 4th Jan 2010

XML has become a de facto information exchange format, which is why oracle has included the XML Database (XDB) feature from 9.2.0. However, it requires the data to be actually loaded into the database before it can be processed. An External Table allows the user to take a quick look at the contents of the external file prior to performing any further processing.

Ads

In this example an External Table is created out of an XML file. This file is read by means of a CLOB field, and some further XDB commands can be issued against the external XML file to extract and view data.

Let's create the external XML file first:

Sample Code

create table EMPLOYEES_XML (xmlFile CLOB)
organization external (
type ORACLE_LOADER
default directory EXTTABDIR
access parameters (
fields (xmllob char terminated by ',')
column transforms (xmlFile from lobfile(xmllob))
)
location('employees.dat')
)
reject limit unlimited

The employees.dat file contains the file name of the XML file to load as an external CLOB file. This file, for the purpose of the demo, contains the file name: employees.xml.

Ads

Now the file can be queried from the database as if it was a regular table with a single XML column.