XML DB - Introduction

Oracle allows XML documents to be stored in the database in three different ways:

Unstructured - stored in a CLOB
Structured - in object-relational tables
Binary - tags compressed and stored in a CLOB

The first page covers preparatory steps and also discusses and example that will be used in the following pages.

Example

This pages uses an XML document containing a set of points representing the co-ordinates of a route (road, railway, canal etc).

For example the following is the XML document for the Great Orme Tramway in Llandudno, Wales

<?xml version="1.0"?>
<route>
  <title>Great Orme Tramway</title>
  <centre>53.328387,-3.839557</centre>
  <zoom>16</zoom>
  <line id="1">
    <point>53.32724,-3.835605</point>
    <point>53.327511,-3.835986</point>
    <point>53.327777,-3.836697</point>
    <point>53.328046,-3.837305</point>
    <point>53.328339,-3.837928</point>
    <point>53.328425,-3.83822</point>
    <point>53.328626,-3.838598</point>
    <point>53.328677,-3.838783</point>
    <point>53.328669,-3.839604</point>
    <point>53.328667,-3.840342</point>
    <point>53.328744,-3.841262</point>
    <point>53.328768,-3.841398</point>
    <point>53.328949,-3.842007</point>
    <point>53.329026,-3.842104</point>
    <point>53.329113,-3.842144</point>
    <point>53.329946,-3.842141</point>
    <point>53.330179,-3.842125</point>
    <point>53.330296,-3.842209</point>
    <point>53.33038,-3.842353</point>
    <point>53.330423,-3.842549</point>
    <point>53.330588,-3.843512</point>
  </line>
</route>

XML Schema

In order to use structured storage for the XML document in the Oracle database, it is necessary first to create an XML schema. The XML schema defines the structure of the document. All documents using this schema must comply to the specification. Effectively the XML schema defines the syntax and to some extent the semantics of the XML document.

Developing an XML schema from scratch would have been too time consuming, so I searched the internet and discovered http://www.freeformatter.com/xsd-generator.html which generates an XML schema from a sample XML document. The above document is sufficient to create an XML schema for these examples. It may be necessary to extend either the sample XML document or the generated XML schema to represent additional elements or attributes.

I reformatted the output from the XSD generator after I had cut-and-pasted it to my laptop.

The reformatted XML schema is as follows:

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified" attributeFormDefault="unqualified">
  <xs:element name="route">
    <xs:complexType>
      <xs:sequence><xs:element name="title" type="xs:string"/>
        <xs:element name="centre" type="xs:string"/>
        <xs:element name="zoom" type="xs:byte"/>
        <xs:element name="line">
        <xs:complexType>
          <xs:sequence>
          <xs:element name="point" type="xs:string" minOccurs="0" maxOccurs="unbounded"/>
            </xs:sequence>
            <xs:attribute name="id" type="xs:byte"/>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

As you would expect the XML schema reflects the structure of the original XML document.

So now we have an XML schema and an XML document, what can we do with it?

Users

Fortunately I do not have to worry about security, so I created a user called XML and granted it DBA privileges. Obviously you may need to take additional security precautions in your environment.

Directories

Before you can access an XML document within XML DB, it must be created as a resource.

There are several ways to load XML documents. The simplest is to use a BFILE which is a variant of the LOB feature where the metadata is located within the database, but the document itself is stored in an external file system.

In this example we will store our documents in the /home/oracle/xdb directory. We will create an Oracle directory object called XDBDIR in the database for this directory:

CREATE OR REPLACE DIRECTORY xdbdir AS '/home/oracle/xdb';

All source documents for my examples (XML schema and XML data) will be located in the above directory.

In Oracle 11.2.0.3 there is a default directory called XMLDIR with the following path:

$ORACLE_HOME/rdbms/xml

For example:

/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/xml

Folders

XML DB stores documents within the database in a virtual folders structure. The virtual folders must exist before the documents can be created.

Virtual folders are created using the CREATEFOLDER function in DBMS_XDB. A parent folder must exist before the child folder is created.

In this example we will store schemas in /route/schema and data in /route/data. The folders are created as follows:

DECLARE
  res BOOLEAN;
BEGIN
  res := dbms_xdb.createfolder('/route/schema');  
  res := dbms_xdb.createfolder('/route/schema');
  res := dbms_xdb.createfolder('/route/data');
END;
/

The XSL schema can be extended to include tags that specify how Oracle should store the document within the Oracle database. This allows more control of storage options e.g. specification of object names etc.

In order to include the XDB tags, we must modify the header of the XSL schema as follows:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"
version="1.0">

There are two ways of storing array data in XML DB; using a VARRAY and using a nested table. In the second page in this series we will investigate VARRAY storage; in the third page we will cover nested tables.