5 XML Schema Storage and Query: The Basics

Why We Need XML Schema

As described in Chapter 4, " XMLType Operations ", XMLType is a datatype that facilitates storing XMLType 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.

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.

Registering Your XML Schema

The main arguments to function registerSchema() are the following:

• schemaURL – the XML Schema URL. This is a unique identifier for the XML Schema within Oracle XML DB. Convention is that this identifier is in the form of a URL; however, this is not a requirement. The XML Schema URL is used with Oracle XML DB to identify instance documents, by making the schema location hint identical to the XML Schema URL. Oracle XML DB will never attempt to access the Web server identified by the specified URL.

• schemaDoc – the XML Schema source document. This is a VARCHAR, CLOB, BLOB, BFILE, XMLType, or URIType value.

• CSID – the character-set ID of the source-document encoding, when schemaDoc is a BFILE or BLOB value.

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
    SCHEMADOC => bfilename('XMLDIR','purchaseOrder.xsd'),
    CSID => nls_charset_id('AL32UTF8'));
END;
/

Storage and Access Infrastructure

As part of registering an XML Schema, Oracle XML DB also performs several tasks that 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 Database creates the appropriate SQL object types that enable the structured storage of XML documents that conform to this XML Schema. You can use the Schema annotations to control how these object types are named and generated. See "SQL Object Types" for details.

• Creating default tables: As part of XML Schema registration, Oracle XML DB generates default XMLType tables for all global elements. You can use schema annotations to control the names of the tables and to provide column-level and table-level storage clauses and constraints for use during table creation.

  
  

  See Also: "Creating Default Tables During XML Schema Registration" "Oracle XML Schema Annotations"

  
  

After registration has completed:

• XMLType tables and columns can be created that are constrained to the global elements defined by this XML Schema.

• XML documents conforming to the XML Schema, and referencing it using the XML Schema instance mechanism, can be processed automatically by Oracle XML DB.

Transactional Action 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, then the operation is auto committed.

• If registration fails, then the database is rolled back to the state before the registration began.

Because 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.

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.

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.

Debugging XML Schema Registration

You can monitor the object types and tables created during XML Schema registration by setting the following event before calling DBMS_XMLSCHEMA.registerSchema():

ALTER SESSION SET events='31098 trace name context forever'

Setting this event causes the generation of a log of all the CREATE TYPE and CREATE TABLE statements. The log is written to the user session trace file, typically found in <ORACLE_HOME>/admin/<ORACLE_SID>/udump. This script can be a useful aid in diagnosing problems during XML Schema registration.

SQL Object Types

Assuming that the parameter GENTYPES is set to TRUE 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. By default, all SQL object types are created in the database schema of the user who registers the XML Schema. If the defaultSchema annotation is used, then Oracle XML DB attempts to create the object type using the specified database schema. The current user must have the necessary privileges to perform this.

SQL> DESCRIBE "PurchaseOrderType1668_T"
  
"PurchaseOrderType1668_T" is NOT FINAL
Name                 Null?  Type
-------------------- ------ -------------------------------
SYS_XDBPD$                  XDB.XDB$RAW_LIST_T
Reference                   VARCHAR2(30 CHAR)
Actions                     ActionsType1661_T
Reject                      RejectionType1660_T
Requestor                   VARCHAR2(128 CHAR)
User                        VARCHAR2(10 CHAR)
CostCenter                  VARCHAR2(4 CHAR)
ShippingInstructions        ShippingInstructionsTyp1659_T
SpecialInstructions         VARCHAR2(2048 CHAR)
LineItems                   LineItemsType1666_T
Notes                       VARCHAR2(4000 CHAR)
 
SQL> DESCRIBE "LineItemsType1666_T"
 
"LineItemsType1666_T" is NOT FINAL
Name                 Null? Type
-------------------- ----- -------------------------------
SYS_XDBPD$                 XDB.XDB$RAW_LIST_T
LineItem                   LineItem1667_COLL
 
SQL> DESCRIBE "LineItem1667_COLL"

"LineItem1667_COLL" VARRAY(2147483647) OF LineItemType1665_T
"LineItemType1665_T" is NOT FINAL
Name                Null? Type
------------------- ----- --------------------------------
SYS_XDBPD$                XDB.XDB$RAW_LIST_T
ItemNumber                NUMBER(38)
Description               VARCHAR2(256 CHAR)
Part                      PartType1664_T

