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.

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:

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...