Reading an xml file as an oracle external table

There is a couple of posts around that imply or claim an oracle external table to be able to read xml files by design. This is not true, really. It may work for you or actually may fit your current xml file structure but you should generally not approach to read xml by external tables. Here is why.

What external tables or sql loader for better can do actually is reading line oriented, file based data in bulk and quite fast. It offers a lot of settings to parametrize the loading process, the reader, according to your input format. You may for example have a look at the excellent series of posts discussing this options by Jiri starting here Oracle External Tables by Examples part 1 – TAB delimited fields.

Well, this functionality, may also be (mis)used to read xml instances from files. Given a file as the following one may be interested in extracting the contents of the token tags.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<FILE_INFO>
	<PERSON_BILD>
		<token_row><token>Fritz &amp; Fischer</token></token_row>
		<token_row><token>Boris Borsberg</token></token_row>
	</PERSON_BILD>
</FILE_INFO>

Some external table definition may then look like this:

Installing semantic technologies support on a fresh Oracle 11gR2.0.1

The following sketches the prerequisites of using Oracle Database Semantic Technologies, see Semantic Technologies Center and Oracle® Database Semantic Technologies Developer’s Guide as an overview and as the developer’s guide respectively, in your databse applications.

The procedure has been executed on a fresh Oracle 11R2.0.1 running on x64 Windows. The semantic technology stack in fact requires an enterprise edition of Oracle, featuring the partitioning and the spatial options as well as the underlying components XDB, JAVAVM and ORDIM to be installed … and licenced. Do check in advance whether this basement is affordable against your business budget because according to the current Oracle global price list by processor, an enterprise edition is around k$ 47, adding partioning by k$ 11 and spatial by k$ 17 plus updates and support. Do note also, that the standard and the standard one editions of Oracle, offering a subset of spatial called Oracle locator is not not sufficient to run the semantic technology layer. That is, it takes a couple of bucks to even create your first simple triple of sematic data with Oracle.

Checking the Oracle base software installation (anything executed as sysdba)

This section of the article, checking the Oracle base software installation itself, has been proof-taken from Stanley Guan’s blog Xml and More, entry Installation of Oracle Semantic Technologies. I can’t imagine why he has skipped the actual steps involving sematic-related code and objects there, moving me to write another post. However, Overview of Oracle Database Semantic Technologies is also worth a look really.

-- verify the underlying components, namely XDB, JAVAVM and ORDIM
select comp_id,version,status from dba_registry where comp_id in ('JAVAVM','ORDIM','XDB');

COMP_ID  VERSION      STATUS
-------- ----------   -------
ORDIM    11.2.0.1.0   VALID
XDB      11.2.0.1.0   VALID
JAVAVM   11.2.0.1.0   VALID

-- assure the options partitioning and spatial are available
select parameter, VALUE from v$option where parameter in ('Partitioning', 'Spatial') order by 1;

PARAMETER     VALUE
------------  ------
Partitioning  TRUE
Spatial       TRUE

-- additionally check spatial in detail
set serveroutput on
execute validate_sdo;

select comp_id, control, schema, version, status, comp_name from dba_registry where comp_id='SDO';

COMP_ID   CONTROL   SCHEMA   VERSION      STATUS   COMP_NAME
--------  --------  -------  -----------  -------  ----------
SDO       SYS       MDSYS    11.2.0.1.0   VALID    Spatial

select object_name, object_type, status from dba_objects where owner='MDSYS' and status <> 'VALID';

Es wurden keine Zeilen ausgewõhlt