Creating Default Tables During XML Schema Registration

As part of XML Schema registration, you can also create default tables. Default tables are most useful when XML instance documents conforming to this XML Schema are inserted through APIs that do not have any table specification, such as with FTP or HTTP. In such cases, the XML instance is inserted into the default table.

If you have given a value for attribute defaultTable, then the XMLType table is created with that name. Otherwise it gets created with an internally generated name.

Further, any text specified using the tableProps and columnProps attribute are appended to the generated CREATE TABLE statement.

SQL> DESCRIBE "PurchaseOrder1669_TAB"

Name                        Null? Type
--------------------------- ----- -----------------------
TABLE of
  SYS.XMLTYPE(
    XMLSchema "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
    Element "PurchaseOrder")
  STORAGE Object-relational TYPE "PurchaseOrderType1668_T"

Generated Names are Case Sensitive

The names of SQL tables, object, and attributes generated by XML Schema registration are case sensitive. For instance, in Example 5-3, "Registering an XML Schema Using Package DBMS_XMLSCHEMA", a table called PurchaseOrder1669_TAB was created automatically during registration of the XML Schema. Since the table name was derived from the element name, PurchaseOrder, the name of the table is also mixed case. This means that you must refer to this table in SQL using a quoted identifier: "PurchaseOrder1669_TAB". Failure to do so results in an object-not-found error, such as ORA-00942: table or view does not exist.

Objects That Depend on Registered XML Schemas

The following objects are dependent on 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.

How to Obtain a List of Registered XML Schemas

To obtain a list of the XML Schemas registered with Oracle XML DB using DBMS_XMLSCHEMA.registerSchema use the following code. You can also use user_xml_schemas, all_xml_schemas, user_xml_tables, and all_xml_tables.

SQL> DESCRIBE DBA_XML_SCHEMAS

Name         Null? Type
------------ ----- -----------------------
OWNER              VARCHAR2(30)
SCHEMA_URL         VARCHAR2(700)
LOCAL              VARCHAR2(3)
SCHEMA             XMLTYPE(XMLSchema "http://xmlns.oracle.com/xdb/XDBSchema.xsd"
                           Element "schema")
INT_OBJNAME        VARCHAR2(4000)
QUAL_SCHEMA_URL    VARCHAR2(767)

SQL> SELECT owner, local, schema_url FROM dba_xml_schemas;

OWNER   LOC   SCHEMA_URL
-----   ---   ----------------------
XDB     NO    http://xmlns.oracle.com/xdb/XDBSchema.xsd
XDB     NO    http://xmlns.oracle.com/xdb/XDBResource.xsd
XDB     NO    http://xmlns.oracle.com/xdb/acl.xsd
XDB     NO    http://xmlns.oracle.com/xdb/dav.xsd
XDB     NO    http://xmlns.oracle.com/xdb/XDBStandard.xsd
XDB     NO    http://xmlns.oracle.com/xdb/log/xdblog.xsd
XDB     NO    http://xmlns.oracle.com/xdb/log/ftplog.xsd
XDB     NO    http://xmlns.oracle.com/xdb/log/httplog.xsd
XDB     NO    http://www.w3.org/2001/xml.xsd
XDB     NO    http://xmlns.oracle.com/xdb/XDBFolderListing.xsd
XDB     NO    http://xmlns.oracle.com/xdb/stats.xsd
XDB     NO    http://xmlns.oracle.com/xdb/xdbconfig.xsd
SCOTT   YES   http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd

13 rows selected.

SQL> DESCRIBE DBA_XML_TABLES

Name         Null? Type
------------ ----- -----------------------
OWNER              VARCHAR2(30)
TABLE_NAME         VARCHAR2(30)
XMLSCHEMA          VARCHAR2(700)
SCHEMA_OWNER       VARCHAR2(30)
ELEMENT_NAME       VARCHAR2(2000)
STORAGE_TYPE       VARCHAR2(17)

SQL> SELECT table_name FROM dba_xml_tables WHERE
       XMLSCHEMA =
         'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd';

TABLE_NAME
------------------------------
PurchaseOrder1669_TAB
 
1 row selected.

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, then it raises an error and the deletion operation fails. This is referred to as the RESTRICT mode of deleting XML Schemas.

