5
Structured Mapping of XMLType

This chapter introduces XML Schema and explains how XML schema are used in Oracle XML DB applications. It describes how to register your XML schema and create storage structures for storing schema-based XML. It explains in detail the mapping from XML to SQL storage types, including techniques for maintaining the DOM fidelity of XML data.This chapter also describes how queries over XMLType tables and columns based on this mapping are optimized using query rewrite techniques. It discusses the mechanism for generating XML schemas from existing object types.

This chapter contains the following sections:

• Introducing XML Schema
• XML Schema and Oracle XML DB
• Using Oracle XML DB and XML Schema
• Introducing DBMS_XMLSCHEMA
• Registering Your XML Schema Before Using Oracle XML DB
• Deleting Your XML Schema Using DBMS_XMLSCHEMA
• Guidelines for Using Registered XML Schemas
• Generating XML Schema Using DBMS_XMLSCHEMA.generateSchema()
• XML Schema-Related Methods of XMLType
• Managing and Storing XML Schema
• DOM Fidelity
• Creating XMLType Tables and Columns Based on XML Schema
• Specifying SQL Object Type Names with SQLName, SQLType Attributes
• Mapping of Types Using DBMS_XMLSCHEMA
• XML Schema: Mapping SimpleTypes to SQL
• XML Schema: Mapping complexTypes to SQL
• Oracle XML DB complexType Extensions and Restrictions
• Further Guidelines for Creating XML Schema-Based XML Tables
• Query Rewrite with XML Schema-Based Structured Storage
• Creating Default Tables During XML Schema Registration
• Ordered Collections in Tables (OCTs)
• Cyclical References Between XML Schemas
• Frequently Asked Questions (FAQs): XML DB, XML Schema-Based Issues

Introducing XML Schema

The XML Schema Recommendation was created by the World Wide Web Consortium (W3C) to describe the content and structure of XML documents in XML. It includes the full capabilities of Document Type Definitions (DTDs) so that existing DTDs can be converted to XML schema. XML schemas have additional capabilities compared to DTDs.

XML Schema and Oracle XML DB

XML Schema is a schema definition language written in XML. It can be used to describe the structure and various other semantics of conforming instance documents. For example, the following XML schema definition, po.xsd, describes the structure and other properties of purchase order XML documents.

This manual refers to an XML schema definition as an XML schema.

Example 5-1 XML Schema Definition, po.xsd

The following is an example of an XML schema definition, po.xsd:

<schema targetNamespace="http://www.oracle.com/PO.xsd" 
xmlns:po="http://www.oracle.com/PO.xsd" 
xmlns="http://www.w3.org/2001/XMLSchema">
 <complexType name="PurchaseOrderType">
  <sequence>
   <element name="PONum" type="decimal"/>
   <element name="Company">
    <simpleType>
     <restriction base="string">
      <maxLength value="100"/>
     </restriction>
    </simpleType>
   </element>
   <element name="Item" maxOccurs="1000">
    <complexType>
     <sequence>
      <element name="Part">
       <simpleType>
        <restriction base="string">
         <maxLength value="1000"/>
        </restriction>
       </simpleType>
      </element>
      <element name="Price" type="float"/>
     </sequence>
    </complexType>
   </element>
  </sequence>
 </complexType>
 <element name="PurchaseOrder" type="po:PurchaseOrderType"/>
</schema>

Example 5-2 XML Document, po.xml Conforming to XML Schema, po.xsd

The following is an example of an XML document that conforms to XML schema po.xsd:

<PurchaseOrder xmlns="http://www.oracle.com/PO.xsd" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
 xsi:schemaLocation="http://www.oracle.com/PO.xsd  
http://www.oracle.com/PO.xsd"> 
     <PONum>1001</PONum> 
     <Company>Oracle Corp</Company> 
     <Item> 
       <Part>9i Doc Set</Part> 
       <Price>2550</Price> 
     </Item> 
</PurchaseOrder>

Using Oracle XML DB and XML Schema

Oracle XML DB uses annotated XML schema as metadata, that is, the standard XML Schema definitions along with several Oracle XML DB-defined attributes. These attributes are in a different namespace and control how instance documents get mapped into the database. Since these attributes are in a different namespace from the XML schema namespace, such annotated XML schemas are still legal XML schema documents:

When using Oracle XML DB, you must first register your XML schema. You can then use the XML schema URLs while creating XMLType tables, columns, and views.

Oracle XML DB provides XML Schema support for the following tasks:

• Registering any W3C-compliant XML schemas.
• Validating your XML documents against a registered XML schema definitions.
• Registering local and global XML schemas.
• Generating XML schema from object types.
• Referencing an XML schema owned by another user.
• Explicitly referencing a global XML schema when a local XML schema exists with the same name.
• Generating a structured database mapping from your XML schemas during XML schema registration. This includes generating SQL object types, collection types, and default tables, and capturing the mapping information using XML schema attributes.
• Specifying a particular SQL type mapping when there are multiple legal mappings.
• Creating XMLType tables, views and columns based on registered XML schemas.
• Performing manipulation (DML) and queries on XML schema-based XMLType tables.
• Automatically inserting data into default tables when schema-based XML instances are inserted into Oracle XML DB Repository using FTP, HTTP/WebDav protocols and other languages.

  See Also: Chapter 26, "Oracle XML DB Basic Demo"

Why Do We Need XML Schema?

As described in Chapter 4, "Using XMLType", XMLType is a datatype that facilitates storing XML in columns and tables in the database. XML schemas further facilitate storing XML columns and tables in the database, and they offer you more storage and access options for XML data along with space- performance-saving options.

