XMLTYPE

Introduction
Using XMLTYPE in PL/SQL
Using XMLTYPE as persistent storage
- Storage Models
Schema-based XMLTYPE
Further Information

Introduction

The XMLTYPE datatype is part of Oracle XMLDB and was originally included in Oracle database 9.2. It is a native datatype solely used for the storage of XML data and can be used as a datatype for the declaration of variables or parameters and return values in procedures and functions in PL/SQL or as a datatype for columns in relational tables. It is implemented as an OBJECT type and contains various member functions for operating upon the content. It can also be used to validate against a defined XML Schema Document (XSD).

The SQL/XML toolkit operates natively on XMLTYPE data.

Passing invalid XML data into an XMLTYPE variable will fail with an exception, generally ORA-31011: XML Parsing Failed along with other exceptions giving more information about what caused the problem.

Using XMLTYPE in PL/SQL

As mentioned above, XMLTYPE can be used for the declaration of variables, parameters and function return values in PL/SQL. Since it is an object type, you can use the default constructor to convert character data into XMLTYPE.

DECLARE
  x  XMLTYPE;
BEGIN
  x := XMLTYPE('<x>10</x>');
END;

Using XMLTYPE as persistent storage

XMLTYPE can be used to define columns in relational tables :

CREATE TABLE xmltest ( a XMLTYPE );

or as an "XML table" :

CREATE TABLE xmltest OF XMLTYPE;

Storage Models

It is important to understand what Oracle is doing when you create an XMLTYPE column. Internally, it stores the data in one of three ways, depending on the storage model that you define. There are (as of 11.2) three classes of storage model, Unstructured, Structured and Binary XML. There are many advantages and disadvantages of each model which I will not mention here. The best source of information for this is to see the Oracle XMLDB documentation.

Unstructured Storage

Unstructured Storage, otherwise known as CLOB storage, simply stores the XML document in a CLOB. This was the default (until 11.2.0.2) for XMLTYPE columns. From 11.2.0.2, the default is to use Binary XML.

You can create this via a declaration such as :

CREATE TABLE xmltest ( a XMLTYPE ) XMLTYPE COLUMN a STORE AS BASICFILE CLOB;

CREATE TABLE xmltest ( a XMLTYPE ) XMLTYPE COLUMN a STORE AS SECUREFILE CLOB;

Note, see Oracle® Database SecureFiles and Large Objects Developer's Guide (docs.oracle.com) for more information about what BasicFile and SecureFile means when dealing with LOBs.

Structured Storage

Structured Storage, otherwise known as object-relational storage, is how Oracle stores the data internally when the XMLTYPE is XML Schema-based. Oracle will extract the data from the XML document and store the data in relational tables and objects (since it knows what the XML structure will be like). This will be discussed in a later section.

Binary XML

Binary XML is stored in a binary format after the XML document has been parsed. It is still stored in it's entirety (like unstructured storage) but can also take advantage of any XML Schema definition (like structured storage). As of 11.2.0.2, the use of SecureFile Binary XML for XMLTYPE columns is the default.

CREATE TABLE xmltest ( a XMLTYPE ) XMLTYPE COLUMN a STORE AS SECUREFILE BINARY XML;

CREATE TABLE xmltest ( a XMLTYPE ) XMLTYPE COLUMN a STORE AS BASICFILE BINARY XML;

Schema-based XMLTYPE

XML Schema is a way of validating an XML document against a series of rules. A full coverage of XML Schema is out of scope of this, for more information see XML Schema specification (w3.org). In Oracle, you register a schema in the database by the use of the built-in DBMS_XMLSCHEMA package.

BEGIN
  dbms_xmlschema.deleteSchema(schemaurl     => 'XMLTEST.xsd',
                              delete_option => dbms_xmlschema.DELETE_CASCADE_FORCE);
END;

BEGIN
  dbms_xmlschema.registerSchema(
      schemaurl => 'XMLTEST.xsd',
      schemadoc => '<?xml version="1.0" encoding="ISO-8859-1" ?>' ||
                   '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">' ||
                     '<xs:element name="test" type="xs:string"/>' ||
                   '</xs:schema>',
      gentypes  => FALSE,
      options   => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
      local     => FALSE);
END;

Note, the "local" parameter of FALSE here defines this XSD as a global schema which makes it visible to all users. See Local and Global XML Schemas (docs.oracle.com) for further information. The "options" parameter set to DBMS_XMLSCHEMA.REGISTER_BINARYXML allows this schema to be used in a Binary XML context.

Once the XSD is registered, you can then create an XMLTYPE table (or XMLTYPE column) or validate an XMLTYPE declared PL/SQL variable against the schema.

Creating an XMLTYPE schema-based PL/SQL variable

You can use the XMLTYPE constructor to specify the registered XSD, but it is not until you call the XMLTYPE .schemaValidate method that it actually gets validated :

DECLARE
  x  XMLTYPE := XMLTYPE('<test>10</test>', 'XMLTEST.xsd');
BEGIN
  x.schemaValidate;
END;

If the validation fails, then the error stack will contain information about why, e.g. :

ORA-31038 : Invalid number value "a"

Creating an XMLTYPE schema-based column

CREATE TABLE xmltest ( a XMLTYPE ) XMLTYPE COLUMN a STORE AS BINARY XML X
  XMLSCHEMA "XMLTEST.xsd" ELEMENT "test";

or, to create an XMLTYPE table :

CREATE TABLE xmltest ( a XMLTYPE ) XMLTYPE STORE AS BINARY XML X
  XMLSCHEMA "XMLTEST.xsd" ELEMENT "test";