BEGIN
  DBMS_XMLSCHEMA.deleteSchema(
    SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
    DELETE_OPTION => dbms_xmlschema.DELETE_CASCADE_FORCE);
END;
/

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. Such XML Schemas are referred to as local. In general, they are usable only by you, the owner.

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.

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
    SCHEMADOC => bfilename('XMLDIR','purchaseOrder.xsd'),
    LOCAL => TRUE,
    GENTYPES => TRUE, 
    GENTABLES => FALSE, 
    CSID => nls_charset_id('AL32UTF8'));
END;
/

/sys/schemas/SCOTT/xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd

Global XML Schema

In contrast to local schemas, 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).

SQL> GRANT XDBADMIN TO SCOTT;

Grant succeeded.

CONNECT scott/tiger

Connected.

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
    SCHEMADOC => bfilename('XMLDIR','purchaseOrder.xsd'),
    LOCAL => FALSE,
    GENTYPES => TRUE, 
    GENTABLES => FALSE, 
    CSID => nls_charset_id('AL32UTF8'));
END;
/

/sys/schemas/PUBLIC/xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd

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 similar the returned and original XML documents are, particularly for purposes of DOM traversals.

DOM Fidelity and SYS_XDBPD$

In order to provide DOM fidelity, Oracle XML DB has to maintain instance-level metadata. This metadata is tracked at a type level using the system-defined binary attribute SYS_XDBPD$. This attribute is referred to as the Positional Descriptor, or PD for short. The PD attribute is intended for Oracle Corporation internal use only. You should never directly access or manipulate this column.

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.

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

Specifying Unstructured (LOB-Based) Storage of Schema-Based XMLType

The default storage model is structured storage. To override this behavior, and store the entire XML document as a single LOB column, use the STORE AS CLOB clause.

CREATE TABLE purchaseorder_as_table OF XMLType
  XMLType STORE AS CLOB
  XMLSCHEMA "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
  ELEMENT "PurchaseOrder";
 
CREATE TABLE purchaseorder_as_column (id NUMBER, xml_document XMLType)
  XMLType COLUMN xml_document
  STORE AS CLOB
  XMLSchema "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
  ELEMENT "PurchaseOrder";

Specifying Storage Models for Structured Storage of Schema-Based XMLType

When structured storage is selected, collections (elements which have maxOccurs 1, allowing them to appear multiple times) are mapped into SQL VARRAY values. By default, the entire contents of such a VARRAY is serialized using a single LOB column. This storage model provides for optimal ingestion and retrieval of the entire document, but it has significant limitations when it is necessary to index, update, or retrieve individual members of the collection. A developer may override the way in which a VARRAY is stored, and force the members of the collection to be stored as a set of rows in a nested table. This is done by adding an explicit VARRAY STORE AS clause to the CREATE TABLE statement.

Developers can also add STORE AS clauses for any LOB columns that will be generated by the CREATE TABLE statement.

Note that the collection and the LOB column must be identified using object-relational notation. Therefore, it is important to understand the structure of the objects that are generated when a XML Schema is registered.

CREATE table purchaseorder_as_table 
  OF XMLType (UNIQUE ("XMLDATA"."Reference"),
              FOREIGN KEY ("XMLDATA"."User") REFERENCES hr.employees (email))
ELEMENT   
  "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd#PurchaseOrder" 
  VARRAY "XMLDATA"."Actions"."Action"
            STORE AS TABLE action_table1 ((PRIMARY KEY (nested_table_id, array_index))
                                          ORGANIZATION INDEX OVERFLOW   )
  VARRAY "XMLDATA"."LineItems"."LineItem"
            STORE AS TABLE lineitem_table1 ((PRIMARY KEY (nested_table_id, array_index))
                                            ORGANIZATION INDEX OVERFLOW)
  LOB ("XMLDATA"."Notes")
            STORE AS (TABLESPACE USERS ENABLE STORAGE IN ROW 
              STORAGE(INITIAL 4K NEXT 32K));

CREATE TABLE purchaseorder_as_column (
  id NUMBER,
  xml_document XMLType,
  UNIQUE (xml_document."XMLDATA"."Reference"),
  FOREIGN KEY (xml_document."XMLDATA"."User") REFERENCES hr.employees (email))
 