For example, you can use XML schema to declare which elements and attributes can be used and what kinds of element nesting, and datatypes are allowed in the XML documents being stored or processed.

XML Schema Provides Flexible XML-to-SQL Mapping Setup

Using XML schema with Oracle XML DB provides a flexible setup for XML storage mapping. For example:

• If your data is highly structured (mostly XML), each element in the XML documents can be stored as a column in a table.
• If your data is unstructured (all or mostly non-XML data), the data can be stored in a Character Large Object (CLOB).

Which storage method you choose depends on how your data will be used and depends on the queriability and your requirements for querying and updating your data. In other words. Using XML schema gives you more flexibility for storing highly structured or unstructured data.

XML Schema Allows XML Instance Validation

Another advantage of using XML schema with Oracle XML DB is that you can perform XML instance validation according to the XML schema and with respect to Oracle XML Repository requirements for optimal performance. For example, an XML schema can check that all incoming XML documents comply with definitions declared in the XML schema, such as allowed structure, type, number of allowed item occurrences, or allowed length of items.

Also, by registering XML schema in Oracle XML DB, when inserting and storing XML instances using Protocols, such as FTP or HTTP, the XML schema information can influence how efficiently XML instances are inserted.

When XML instances must be handled without any prior information about them, XML schema can be useful in predicting optimum storage, fidelity, and access.

DTD Support in Oracle XML DB

In addition to supporting XML schema that provide a structured mapping to object- relational storage, Oracle XML DB also supports DTD specifications in XML instance documents. Though DTDs are not used to derive the mapping, XML processors can still access and interpret the DTDs.

Inline DTD Definitions

When an XML instance document has an inline DTD definition, it is used during document parsing. Any DTD validations and entity declaration handling is done at this point. However, once parsed, the entity references are replaced with actual values and the original entity reference is lost.

External DTD Definitions

Oracle XML DB also supports external DTD definitions if they are stored in the Repository. Applications needing to process an XML document containing an external DTD definition such as "/public/flights.dtd", must first ensure that the DTD document is stored in Oracle XML DB at the path "/public/flights.xsd".

Introducing DBMS_XMLSCHEMA

Oracle XML DB's XML schema functionality is available through the PL/SQL supplied package, DBMS_XMLSCHEMA, a server-side component that handles the registration of XML schema definitions for use by Oracle XML DB applications.

Two of the main DBMS_XMLSCHEMA functions are:

• registerSchema(). This registers an XML schema given:
  • XML schema source, which can be in a variety of formats, including string, LOB, XMLType, and URIType
  • Its schema URL or XMLSchema name
• deleteSchema(). This deletes a previously registered XML schema, identified by its URL or XMLSchema name.

Registering Your XML Schema Before Using Oracle XML DB

An XML schema must be registered before it can be used or referenced in any context by Oracle XML DB. XML schema are registered by using DBMS_XMLSCHEMA.registerSchema() and specifying the following:

• The XML schema source document as a VARCHAR, CLOB, XMLType, or URIType.
• The XML schema URL. This is a name for the XML schema that is used within XML instance documents to specify the location of the XML schema to which they conform.

After registration has completed:

• XML documents conforming to this XML schema, and referencing it using the XML schema's URL within the XML document, can be processed by Oracle XML DB.
• Tables and columns can be created for root XML elements defined by this XML schema to store the conforming XML documents.

Registering Your XML Schema Using DBMS_XMLSCHEMA

Use DBMS_XMLSCHEMA to register your XML schema. This involves specifying the XML schema document and its URL, also known as the XML schema location.

Example 5-3 Registering an XML Schema That Declares a complexType Using DBMS_XMLSCHEMA

Consider the following XML schema. It declares a complexType called PurchaseOrderType and an element PurchaseOrder of this type. The schema is stored in the PL/SQL variable doc. The following registers the XML schema at URL: http://www.oracle.com/PO.xsd:

declare
        doc varchar2(1000) := '<schema 
targetNamespace="http://www.oracle.com/PO.xsd" 
xmlns:po="http://www.oracle.com/PO.xsd" 
xmlns="http://www.w3.org/2001/XMLSchema">
 <complexType name="PurchaseOrderType">
  <sequence>
   <element name="PONum" type="decimal"/>
   <element name="Company">
    <simpleType>
     <restriction base="string">
      <maxLength value="100"/>
     </restriction>
    </simpleType>
   </element>
   <element name="Item" maxOccurs="1000">
    <complexType>
     <sequence>
      <element name="Part">
       <simpleType>
        <restriction base="string">
         <maxLength value="1000"/>
        </restriction>
       </simpleType>
      </element>
      <element name="Price" type="float"/>
     </sequence>
    </complexType>
   </element>
  </sequence>
 </complexType>
 <element name="PurchaseOrder" type="po:PurchaseOrderType"/>
</schema>';
begin
        dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc);
end;

The registered schema can be used to created XML schema-Based tables, or XML schema-based columns. For example, the following statement creates an a table with an XML schema-based column.

create table po_tab(
 id number,
 po sys.XMLType
) 
  xmltype column po 
    XMLSCHEMA "http://www.oracle.com/PO.xsd"
    element "PurchaseOrder";

The following shows an XMLType instance that conforms to the preceding XML schema being inserted into the preceding table. The schemaLocation attribute specifies the schema URL:

insert into po_tab values (1, 
  xmltype('<po:PurchaseOrder xmlns:po="http://www.oracle.com/PO.xsd" 
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
   xsi:schemaLocation="http://www.oracle.com/PO.xsd  
  http://www.oracle.com/PO.xsd"> 
     <PONum>1001</PONum> 
       <Company>Oracle Corp</Company> 
     <Item> 
      <Part>9i Doc Set</Part> 
       <Price>2550</Price> 
     </Item> 
     <Item> 
       <Part>8i Doc Set</Part> 
       <Price>350</Price> 
     </Item> 
 </po:PurchaseOrder>')); 

Local and Global XML Schemas

XML schemas can be registered as local or global:

• Local XML schema: An XML schema registered as a local schema is, by default, visible only to the owner.
• Global XML schema: An XML schema registered as a global schema is, by default, visible and usable by all database users.

When you register an XML schema, DBMS_XMLSCHEMA adds an Oracle XML DB resource corresponding to the XML schema into the Oracle XML DB Repository. The XML schema URL determines the path name of the resource in Oracle XML DB Repository according to the following rules:

Local XML Schema

In Oracle XML DB, local XML schema resources are created under the /sys/schemas/<username> directory. The rest of the path name is derived from the schema URL.

Example 5-4 A Local XML Schema

For example, a local XML schema with schema URL:

http://www.myco.com/PO.xsd

registered by SCOTT, is given the path name:

/sys/schemas/SCOTT/www.myco.com/PO.xsd.

Database users need appropriate permissions (ACLs) to create a resource with this path name in order to register the XML schema as a local XML schema.

By default, an XML schema belongs to you after registering the XML schema with Oracle XML DB. A reference to the XML schema document is stored in Oracle XML DB Repository, in directory:

/sys/schemas/<username>/.... 

For example, if you, SCOTT, registered the preceding XML schema, it is mapped to the file:

/sys/schemas/SCOTT/www.oracle.com/PO.xsd

Such XML schemas are referred to as local. In general, they are usable only by you to whom they belong.

Global XML Schema

In contrast to local schema, privileged users can register an XML schema as a global XML schema by specifying an argument in the DBMS_XMLSCHEMA registration function.

Global schemas are visible to all users and stored under the /sys/schemas/PUBLIC/ directory in Oracle XML DB Repository.

You can register a local schema with the same URL as an existing global schema. A local schema always hides any global schema with the same name (URL).

Example 5-5 A Global XML Schema

For example, a global schema registered by SCOTT with the URL:

www.myco.com/PO.xsd

is mapped to Oracle XML DB Repository at:

/sys/schemas/PUBLIC/www.myco.com/PO.xsd

Database users need appropriate permissions (ACLs) to create this resource in order to register the XML schema as global.

Registering Your XML Schema: Oracle XML DB Sets Up the Storage and Access Infrastructure

As part of registering an XML schema, Oracle XML DB also performs several other steps to facilitate storing, accessing, and manipulating XML instances that conform to the XML schema. These steps include:

• Creating types: When an XML schema is registered, Oracle creates the appropriate SQL object types that enable the structured storage of XML documents that conform to this XML schema. You can use Oracle XML DB-defined attributes in XML schema documents to control how these object types are generated.
• Creating default tables: As part of XML schema registration, Oracle XML DB generates default XMLType tables for all root elements. You can also specify any column and table level constraints for use during table creation.

  See Also: "Specifying SQL Object Type Names with SQLName, SQLType Attributes" Chapter 3, "Using Oracle XML DB"

Deleting Your XML Schema Using DBMS_XMLSCHEMA

You can delete your registered XML schema by using the DBMS_XMLSCHEMA.deleteSchema procedure. When you attempt to delete an XML schema, DBMS_XMLSCHEMA checks:

• That the current user has the appropriate privileges (ACLs) to delete the resource corresponding to the XML schema within Oracle XML DB Repository. You can thus control which users can delete which XML schemas by setting the appropriate ACLs on the XML schema resources.
• For dependents. If there are any dependents, it raises an error and the deletion operation fails. This is referred to as the RESTRICT mode of deleting XML schemas.

FORCE Mode

A FORCE mode option is provided while deleting XML schemas. If you specify the FORCE mode option, the XML schema deletion proceeds even if it fails the dependency check. In this mode, XML schema deletion marks all its dependents as invalid.

CASCADE Mode

The CASCADE mode option drops all generated types and default tables as part of a previous call to register XML schema.

Example 5-6 Deleting the XML Schema Using DBMS_XMLSCHEMA

The following example deletes XML schema PO.xsd. First, the dependent table po_tab is dropped. Then, the schema is deleted using the FORCE and CASCADE modes with DBMS_XMLSCHEMA.DELETESCHEMA:

drop table po_tab;

EXEC dbms_xmlschema.deleteSchema('http://www.oracle.com/PO.xsd',
            dbms_xmlschema.DELETE_CASCADE_FORCE);

Guidelines for Using Registered XML Schemas

The following sections describe guidelines for registering XML schema with Oracle XML DB.

Objects That Depend on Registered XML Schemas

The following objects depend on a registered XML schemas:

• Tables or views that have an XMLType column that conforms to some element in the XML schema.
• XML schemas that include or import this schema as part of their definition.
• Cursors that reference the XML schema name, for example, within DBMS_XMLGEN operators. Note that these are purely transient objects.

Creating XMLType Tables, Views, or Columns

After an XML schema has been registered, it can be used to create XML schema-based XMLType tables, views, and columns by referencing the following:

• The XML schema URL of a registered XML schema
• The name of the root element

Example 5-7 Post-Registration Creation of an XMLType Table

For example you can create an XML schema-based XMLType table as follows:

CREATE TABLE po_tab OF XMLTYPE 
      XMLSCHEMA "http://www.oracle.com/PO.xsd" ELEMENT "PurchaseOrder";

The following statement inserts XML schema-conformant data:

insert into po_tab values (
  xmltype('<PurchaseOrder xmlns="http://www.oracle.com/PO.xsd" 
 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"  
 xsi:schemaLocation="http://www.oracle.com/PO.xsd  
http://www.oracle.com/PO.xsd"> 
     <PONum>1001</PONum> 
     <Company>Oracle Corp</Company> 
     <Item> 
       <Part>9i Doc Set</Part> 
       <Price>2550</Price> 
     </Item> 
     <Item> 
       <Part>8i Doc Set</Part> 
       <Price>350</Price> 
     </Item> 
</PurchaseOrder>'));

Validating XML Instances Against the XML Schema: schemaValidate()

You can validate an XMLType instance against a registered XML schema by using one of the validation methods.

Example 5-8 Validating XML Using schemaValidate()

The following PL/SQL example validates an XML instance against XML schema PO.xsd:

declare 
  xmldoc xmltype; 
  begin 
  
  -- populate xmldoc (by fetching from table) 
  select value(p) into xmldoc from po_tab p;

  --   validate against XML schema 
  xmldoc.schemavalidate();

  if xmldoc.isschemavalidated() = 1 then  
                      dbms_output.put_line('Data is valid');
  else  
                      dbms_output.put_line('Data is invalid');        
  end if; 
end;

Fully Qualified XML Schema URLs

By default, XML schema URL names are always referenced within the scope of the current user. In other words, when database users specify XML Schema URLs, they are first resolved as the names of local XML schemas owned by the current user.

• If there are no such XML schemas, then they are resolved as names of global XML schemas.
• If there are no global XML schemas, then Oracle XML DB raises an error.

XML Schema That Users Cannot Reference

These rules imply that, by default, users cannot reference the following kinds of XML schemas:

• XML schemas owned by a different database user
• Global XML schemas that have the same name as local XML schemas

Fully Qualified XML Schema URLs Permit Explicit Reference to XML Schema URLs

To permit explicit reference to XML schemas in these cases, Oracle XML DB supports a notion of fully qualified XML schema URLs. In this form, the name of the database user owning the XML schema is also specified as part of the XML schema URL, except that such XML schema URLs belong to the Oracle XML DB namespace as follows: http://xmlns.oracle.com/xdb/schemas/<database-user-name>/<schemaURL-minus-protocol>

Example 5-9 Using Fully Qualified XML Schema URL

For example, consider the global XML schema with the following URL: http://www.example.com/po.xsd

Assume that database user SCOTT has a local XML schema with the same URL:

http://www.example.com/po.xsd

User JOE can reference the local XML schema owned by SCOTT as follows:

http://xmlns.oracle.com/xdb/schemas/SCOTT/www.example.com/po.xsd

Similarly, the fully qualified URL for the global XML schema is:

http://xmlns.oracle.com/xdb/schemas/PUBLIC/www.example.com/po.xsd   

Transactional Behavior of XML Schema Registration

Registration of an XML schema is non transactional and auto committed as with other SQL DDL operations, as follows:

• If registration succeeds, the operation is auto committed.
• If registration fails, the database is rolled back to the state before the registration began.

Since XML schema registration potentially involves creating object types and tables, error recovery involves dropping any such created types and tables. Thus, the entire XML schema registration is guaranteed to be atomic. That is, either it succeeds or the database is restored to the state before the start of registration.

Generating XML Schema Using DBMS_XMLSCHEMA.generateSchema()

An XML schema can be generated from an object-relational type automatically using a default mapping. The generateSchema() and generateSchemas() functions in the DBMS_XMLSCHEMA package take in a string that has the object type name and another that has the Oracle XML DB XML schema.

• generateSchema() returns an XMLType containing an XML schema. It can optionally generate XML schema for all types referenced by the given object type or restricted only to the top-level types.
• generateSchemas() is similar, except that it returns an XMLSequenceType of XML schemas, each corresponding to a different namespace. It also takes an additional optional argument, specifying the root URL of the preferred XML schema location:

  http://xmlns.oracle.com/xdb/schemas/<schema>.xsd
  
  

  They can also optionally generate annotated XML schemas that can be used to register the XML schema with Oracle XML DB.

Example 5-10 Generating XML Schema: Using generateSchema()

For example, given the object type:

connect t1/t1
CREATE TYPE employee_t AS OBJECT
(
   empno NUMBER(10),
   ename VARCHAR2(200),
   salary NUMBER(10,2)
);

You can generate the schema for this type as follows:

select  dbms_xmlschema.generateschema('T1', 'EMPLOYEE_T') from dual;

This returns a schema corresponding to the type EMPLOYEE_T. The schema declares an element named EMPLOYEE_T and a complexType called EMPLOYEE_TType. The schema includes other annotation from http://xmlns.oracle.com/xdb.

DBMS_XMLSCHEMA.GENERATESCHEMA('T1','EMPLOYEE_T')
--------------------------------------------------------------------------------
<xsd:schema targetNamespace="http://ns.oracle.com/xdb/T1" xmlns="http://ns.oracl
e.com/xdb/T1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xml
ns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:sch
emaLocation="http://xmlns.oracle.com/xdb http://xmlns.oracle.com/xdb/XDBSchema.x
sd">
  <xsd:element name="EMPLOYEE_T" type="EMPLOYEE_TType" xdb:SQLType="EMPLOYEE_T"
xdb:SQLSchema="T1"/>
  <xsd:complexType name="EMPLOYEE_TType">
    <xsd:sequence>
      <xsd:element name="EMPNO" type="xsd:double" xdb:SQLName="EMPNO" xdb:SQLTyp
e="NUMBER"/>
      <xsd:element name="ENAME" type="xsd:string" xdb:SQLName="ENAME" xdb:SQLTyp
e="VARCHAR2"/>
      <xsd:element name="SALARY" type="xsd:double" xdb:SQLName="SALARY" xdb:SQLT
ype="NUMBER"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

XML Schema-Related Methods of XMLType

Table 5-1 lists the XMLType API's XML schema-related methods.

Table 5-1 XMLType API XML Schema-Related Methods  

XMLType API Method	Description
isSchemaBased()	Returns TRUE if the XMLType instance is based on an XML schema, FALSE otherwise.
getSchemaURL() getRootElement() getNamespace()	Returns the XML schema URL, name of root element, and the namespace for an XML schema-based XMLType instance.
schemaValidate() isSchemaValid() is SchemaValidated() setSchemaValidated()	An XMLType instance can be validated against a registered XML schema using the validation methods. See Chapter 6, "Transforming and Validating XMLType Data".

Managing and Storing XML Schema

XML schema documents are themselves stored in Oracle XML DB as XMLType instances. XML schema-related XMLType types and tables are created as part of the Oracle XML DB installation script, catxdbs.sql.

Root XML Schema, XDBSchema.xsd

The XML schema for XML schemas is called the root XML schema, XDBSchema.xsd. XDBSchema.xsd describes any valid XML schema document that can be registered by Oracle XML DB. You can access XDBSchema.xsd through Oracle XML DB Repository at:

/sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBSchema.xsd

How Are XML Schema-Based XMLType Structures Stored?

XML Schema-based XMLType structures are stored in one of the following ways:

• In underlying object type columns. This is the default storage mechanism.
  • SQL object types can be created optionally during the XML schema registration process. See "Creating XMLType Tables and Columns Based on XML Schema".
  • See "Specifying SQL Object Type Names with SQLName, SQLType Attributes".
• In a single underlying LOB column. Here the storage choice is specified in the STORE AS clause of the CREATE TABLE statement:

  CREATE TABLE po_tab OF xmltype 
      STORE AS CLOB 
      ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder";
  

Design criteria for storing XML data are discussed inChapter 2, "Getting Started with Oracle XML DB" and Chapter 3, "Using Oracle XML DB".

Specifying the Storage Mechanism

Instead of using the STORE AS clause, you can specify that the table and column be stored according to a mapping based on a particular XML schema. You can specify the URL for the XML schema used for the mapping.

Non-schema-based XML data can be stored in tables using CLOBs. However you do not gain benefits such as indexing, query-rewrite, and so on.

DOM Fidelity

Document Object Model (DOM) fidelity is the concept of retaining the structure of a retrieved XML document, compared to the original XML document, for DOM traversals. DOM fidelity is needed to ensure the accuracy and integrity of XML documents stored in Oracle XML DB.

How Oracle XML DB Ensures DOM Fidelity with XML Schema

All elements and attributes declared in the XML schema are mapped to separate attributes in the corresponding SQL object type. However, some pieces of information in XML instance documents are not represented directly by these element or attributes, such as:

• Comments
• Namespace declarations
• Prefix information

To ensure the integrity and accuracy of this data, for example, when regenerating XML documents stored in the database, Oracle XML DB uses a data integrity mechanism called DOM fidelity.

DOM fidelity refers to how identical the returned XML documents are compared to the original XML documents, particularly for purposes of DOM traversals.

DOM Fidelity and SYS_XDBPD$

To guarantee that DOM fidelity is maintained and that the returned XML documents are identical to the original XML document for DOM traversals, Oracle XML DB adds a system binary attribute, SYS_XDBPD$, to each created object type.

This positional descriptor attribute stores all pieces of information that cannot be stored in any of the other attributes, thereby ensuring the DOM fidelity of all XML documents stored in Oracle XML DB. Examples of such pieces of information include: ordering information, comments, processing instructions, namespace prefixes, and so on. This is mapped to a Positional Descriptor (PD) column.

How to Suppress SYS_XDBPD$

If DOM fidelity is not required, you can suppress SYS_XDBPD$ in the XML schema definition by setting the attribute, maintainDOM=FALSE.

Creating XMLType Tables and Columns Based on XML Schema

Oracle XML DB creates XML schema-based XMLType tables and columns by referencing:

• The XML schema URL of a registered XML schema
• The name of the root element

Figure 5-1 shows the syntax for creating an XMLType table:

CREATE TABLE [schema.] table OF XMLTYPE
  [XMLTYPE XMLType_storage] [XMLSchema_spec];

Figure 5-1 Creating an XMLType Table

Text description of the illustration XMLType_table.gif

A subset of the XPointer notation, shown in the following example, can also be used to provide a single URL containing the XML schema location and element name.

Example 5-11 Creating XML Schema-Based XMLType Table

This example creates the XMLType table po_tab using the XML schema at the given URL:

CREATE TABLE po_tab OF XMLTYPE 
    XMLSCHEMA "http://www.oracle.com/PO.xsd" ELEMENT "PurchaseOrder";

An equivalent definition is:

CREATE TABLE po_tab OF XMLTYPE
    ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder";

SQL Object-Relational Types Store XML Schema-Based XMLType Tables

When an XML schema is registered, Oracle XML DB creates the appropriate SQL object types that enable structured storage of XML documents that conform to this XML schema. All SQL object types are created based on the current registered XML schema, by default.

Example 5-12 Creating SQL Object Types to Store XMLType Tables

or example, when PO.xsd is registered with Oracle XML DB, the following SQL types are created.

 CREATE TYPE "Itemxxx_T" as object 
(    
    part varchar2(1000),
    price number
);

CREATE TYPE "Itemxxx_COLL" AS varray(1000) OF "Item_T";
CREATE TYPE "PurchaseOrderTypexxx_T" AS OBJECT
(
 ponum number,
 company varchar2(100),
 item Item_varray_COLL
); 

Specifying SQL Object Type Names with SQLName, SQLType Attributes

To specify specific names of SQL objects generated include the attributes SQLName and SQLType in the XML schema definition prior to registering the XML schema.

• If you specify the SQLName and SQLType values, Oracle XML DB creates the SQL object types using these names.
• If you do not specify these attributes, Oracle XML DB uses system-generated names.

  Note: You do not have to specify values for any of these attributes. Oracle XML DB fills in appropriate values during the XML schema registration process. However, it is recommended that you specify the names of at least the top-level SQL types so that you can reference them later.

All annotations are in the form of attributes that can be specified within attribute and element declarations. These attributes belong to the Oracle XML DB namespace: http://xmlns.oracle.com/xdb

Table 5-2 lists Oracle XML DB attributes that you can specify in element and attribute declarations.

Table 5-2 Attributes You Can Specify in Elements 

Attribute	Values	Default	Description
SQLName	Any SQL identifier	Element name	Specifies the name of the attribute within the SQL object that maps to this XML element.
SQLType	Any SQL type name	Name generated from element name	Specifies the name of the SQL type corresponding to this XML element declaration.
SQLCollType	Any SQL collection type name	Name generated from element name	Specifies the name of the SQL collection type corresponding to this XML element that has maxOccurs > 1.
SQLSchema	Any SQL username	User registering XML schema	Name of database user owning the type specified by SQLType.
SQLCollSchema	Any SQL username	User registering XML schema	Name of database user owning the type specified by SQLCollType.
maintainOrder	true | false	true	If true, the collection is mapped to a VARRAY. If false, the collection is mapped to a NESTED TABLE.
SQLInline	true | false	true	If true this element is stored inline as an embedded attribute (or a collection if maxOccurs > 1). If false, a REF (or collection of REFs if maxOccurs > 1) is stored. This attribute will be forced to false in certain situations (like cyclic references) where SQL will not support inlining.
maintainDOM	true | false	true	If true, instances of this element are stored such that they retain DOM fidelity on output. This implies that all comments, processing instructions, namespace declarations, and so on are retained in addition to the ordering of elements. If false, the output need not be guaranteed to have the same DOM behavior as the input.
columnProps	Any valid column storage clause	NULL	Specifies the column storage clause that is inserted into the default CREATE TABLE statement. It is useful mainly for elements that get mapped to tables, namely top-level element declarations and out-of-line element declarations.
tableProps	Any valid table storage clause	NULL	Specifies the TABLE storage clause that is appended to the default CREATE TABLE statement. This is meaningful mainly for global and out-of-line elements.
defaultTable	Any table name	Based on element name.	Specifies the name of the table into which XML instances of this schema should be stored. This is most useful in cases when the XML is being inserted from APIs where table name is not specified, for example, FTP and HTTP.
beanClassname	Any Java class name	Generated from element name.	Can be used within element declarations. If the element is based on a global complexType, this name must be identical to the beanClassname value within the complexType declaration. If a name is specified by the user, the bean generation will generate a bean class with this name instead of generating a name from the element name.
JavaClassname	Any Java class name	None	Used to specify the name of a Java class that is derived from the corresponding bean class to ensure that an object of this class is instantiated during bean access. If a JavaClassname is not specified, Oracle XML DB will instantiate an object of the bean class directly.

Table 5-3 Attributes You Can Specify in Elements Declaring Global complexTypes    

Attribute	Values	Default	Description
SQLType	Any SQL type name	Name generated from element name	Specifies the name of the SQL type corresponding to this XML element declaration.
SQLSchema	Any SQL username	User registering XML schema	Name of database user owning the type specified by SQLType.
beanClassname	Any Java class name	Generated from element name.	Can be used within element declarations. If the element is based on a global complexType, this name must be identical to the beanClassname value within the complexType declaration. If a name is specified by the user, the bean generation will generate a bean class with this name, instead of generating a name from the element name.
maintainDOM	true | false	true	If true, instances of this element are stored such that they retain DOM fidelity on output. This implies that all comments, processing instructions, namespace declarations,.and so on, are retained in addition to the ordering of elements. If false, the output need not be guaranteed to have the same DOM behavior as the input.

Table 5-4 Attributes You Can Specify in XML Schema Declarations  

Attribute	Values	Default	Description
mapUnboundedStringToLob	true | false	false	If true, unbounded strings are mapped to CLOB by default. Similarly, unbounded binary data gets mapped to BLOB, by default. If false, unbounded strings are mapped to VARCHAR2(4000) and unbounded binary components are mapped to RAW(2000).
storeVarrayAsTable	true | false	false	If true, the VARRAY is stored as a table (OCT). If false, the VARRAY is stored in a LOB.

SQL Mapping Is Specified in the XML Schema During Registration

Information regarding the SQL mapping is stored in the XML schema document. The registration process generates the SQL types, as described in "Mapping of Types Using DBMS_XMLSCHEMA" and adds annotations to the XML schema document to store the mapping information. Annotations are in the form of new attributes.

Example 5-13 Capturing SQL Mapping Using SQLType and SQLName Attributes

The following XML schema definition shows how SQL mapping information is captured using SQLType and SQLName attributes:

declare
     doc varchar2(3000) := '<schema 
targetNamespace="http://www.oracle.com/PO.xsd" 
xmlns:po="http://www.oracle.com/PO.xsd" xmlns:xdb="http://xmlns.oracle.com/xdb" 
xmlns="http://www.w3.org/2001/XMLSchema">
 <complexType name="PurchaseOrderType">
  <sequence>
   <element name="PONum" type="decimal" xdb:SQLName="PONUM" 
xdb:SQLType="NUMBER"/>
   <element name="Company" xdb:SQLName="COMPANY" xdb:SQLType="VARCHAR2">
    <simpleType>
     <restriction base="string">
      <maxLength value="100"/>
     </restriction>
    </simpleType>
   </element>
   <element name="Item"  xdb:SQLName="ITEM" xdb:SQLType="ITEM_T" 
maxOccurs="1000">
    <complexType>
     <sequence>
      <element name="Part"  xdb:SQLName="PART" xdb:SQLType="VARCHAR2">
       <simpleType>
        <restriction base="string">
         <maxLength value="1000"/>
        </restriction>
       </simpleType>
      </element>
      <element name="Price" type="float"  xdb:SQLName="PRICE" 
xdb:SQLType="NUMBER"/>
     </sequence>
    </complexType>
   </element>
  </sequence>
 </complexType>
 <element name="PurchaseOrder" type="po:PurchaseOrderType"/>
</schema>';
begin
   dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc);
end;

Figure 5-2 shows how Oracle XML DB creates XML schema-based XMLType tables using an XML document and mapping specified in an XML schema. An XMLType table is first created and depending on how the storage is specified in the XML schema, the XML document is mapped and stored either as a CLOB in one XMLType column, or stored object-relationally and spread out across several columns in the table.

Figure 5-2 How Oracle XML DB Maps XML Schema-Based XMLType Tables

Text description of the illustration adxdb025.gif

An XMLType table is first created and depending on how the storage is specified in the XML schema, the XML document is mapped and stored either as a CLOB in one XMLType column, or stored object-relationally and spread out across several columns in the table.

Mapping of Types Using DBMS_XMLSCHEMA

Use DBMS_XMLSCHEMA to set the mapping of type information for attributes and elements.

Setting Attribute Mapping Type Information

An attribute declaration can have its type specified in terms of one of the following:

• Primitive type
• Global simpleType, declared within this XML schema or in an external XML schema
• Reference to global attribute (ref=".."), declared within this XML schema or in an external XML schema
• Local simpleType

In all cases, the SQL type and associated information (length and precision) as well as the memory mapping information, are derived from the simpleType on which the attribute is based.

Overriding SQL Types

You can explicitly specify an SQLType value in the input XML schema document. In this case, your specified type is validated. This allows for the following specific forms of overrides:

• If the default type is a STRING, you can override it with any of the following: CHAR, VARCHAR, or CLOB.
• If the default type is RAW, you can override it with RAW or BLOB.

Setting Element Mapping Type Information

An element declaration can specify its type in terms of one of the following:

• Any of the ways for specifying type for an attribute declaration. See "Setting Attribute Mapping Type Information" .
• Global complexType, specified within this XML schema document or in an external XML schema.
• Reference to a global element (ref="..."), which could itself be within this XML schema document or in an external XML schema.
• Local complexType.

Overriding SQL Type

An element based on a complexType is, by default, mapped to an object type containing attributes corresponding to each of the sub-elements and attributes. However, you can override this mapping by explicitly specifying a value for SQLType attribute in the input XML schema. The following values for SQLType are permitted in this case:

• VARCHAR2
• RAW
• CLOB
• BLOB

These represent storage of the XML in a text or unexploded form in the database. The following special cases are handled:

• If a cycle is detected, as part of processing the complexTypes used to declare elements and elements declared within the complexType), the SQLInline attribute is forced to be "false" and the correct SQL mapping is set to REF XMLTYPE.
• If maxOccurs > 1, a VARRAY type may need to be created.
  • If SQLInline ="true", a varray type is created whose element type is the SQL type previously determined.
    • Cardinality of the VARRAY is determined based on the value of maxOccurs attribute.
    • The name of the VARRAY type is either explicitly specified by the user using SQLCollType attribute or obtained by mangling the element name.
  • If SQLInline="false", the SQL type is set to XDB.XDB$XMLTYPE_REF_LIST_T, a predefined type representing an array of REFs to XMLType.
• If the element is a global element, or if SQLInline="false", a default table needs to be created. It is added to the table creation context. The name of the default table has either been specified by the user, or derived by mangling the element name.

XML Schema: Mapping SimpleTypes to SQL

This section describes how XML schema definitions map XML schema simpleType to SQL object types. Figure 5-3 shows an example of this.

Table 5-5 through Table 5-8 list the default mapping of XML schema simpleType to SQL, as specified in the XML schema definition. For example:

• An XML primitive type is mapped to the closest SQL datatype. For example, DECIMAL, POSITIVEINTEGER, and FLOAT are all mapped to SQL NUMBER.
• An XML enumeration type is mapped to an object type with a single RAW(n) attribute. The value of n is determined by the number of possible values in the enumeration declaration.
• An XML list or a union datatype is mapped to a string (VARCHAR2/CLOB) datatype in SQL.

Figure 5-3 Mapping simpleType: XML Strings to SQL VARCHAR2 or CLOBs

Text description of the illustration adxdb038.gif

Table 5-5 Mapping XML String Datatypes to SQL  

XML Primitive Type	Length or MaxLength Facet	Default Mapping	Compatible Datatype
string	n	VARCHAR2(n) if n < 4000, else VARCHAR2(4000)	CHAR, VARCHAR2, CLOB
string	--	VARCHAR2(4000) if mapUnboundedStringToLob="false", CLOB	CHAR, VARCHAR2, CLOB

Table 5-6 Mapping XML Binary Datatypes (hexBinary/base64Binary) to SQL  

XML Primitive Type	Length or MaxLength Facet	Default Mapping	Compatible Datatype
hexBinary, base64Binary	n	RAW(n) if n < 2000, else RAW(2000)	RAW, BLOB
hexBinary, base64Binary	-	RAW(2000) if mapUnboundedStringToLob="false", BLOB	RAW, BLOB

Table 5-7 Default Mapping of Numeric XML Primitive Types to SQL 

XML Simple Type	Default Oracle DataType	totalDigits (m), fractionDigits(n) Specified	Compatible Datatypes
float	NUMBER	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
double	NUMBER	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
decimal	NUMBER	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
integer	NUMBER	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
nonNegativeInteger	NUMBER	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
positiveInteger	NUMBER	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
nonPositiveInteger	NUMBER	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
negativeInteger	NUMBER	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
long	NUMBER(20)	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
unsignedLong	NUMBER(20)	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
int	NUMBER(10)	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
unsignedInt	NUMBER(10)	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
short	NUMBER(5)	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
unsignedShort	NUMBER(5)	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
byte	NUMBER(3)	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE
unsignedByte	NUMBER(3)	NUMBER(m,n)	NUMBER, FLOAT, DOUBLE

Table 5-8 Mapping XML Date Datatypes to SQL  

XML Primitive Type	Default Mapping	Compatible Datatypes
datetime	TIMESTAMP	DATE
time	TIMESTAMP	DATE
date	DATE	DATE
gDay	DATE	DATE
gMonth	DATE	DATE
gYear	DATE	DATE
gYearMonth	DATE	DATE
gMonthDay	DATE	DATE
duration	VARCHAR2(4000)	none

Table 5-9 Default Mapping of Other XML Primitive Datatypes to SQL    

XML Simple Type	Default Oracle DataType	Compatible Datatypes
boolean	RAW(1)	VARCHAR2
Language(string)	VARCHAR2(4000)	CLOB, CHAR
NMTOKEN(string)	VARCHAR2(4000)	CLOB, CHAR
NMTOKENS(string)	VARCHAR2(4000)	CLOB, CHAR
Name(string)	VARCHAR2(4000)	CLOB, CHAR
NCName(string)	VARCHAR2(4000)	CLOB, CHAR
ID	VARCHAR2(4000)	CLOB, CHAR
IDREF	VARCHAR2(4000)	CLOB, CHAR
IDREFS	VARCHAR2(4000)	CLOB, CHAR
ENTITY	VARCHAR2(4000)	CLOB, CHAR
ENTITIES	VARCHAR2(4000)	CLOB, CHAR
NOTATION	VARCHAR2(4000)	CLOB, CHAR
anyURI	VARCHAR2(4000)	CLOB, CHAR
anyType	VARCHAR2(4000)	CLOB, CHAR
anySimpleType	VARCHAR2(4000)	CLOB, CHAR
QName	XDB.XDB$QNAME	--

simpleType: Mapping XML Strings to SQL VARCHAR2 Versus CLOBs

If the XML schema specifies the datatype to be string with a maxLength value of less than 4000, it is mapped to a VARCHAR2 attribute of the specified length. However, if maxLength is not specified in the XML schema, it can only be mapped to a LOB. This is sub-optimal when most of the string values are small and only a small fraction of them are large enough to need a LOB.

XML Schema: Mapping complexTypes to SQL

Using XML schema, a complexType is mapped to an SQL object type as follows:

• XML attributes declared within the complexType are mapped to object attributes. The simpleType defining the XML attribute determines the SQL datatype of the corresponding attribute.
• XML elements declared within the complexType are also mapped to object attributes. The datatype of the object attribute is determined by the simpleType or complexType defining the XML element.

If the XML element is declared with attribute maxOccurs > 1, it is mapped to a collection attribute in SQL. The collection could be a VARRAY (default) or nested table if the maintainOrder attribute is set to false. Further, the default storage of the VARRAY is in Ordered Collections in Tables (OCTs) instead of LOBs. You can choose LOB storage by setting the storeAsLob attribute to true.

Setting the SQLInLine Attribute to FALSE for Out-of-Line Storage

By default, a sub-element is mapped to an embedded object attribute. However, there may be scenarios where out-of-line storage offers better performance. In such cases the SQLInline attribute can be set to false, and Oracle XML DB generates an object type with an embedded REF attribute. REF points to another instance of XMLType that corresponds to the XML fragment that gets stored out-of-line. Default XMLType tables are also created to store the out-of-line fragments.

Figure 5-4 illustrates the mapping of a complexType to SQL for out-of-line storage.

Figure 5-4 Mapping complexType to SQL for Out-of-Line Storage

Text description of the illustration adxdb010.gif

Example 5-14 Oracle XML DB XML Schema: complexType Mapping - Setting SQLInLine Attribute to False for Out-of-Line Storage

In this example element Addr's attribute, xdb:SQLInLine, is set to false.The resulting object type OBJ_T2 has a column of type XMLType with an embedded REF attribute. The REF attribute points to another XMLType instance created of object type OBJ_T1 in table Addr_tab. Addr_tab has columns Street and City. The latter XMLType instance is stored out-of-line.

declare
        doc varchar2(3000) := '<schema xmlns="http://www.w3.org/2001/XMLSchema"       
targetNamespace="http://www.oracle.com/emp.xsd"       
xmlns:emp="http://www.oracle.com/emp.xsd"       
xmlns:xdb="http://xmlns.oracle.com/xdb">
     <complexType name = "Employee" xdb:SQLType="OBJ_T2">
        <sequence>
           <element name = "Name" type = "string"/>
           <element name = "Age" type = "decimal"/>
           <element name = "Addr" xdb:SQLInline = "false">
              <complexType xdb:SQLType="OBJ_T1">