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.
x := XMLTYPE('<x>10</x>');
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;
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, otherwise known as CLOB storage, simply stores the XML document in a CLOB. This was the default (until 188.8.131.52) for XMLTYPE columns.
From 184.108.40.206, 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, 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 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 220.127.116.11, 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;
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.
dbms_xmlschema.deleteSchema(schemaurl => 'XMLTEST.xsd',
delete_option => dbms_xmlschema.DELETE_CASCADE_FORCE);
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"/>' ||
gentypes => FALSE,
options => DBMS_XMLSCHEMA.REGISTER_BINARYXML,
local => FALSE);
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 :
x XMLTYPE := XMLTYPE('<test>10</test>', 'XMLTEST.xsd');
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";