XMLType COLUMN xml_document
XMLSchema "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
ELEMENT "PurchaseOrder"
  VARRAY xml_document."XMLDATA"."Actions"."Action"
        STORE AS TABLE action_table2 ((PRIMARY KEY (nested_table_id, array_index))
                                          ORGANIZATION INDEX OVERFLOW)
  VARRAY xml_document."XMLDATA"."LineItems"."LineItem"
            STORE AS TABLE lineitem_table2      ((PRIMARY KEY (nested_table_id, array_index))
                                            ORGANIZATION INDEX OVERFLOW)
  LOB (xml_document."XMLDATA"."Notes")
            STORE AS (TABLESPACE USERS ENABLE STORAGE IN ROW 
              STORAGE(INITIAL 4K NEXT 32K));

Specifying Relational Constraints on XMLType Tables and Columns

When structured storage is selected, typical relational constraints can be specified for elements and attributes that occur once in the XML document. Example 5-12 shows how to use object-relational notation to define a unique constraint and a foreign key constraint when creating the table.

Note that it is not possible to define constraints for XMLType tables and columns that make use of unstructured storage.

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.

DECLARE
  doc VARCHAR2(3000) := 
   '<schema
     targetNamespace="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
     xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.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://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd', doc);
END;

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

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.

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.

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.

xmlns:xdb"http://xmlns.oracle.com/xdb"

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, then it is mapped to a VARCHAR2 attribute of the specified length. However, if maxLength is not specified in the XML Schema, then 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.

Working with Time Zones

The following XML Schema types allow for an optional time-zone indicator as part of their literal values.

• xsd:dateTime

• xsd:time

• xsd:date

• xsd:gYear

• xsd:gMonth

• xsd:gDay

• xsd:gYearMonth

• xsd:gMonthDay

By default, the schema registration maps xsd:dateTime and xsd:time to SQL TIMESTAMP and all the other datatypes to SQL DATE. The SQL TIMESTAMP and DATE types do not permit the time-zone indicator.

However, if the application needs to work with time-zone indicators, then the schema should explicitly specify the SQL type to be TIMESTAMP WITH TIME ZONE, using the xdb:SQLType attribute. This ensures that values containing time-zone indicators can be stored and retrieved correctly.

Example:

<element name="dob" type="xsd:dateTime" 
  xdb:SQLType="TIMESTAMP WITH TIME ZONE"/>

<attribute name="endofquarter" type="xsd:gMonthDay" 
  xdb:SQLType="TIMESTAMP WITH TIME ZONE"/>

Note: Using trailing Z to indicate UTC time zone.

XML Schema allows the time-zone component to be specified as Z to indicate UTC time zone. When a value with a trailing Z is stored in a TIMESTAMP WITH TIME ZONE column, the time zone is actually stored as +00:00. Thus, the retrieved value contains the trailing +00:00 and not the original Z.

Example: If the value in the input XML document is 1973-02-12T13:44:32Z, the output will look like 1973-02-12T13:44:32.000000+00:00.

Specifying Attributes in a complexType XML Schema Declaration

When you have an element based on a global complexType, the SQLType and SQLSchema attributes must be specified for the complexType declaration. In addition you can optionally include the same SQLType and SQLSchema attributes within the element declaration.

The reason is that if you do not specify the SQLType for the global complexType, Oracle XML DB creates a SQLType with an internally generated name. The elements that reference this global type cannot then have a different value for SQLType. In other words, the following code is fine:

<xsd:complexType name="PURCHASEORDERLINEITEM_TYPEType">
  <xsd:sequence>
    <xsd:element name="LineNo" type="xsd:double" 
                 xdb:SQLName="LineNo" xdb:SQLType="NUMBER"/>
    <xsd:element name="Decription" type="xsd:string"
                 xdb:SQLName="Decription" xdb:SQLType="VARCHAR2"/>
    <xsd:element name="Part" type="PURCHASEORDERPART_TYPEType"
                 xdb:SQLName="Part" />
  </xsd:sequence>
</xsd:complexType>
  <xsd:complexType name="PURCHASEORDERPART_TYPEType" xdb:SQLSchema="XMLUSER"
                   xdb:SQLType="PURCHASEORDERPART_TYPE">
     <xsd:sequence>
       <xsd:element name="Id" type="xsd:string" 
                    xdb:SQLName="Id"xdb:SQLType="VARCHAR2"/>
       <xsd:element name="Quantity" type="xsd:double"
                    xdb:SQLName="Quantity" xdb:SQLType="NUMBER"/>
       <xsd:element name="cost" type="xsd:double"
                    xdb:SQLName="cost"xdb:SQLType="NUMBER"/>
     </xsd:sequence>
   </xsd:complexType>

The following is also fine:

<xsd:complexType name="PURCHASEORDERLINEITEM_TYPEType">
  <xsd:sequence>
    <xsd:element name="LineNo" type="xsd:double" 
                 xdb:SQLName="LineNo" xdb:SQLType="NUMBER"/>
    <xsd:element name="Decription" type="xsd:string"
                 xdb:SQLName="Decription" xdb:SQLType="VARCHAR2"/>
    <xsd:element name="Part" type="PURCHASEORDERPART_TYPEType"
                 xdb:SQLName="Part" xdb:SQLSchema="XMLUSER"
                 xdb:SQLType="PURCHASEORDERPART_TYPE" />
  </xsd:sequence>
</xsd:complexType>

When using XMLType functions such as extract() and existsNode() remotely for XML Schema-based views or tables, you must specify the namespace completely.

Oracle XML DB does not support NVARCHAR or NCHAR as a SQLType when registering an XML Schema. In other words in the XML Schema .xsd file you cannot specify that an element should be of type NVARCHAR or NCHAR. Also, if you provide your own type you should not use these datatypes.

What Is XPath Rewrite?

When the XMLType is stored in structured storage (object-relationally) using an XML Schema and queries using XPath are used, they can potentially be rewritten directly to the underlying object-relational columns. This rewrite of queries can also potentially happen when queries using XPath are issued on certain non-schema-based XMLType views.

This enables the use of B*Tree or other indexes, if present on the column, to be used in query evaluation by the Optimizer. This XPath rewrite mechanism is used for XPaths in SQL functions such as existsNode(), extract(), extractValue(), and updateXML(). This enables the XPath to be evaluated against the XML document without having to ever construct the XML document in memory.

SELECT VALUE(p) FROM MyPOs p
     WHERE extractValue(value(p),'/PurchaseOrder/Company') = 'Oracle';

SELECT VALUE(p) FROM MyPOs p WHERE p.xmldata.Company = 'Oracle';

If there was a regular index created on the Company column, such as:

CREATE INDEX company_index ON MyPos e
      (extractvalue(value(e),'/PurchaseOrder/Company'));

then the preceding query would use the index for its evaluation.

XPath rewrite happens for XML Schema-based tables and both schema-based and non-schema based views. In this chapter we consider examples related to schema-based tables.

When Does XPath Rewrite Occur?

XPath rewrite happens for the following SQL functions:

• extract

• existsNode

• extractValue

• updateXML

• XMLSequence

The rewrite happens when these SQL functions are present in any expression in a query, DML, or DDL statement. For example, you can use extractValue() to create indexes on the underlying relational columns.

SELECT extractValue(value(x), '/PurchaseOrder/Company')
  FROM MYPOs x
  WHERE existsNode(value(x), '/PurchaseOrder/Item[1]/Part') = 1;

DELETE FROM MYPOs x
  WHERE extractValue(value(x),'/PurchaseOrder/Company') = 'Oracle Corp';

CREATE INDEX company_index ON MyPos e
      (extractValue(value(e),'/PurchaseOrder/Company'));

What XPath Expressions Are Rewritten?

The rewrite of XPath expressions happen if all of the following hold true:

• The XML function or method is rewritable.

  The SQL functions extract, existsNode, extractValue, updateXML and XMLSequence get rewritten. Other than the existsNode() method, none of the methods of XMLType get rewritten. You can however use the SQL function equivalents instead.

• The XPath construct is rewritable

  XPath constructs such as simple expressions, wildcards, and descendent axes get rewritten. The XPath may select attributes, elements or text nodes. Predicates also get rewritten to SQL predicates. Expressions involving parent axes, sibling axis, and so on are not rewritten.

• The XMLSchema constructs for these paths are rewritable.

  XMLSchema constructs such as complex types, enumerated values, lists, inherited types, and substitution groups are rewritten. Constructs such as recursive type definitions are not rewritten.

• The storage structure chosen during the schema registration is rewritable.

  Storage using the object-relational mechanism is rewritten. Storage of complex types using CLOBs are not rewritten

Table 5-10 lists the kinds of XPath expressions that can be translated into underlying SQL queries in this release.

Is there a difference in XPath logic with rewrite?

For the most part, there is no difference between rewritten XPath queries and functionally evaluated ones. However, since XPath rewrite uses XML Schema information to turn XPath predicates into SQL predicates, comparison of non-numeric entities are different.

In XPath 1.0, the comparison operators, >, <, >=, and <= use only numeric comparison. The two sides of the operator are turned into numeric values before comparison. If either of them fail to be a numeric value, the comparison returns FALSE.

For instance, if I have a schema element such as,

<element name="ShipDate" type="xs:date"   xdb:SQLType="DATE"/>

An XPath predicate such as [ShipDate < '2003-02-01'] will always evaluate to false with functional evaluation, since the string value '2003-02-01' cannot be converted to a numeric quantity. With XPath rewrite, however, this gets translated to a SQL date comparison and will evaluate to true or false depending on the value of ShipDate.

Similarly if you have a collection value compared with another collection value, the XPath 1.0 semantics dictate that the values have to be converted to a string and then compared. With Query Rewrite, the comparison will use the SQL datatype comparison rules.

To suppress this behavior, you can turn off rewrite either using query hints or session level events.

How are the XPaths Rewritten?

The following sections use the same purchaseorder XML Schema explained earlier in the chapter to explain how the functions get rewritten.

DECLARE
  doc VARCHAR2(2000) := 
   '<schema  
     targetNamespace="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
     xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd" 
     xmlns="http://www.w3.org/2001/XMLSchema"   
     elementFormDefault="qualified">
      <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="20"/>
                    </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://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd', doc);
END;
/

SQL> CREATE TABLE MYPOs OF XMLType
   2 XMLSchema "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
   3 ELEMENT "PurchaseOrder"
   4 VARRAY xmldata."Item" store as table item_nested;

Table created

INSERT INTO MyPos
VALUES(
 XMLType(
 '<PurchaseOrder
   xmlns="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
   xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
   xsi:schemaLocation="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd   
   http://xmlns.oracle.com/xdb/documentation/purchaseOrder.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>'));

/PurchaseOrder[PONum=1001 and Company = "Oracle Corp"]

( XMLData."PONum" = 20 and XMLData."Company" = "Oracle Corp")

SELECT Extract(value(p),'/PurchaseOrder/Item').getClobval()
   FROM MYPOs p
   WHERE ExistsNode(value(p),'/PurchaseOrder[PONum=1001 
     AND Company = "Oracle Corp"]') =1;

/PurchaseOrder[Items/Price > 200]
-- maps to a SQL collection expression:
exists(SELECT null FROM TABLE (XMLDATA."Item") x
                   WHERE  x."Price" > 200 )

SELECT Extract(value(p),'/PurchaseOrder/Item').getClobval()
  FROM MYPos p
  WHERE ExistsNode(value(p),'/PurchaseOrder[Item/Price > 400]') = 1;

/PurchaseOrder[Items/Price = Items/Part]
-- maps to a SQL collection expression:
   exists(SELECT null 
            FROM   TABLE (XMLDATA."Item") x
            WHERE  EXISTS (SELECT null 
                             FROM  TABLE(XMLDATA."Item") y
                             WHERE  y."Part" = x."Price"))

SELECT Extract(value(p),'/PurchaseOrder/Item').getClobval()
  FROM  MYPOs p
  WHERE ExistsNode(value(p),'/PurchaseOrder[Item/Price = Item/Part]') = 1;

SELECT extract(value(p),'/PurchaseOrder/Item[Price>2100]/Part')
  FROM MYPOs p;

SELECT (SELECT XMLAgg(XMLForest(x."Part" AS "Part")) 
          FROM   TABLE (XMLData."Item") x
          WHERE  x."Price" > 2100)
  FROM MYPOs p;

SELECT * FROM MYPOs p
  WHERE  ExistsNode(value(p), '/PurchaseOrder/PONum') = 1;

SELECT * 
  FROM MYPOs p
  WHERE ExistsNode(
          value(p),
          '/PurchaseOrder/PONum',
          'xmlns="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd')
        = 1;

existsNODE(
  value(p),
  '/a:PurchaseOrder/PONum',                   
  'xmlns:a="http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd")
= 1;

[@PurchaseDate="2002-02-01"]

XMLData."PurchaseDate" = "2002-02-01"

XMLData."PurchaseDate" = TO_DATE("2002-02-01","SYYYY-MM-DD");

existsNode(value(p),'/PurchaseOrder/PONum/text()') = 1;

(p.XMLDATA."PONum" IS NOT NULL)

existsNode(value(p),'/PurchaseOrder/PONum') = 1;

(check-node-exists(p.XMLDATA."SYS_XDBPD$","PONum") IS NOT NULL)

Extract(value(p),'/PurchaseOrder/PONum')

CASE WHEN check-node-exists(p.XMLDATA.SYS_XDBPD$", "PONum") IS NOT NULL 
   THEN XMLElement("PONum", p.XMLDATA."PONum")
   ELSE NULL END;

SELECT count(*)
FROM   mypos p
WHERE EXISTSNODE(value(p),'/PurchaseOrder[PONum=1001 AND 
                                          Item/Price > 2000]')= 1;

SELECT count(*) 
FROM   mypos p
WHERE  CASE WHEN 
           p.XMLData."PONum" = 1001 AND 
           EXISTS ( SELECT NULL 
                    FROM   TABLE ( XMLData."Item") p
                    WHERE  p."Price" > 2000 )) THEN 1 ELSE 0 END  = 1;

SELECT count(*) 
FROM   mypos p
WHERE  p.XMLData."PONum" = 1001 AND 
       EXISTS ( SELECT NULL 
                FROM   TABLE ( p.XMLData."Item") x
                WHERE  x."Price" > 2000 );

SELECT extractValue(value(p),'/PurchaseOrder/PONum') 
   FROM   mypos p
   WHERE  extractValue(value(p),'/PurchaseOrder/PONum') = 1001;

SELECT p.XMLData."PONum"
   FROM   mypos p
   WHERE  p.XMLData."PONum" = 1001;

create index my_po_index on mypos x 
  (extract(value(x),'/PurchaseOrder/PONum/text()').getnumberval());

create index my_po_index on mypos x ( x.XMLData."PONum");

existsNode(value(x),'/PurchaseOrder[PONum=1001]') = 1;

SQL> SELECT extractValue(value(p),'/PurchaseOrder/PONum') as ponum,
 Extractvalue(value(i) , '/Item/Part') as part,
 Extractvalue(value(i), '/Item/Price') as price
FROM MyPOs p,
     TABLE(XMLSequence(extract(value(p),'/PurchaseOrder/Item'))) i;
     PONUM PART                                 PRICE
---------- -------------------- ----------
      1001 9i Doc Set                      2550
      1001 8i Doc Set                      350

SQL> EXPLAIN PLAN
FOR SELECT extractValue(value(p),'/PurchaseOrder/PONum') AS ponum,
 extractValue(value(i) , '/Item/Part') AS part,
 extractValue(value(i), '/Item/Price') AS price
FROM MyPOs p,
     TABLE(XMLSequence(extract(value(p),'/PurchaseOrder/Item'))) i;

Explained

SQL> @utlxpls.sql

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id  | Operation                                    | Name              |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |
|   1 |  NESTED LOOPS                            |              |
|   2 |   TABLE ACCESS FULL                | ITEM_NESTED  |
|   3 |   TABLE ACCESS BY INDEX ROWID | MYPOS           |
|*  4 |    INDEX UNIQUE SCAN              | SYS_C002973  |
----------------------------------------------------------------------------

Predicate Information (identified by operation id)
--------------------------------------------------
   4 - access("NESTED_TABLE_ID"="P"."SYS_NC0001100012$")

SQL> CREATE INDEX price_index ON item_nested ("Price");

Index created.

SQL> EXPLAIN PLAN FOR
     SELECT extractValue(value(p),'/PurchaseOrder/PONum') AS ponum,
            extractValue(value(i) , '/Item/Part') AS part,
            extractValue(value(i), '/Item/Price') AS price
     FROM  MyPOs p,
          TABLE(XMLSequence(extract(value(p),'/PurchaseOrder/Item'))) i
     WHERE extractValue(value(i),'/Item/Price') > 2000;

Explained.

SQL> @?/rdbms/admin/utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------
| Id  | Operation                                    | Name               | 
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |
|   1 |  NESTED LOOPS                            |               |
|   2 |   TABLE ACCESS BY INDEX ROWID | ITEM_NESTED   |
|*  3 |    INDEX RANGE SCAN                | PRICE_INDEX   |
|   4 |   TABLE ACCESS BY INDEX ROWID | MYPOS         |
|*  5 |    INDEX UNIQUE SCAN              | SYS_C002973   |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ITEM_NESTED"."Price">2000)
   5 - access("NESTED_TABLE_ID"="P"."SYS_NC0001100012$")

SELECT extract(value(p),'/PurchaseOrder[Item/Part > 2000]/PONum')
FROM   PurchaseOrder_table p;

SELECT (SELECT CASE WHEN check_node_exists(p.XMLData.SYS_XDBPD$, 'PONum') 
                         IS NOT NULL
                THEN XMLElement("PONum", p.XMLData."PONum") 
                ELSE NULL END)
        FROM  DUAL
        WHERE  EXISTS( SELECT NULL 
                       FROM   TABLE ( XMLData."Item") p
                       WHERE  p."Part" > 2000)
       )
FROM PurchaseOrder_table p;

UPDATE PurchaseOrder_table p SET value(p) =

updatexml(value(p),'/PurchaseOrder/@PurchaseDate','2002-01-02',
                       '/PurchaseOrder/PONum/text()', 2200);

UPDATE PurchaseOrder_table p
  SET p.XMLData."PurchaseDate" = TO_DATE('2002-01-02','SYYYY-MM-DD'),
      p.XMLData."PONum" = 2100;

Diagnosing XPath Rewrite

To determine if your XPath expressions are getting rewritten, you can use one of the following techniques:

SQL> CREATE INDEX company_index ON MyPOs e
       (extractValue(object_value,'/PurchaseOrder/Company'));

Index created.

SQL> EXPLAIN PLAN FOR 
       SELECT  extractValue(value(p),'/PurchaseOrder/PONum')
         FROM MyPOs p
         WHERE existsNode(value(p),'/PurchaseOrder[Company="Oracle"]')=1;

Explained.

SQL> @utlxpls.sql

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
| Id  | Operation                                  | Name                 | Rows | Bytes | Cost |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                          |              |        |              |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYPOS              |            |        |              |
|*  2 |   INDEX RANGE SCAN                | COMPANY_INDEX |           |        |              |
-------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MYPOS"."SYS_NC00010$"='Oracle')

SQL> EXPLAIN PLAN FOR
       SELECT  extractValue(value(p),'/PurchaseOrder/PONum')
         FROM MyPOs p
         WHERE existsNode(value(p),
                 '/PurchaseOrder[Company+PONum="Oracle"]') = 1;

Explained.

SQL> @utlxpls.sql

PLAN_TABLE_OUTPUT
-----------------------------------------------------------
| Id  | Operation                 | Name  
-----------------------------------------------------------
|   0 | SELECT STATEMENT   |
|*  1 |  FILTER                    |
|   2 |   TABLE ACCESS FULL| MYPOS
|*  3 |   TABLE ACCESS FULL| ITEM_NESTED
-----------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(EXISTSNODE(SYS_MAKEXML('C6DB2B4A1A3B0
                      6CDE034080020E5CF39',2300,"MYPOS"."XMLEXTRA",
              "MYPOS"."XMLDATA"),
      '/PurchaseOrder[Company+PONum="Oracle"]')=1)
   3 - filter("NESTED_TABLE_ID"=:B1)

ALTER SESSION SET EVENTS '19021 trace name context forever, level 1';

SQL> SELECT value(p) FROM MyPOs p
     WHERE Existsnode(value(p),
                 '/PurchaseOrder[Company+PONum="Oracle"]')=1 ;

ERROR:
ORA-19022: XML XPath functions are disabled

SQL> alter session set events '19027 trace name context forever, level 8192';Session altered.SQL> EXPLAIN PLAN FOR
     SELECT value(p) from MyPOs p
     WHERE Existsnode(value(p),'/PurchaseOrder[Company+100="Oracle"]')=1;Explained.

NO REWRITE        XPath ==> /PurchaseOrder[Company+PONum = "Oracle" ]        Reason ==> non numeric inputs to arith{2}{4}