Skip Headers

Oracle9i XML Database Developer's Guide - Oracle XML DB
Release 2 (9.2)

Part Number A96620-02
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Feedback

Go to previous page Go to next page
View PDF

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

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.

See Also:

Appendix B, "XML Schema Primer"

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>

Note:

The URL 'http://www.oracle.com/PO.xsd' used here is simply a name that uniquely identifies the registered XML schema within the database and need not be the physical URL at the which the XML schema document is located. Also, the target namespace of the XML schema is another URL, different from the XML schema location URL, that specifies an abstract namespace within which elements and types get declared.

An XML schema can optionally specify the target namespace URL. If this attribute is omitted, the XML schema has no target namespace. Note: The targetnamespace is commonly the same as XML schema's URL.

An XML instance document must specify both the namespace of the root element (same as the XML schema's target namespace) and the location (URL) of the XML schema that defines this root element. The location is specified with attribute xsi:schemaLocation. When the XML schema has no target namespace, use attribute xsi:noNamespaceSchemaLocation to specify the XML schema URL.


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:

See Also:

Namespace of XML Schema constructs: http://www.w3.org/2001/XMLSchema

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:

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:

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.

See Also:

Oracle9i XML API Reference - XDK and Oracle XML DB

Two of the main DBMS_XMLSCHEMA functions are:

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:

After registration has completed:

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>')); 
See Also:

Oracle9i XML API Reference - XDK and Oracle XML DB

Local and Global XML Schemas

XML schemas can be registered as local or global:

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.

See Also:

Chapter 18, "Oracle XML DB Resource Security"

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.


Note: Typically, only the owner of the XML schema can use it to define XMLType tables, columns, or views, validate documents, and so on. However, Oracle supports fully qualified XML schema URLs which can be specified as:

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

This extended URL can be used by privileged users to specify XML schema belonging to other users.


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.


Note:

Access to this directory is controlled by Access Control Lists (ACLs) and, by default, is writable only by a DBA. You need WRITE privileges on this directory to register global schemas.

XDBAdmin role also provides WRITE access to this directory, assuming that it is protected by the default "protected" ACL.

See also Chapter 18, "Oracle XML DB Resource Security" for further information on privileges and for details on XDBAdmin role.


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:

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:

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.

See Also:

Oracle9i XML API Reference - XDK and Oracle XML DB the chapter on DBMS_XMLSCHEMA.

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:

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:

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.

See Also:

Chapter 6, "Transforming and Validating XMLType Data"

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.

XML Schema That Users Cannot Reference

These rules imply that, by default, users cannot reference the following kinds of 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:

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.

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
See Also:

How Are XML Schema-Based XMLType Structures Stored?

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

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.

See Also:

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

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:

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.


Note:

The PD attribute is mainly intended for Oracle internal use only. You should never directly access or manipulate this 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.


Note:

The attribute SYS_XDBPD$ is omitted in many examples here for clarity. However, the attribute is always present as a Positional Descriptor (PD) column in all SQL object types generated by the XML schema registration process.

In general however, it is not a good idea to suppress the PD attribute because the extra pieces of information, such as, comments, processing instructions, and so on, could be lost if there is no PD column.


Creating XMLType Tables and Columns Based on XML Schema

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

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 XMLType_table.gif follows
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.


Note:

The names of the types are generated names, and will not necessarily match Itemxxx_t, Itemxxx_COLL and PurchaseOrderTypexxx_T, where xxx is a 3-digit integer.


 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
); 


Note:

The names of the object types and attributes in the preceding example can be system-generated.

  • If the XML schema already contains the SQLName, SQLType, or SQLColType attribute filled in (see "Specifying SQL Object Type Names with SQLName, SQLType Attributes" for details), this name is used as the object attribute's name.
  • If the XML schema does not contain the SQLName attribute, the name is derived from the XML name, unless it cannot be used because of length or conflict reasons.

If the SQLSchema attribute is used, 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.


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.

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 adxdb025.gif follows
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:

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:

Setting Element Mapping Type Information

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

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:

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

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:

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

Text description of adxdb038.gif follows
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.

See Also:

Table 5-5, "Mapping XML String Datatypes to SQL"

XML Schema: Mapping complexTypes to SQL

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

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.

See Also:

"Ordered Collections in Tables (OCTs)"

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 adxdb010.gif follows
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">
                 <sequence>
                    <element name = "Street" type = "string"/>
                    <element name = "City" type = "string"/>
                 </sequence>
              </complexType>
           </element>
        </sequence>
     </complexType>
  </schema>';
begin
        dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc);
end;

On registering this XML schema, Oracle XML DB generates the following types and XMLType tables:

CREATE TYPE OBJ_T1 AS OBJECT 
(
  SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
  Street VARCHAR2(4000), 
  City VARCHAR2(4000) 
);

CREATE TYPE OBJ_T2 AS OBJECT 
( 
  SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
  Name VARCHAR2(4000), 
  Age NUMBER, 
  Addr REF XMLType 
);

Mapping XML Fragments to Large Objects (LOBs)

You can specify the SQLType for a complex element as a Character Large Object (CLOB) or Binary Large Object (BLOB) as shown in Figure 5-5. Here the entire XML fragment is stored in a LOB attribute. This is useful when parts of the XML document are seldom queried but are mostly retrieved and stored as single pieces. By storing XML fragments as LOBs, you can save on parsing/decomposition/recomposition overheads.

Example 5-15 Oracle XML DB XML Schema: complexType Mapping XML Fragments to LOBs

In the following example, the XML schema specifies that the XML fragment's element Addr is using the attribute SQLType="CLOB":

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:SQLType = "CLOB">
              <complexType >
                 <sequence>
                    <element name = "Street" type = "string"/>
                    <element name = "City" type = "string"/>
                 </sequence>
              </complexType>
           </element>
        </sequence>
     </complexType>
  </schema>';
begin
        dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc);
end;

On registering this XML schema, Oracle XML DB generates the following types and XMLType tables:

CREATE TYPE OBJ_T AS OBJECT 
( 
  SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
  Name VARCHAR2(4000), 
  Age NUMBER, 
  Addr CLOB 
);

Figure 5-5 Mapping complexType XML Fragments to Character Large Objects (CLOBs)

Text description of adxdb011.gif follows
Text description of the illustration adxdb011.gif


Oracle XML DB complexType Extensions and Restrictions

In XML schema, complexTypes are declared based on complexContent and simpleContent.

complexType Declarations in XML Schema: Handling Inheritance

For complexType, Oracle XML DB handles inheritance in the XML schema as follows:

Example 5-16 Inheritance in XML Schema: complexContent as an Extension of complexTypes

Consider an XML schema that defines a base complexType "Address" and two extensions "USAddress" and "IntlAddress".

declare
        doc varchar2(3000) := '<xs:schema 
xmlns:xs="http://www.w3.org/2001/XMLSchema" 
           xmlns:xdb="http://xmlns.oracle.com/xdb">
  <xs:complexType name="Address" xdb:SQLType="ADDR_T">
    <xs:sequence>
      <xs:element name="street" type="xs:string"/>
      <xs:element name="city" type="xs:string"/>
    </xs:sequence>
  </xs:complexType>

  <xs:complexType name="USAddress" xdb:SQLType="USADDR_T">
   <xs:complexContent>
    <xs:extension base="Address">
      <xs:sequence>
        <xs:element name="zip" type="xs:string"/>
      </xs:sequence>
    </xs:extension>
   </xs:complexContent>
  </xs:complexType>

  <xs:complexType name="IntlAddress" final="#all" xdb:SQLType="INTLADDR_T">
   <xs:complexContent>
    <xs:extension base="Address">
      <xs:sequence>
        <xs:element name="country" type="xs:string"/>
      </xs:sequence>
    </xs:extension>
   </xs:complexContent>
  </xs:complexType>
</xs:schema>';
begin
        dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc);
end;

Note:

Type INTLADDR_T is created as a final type because the corresponding complexType specifies the "final" attribute. By default, all complexTypes can be extended and restricted by other types, and hence, all SQL object types are created as not final types.


create type ADDR_T as object (
  SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
  "street" varchar2(4000),
  "city" varchar2(4000)
) not final;

create type USADDR_T under ADDR_T (
 "zip" varchar2(4000)
) not final;

create type INTLADDR_T under ADDR_T (
  "country" varchar2(4000)
) final;

Example 5-17 Inheritance in XML Schema: Restrictions in complexTypes

Consider an XML schema that defines a base complexType Address and a restricted type LocalAddress that prohibits the specification of country attribute.

declare
        doc varchar2(3000) := '<xs:schema 
xmlns:xs="http://www.w3.org/2001/XMLSchema" 
           xmlns:xdb="http://xmlns.oracle.com/xdb">
  <xs:complexType name="Address" xdb:SQLType="ADDR_T">
    <xs:sequence>
      <xs:element name="street" type="xs:string"/>
      <xs:element name="city" type="xs:string"/>
      <xs:element name="zip" type="xs:string"/>
      <xs:element name="country" type="xs:string" minOccurs="0" maxOccurs="1"/>
    </xs:sequence>
  </xs:complexType>

  <xs:complexType name="LocalAddress" xdb:SQLType="USADDR_T">
    <xs:complexContent>
     <xs:restriction base="Address">
      <xs:sequence>
        <xs:element name="street" type="xs:string"/>
        <xs:element name="city" type="xs:string"/>
        <xs:element name="zip" type="xs:string"/>
        <xs:element name="country" type="xs:string" 
             minOccurs="0" maxOccurs="0"/>
      </xs:sequence>
     </xs:restriction>
    </xs:complexContent>
  </xs:complexType>
</xs:schema>';
begin
        dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc);
end;

Since inheritance support in SQL does not support a notion of restriction, the SQL type corresponding to the restricted complexType is a empty subtype of the parent object type.For the preceding XML schema, the following SQL types are generated:

create type ADDR_T as object (
  SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
  "street" varchar2(4000),
  "city" varchar2(4000),
  "zip" varchar2(4000),
  "country" varchar2(4000)
) not final;

create type USADDR_T under ADDR_T;

Mapping complexType: simpleContent to Object Types

A complexType based on a simpleContent declaration is mapped to an object type with attributes corresponding to the XML attributes and an extra SYS_XDBBODY attribute corresponding to the body value. The datatype of the body attribute is based on simpleType which defines the body's type.

Example 5-18 XML Schema complexType: Mapping complexType to simpleContent

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="name" xdb:SQLType="OBJ_T"> 
  <simpleContent> 
    <restriction base = "string"> 
    </restriction> 
  </simpleContent> 
</complexType>
</schema>';
begin
        dbms_xmlschema.registerSchema('http://www.oracle.com/emp.xsd', doc);
end;

On registering this XML schema, Oracle XML DB generates the following types and XMLType tables:

create type OBJ_T as object 
( 
  SYS_XDBPD$  xdb.xdb$raw_list_t,
  SYS_XDBBODY$ VARCHAR2(4000) 
);

Mapping complexType: Any and AnyAttributes

Oracle XML DB maps the element declaration, any, and the attribute declaration, anyAttribute, to VARCHAR2 attributes (or optionally to Large Objects (LOBs)) in the created object type. The object attribute stores the text of the XML fragment that matches the any declaration.

Example 5-19 Oracle XML DB XML Schema: Mapping complexType to Any/AnyAttributes

This XML schema example declares an any element and maps it to the column SYS_XDBANY$, in object type OBJ_T. This element also declares that the attribute, processContents, skips validating contents that match the any declaration.

declare
doc varchar2(3000) := '<schema xmlns="http://www.w3.org/2001/XMLSchema" 
targetNamespace="http://www.oracle.com/any.xsd" 
xmlns:emp="http://www.oracle.com/any.xsd" 
xmlns:xdb="http://xmlns.oracle.com/xdb">
 <complexType name = "Employee" xdb:SQLType="OBJ_T">
  <sequence>
     <element name = "Name" type = "string" />
     <element name = "Age" type = "decimal"/>
     <any namespace = "http://www/w3.org/2001/xhtml" processContents = "skip"/>
  </sequence>
 </complexType>
</schema>';
begin
     dbms_xmlschema.registerSchema('http://www.oracle.com/emp.xsd', doc);
end;

It results in the following statement:

CREATE TYPE OBJ_T AS OBJECT
( 
  SYS_XDBPD$  xdb.xdb$raw_list_t,
  Name VARCHAR2(4000), 
  Age NUMBER, 
  SYS_XDBANY$ VARCHAR2(4000) 
);

Handling Cycling Between complexTypes in XML Schema

Cycles in the XML schema are broken while generating the object types, because object types do not allow cycles, by introducing a REF attribute at the point at which the cycle gets completed. Thus part of the data is stored out-of-line yet still belongs to the parent XML document when it is retrieved.

Example 5-20 XML Schema: Cycling Between complexTypes

XML schemas permit cycling between definitions of complexTypes. Figure 5-6 shows this example, where the definition of complexType CT1 can reference another complexType CT2, whereas the definition of CT2 references the first type CT1.

XML schemas permit cycling between definitions of complexTypes. This is an example of cycle of length 2:

declare 
doc varchar2(3000) := '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
           xmlns:xdb="http://xmlns.oracle.com/xdb">
  <xs:complexType name="CT1" xdb:SQLType="CT1">
    <xs:sequence>
      <xs:element name="e1" type="xs:string"/>
      <xs:element name="e2" type="CT2"/>
    </xs:sequence>
  </xs:complexType>

  <xs:complexType name="CT2" xdb:SQLType="CT2">
    <xs:sequence>
      <xs:element name="e1" type="xs:string"/>
      <xs:element name="e2" type="CT1"/>
    </xs:sequence>
  </xs:complexType>
</xs:schema>';
begin
        dbms_xmlschema.registerSchema('http://www.oracle.com/emp.xsd', doc);
end;

SQL types do not allow cycles in type definitions. However, they support weak cycles, that is, cycles involving REF (references) attributes. Therefore, cyclic XML schema definitions are mapped to SQL object types such that any cycles are avoided by forcing SQLInline="false" at the appropriate point. This creates a weak cycle.

For the preceding XML schema, the following SQL types are generated:

create type CT1 as object 
(
  SYS_XDBPD$  xdb.xdb$raw_list_t,
  "e1" varchar2(4000),
  "e2" ref xmltype;
) not final;

create type CT2 as object 
(
  SYS_XDBPD$  xdb.xdb$raw_list_t,
  "e1" varchar2(4000),
  "e2" CT1
) not final;

Figure 5-6 Cross Referencing Between Different complexTypes in the Same XML Schema

Text description of adxdb012.gif follows
Text description of the illustration adxdb012.gif


Example 5-21 XML Schema: Cycling Between complexTypes, Self-Referencing

Another example of a cyclic complexType involves the declaration of the complexType having a reference to itself. The following is an example of type <SectionT> that references itself:

declare 
        doc varchar2(3000) := '<xs:schema 
xmlns:xs="http://www.w3.org/2001/XMLSchema" 
xmlns:xdb="http://xmlns.oracle.com/xdb">
  <xs:complexType name="SectionT" xdb:SQLType="SECTION_T">
    <xs:sequence>
      <xs:element name="title" type="xs:string"/>
      <xs:choice maxOccurs="unbounded">
        <xs:element name="body" type="xs:string" xdb:SQLCollType="BODY_COLL"/>
        <xs:element name="section" type="SectionT"/>
      </xs:choice>
    </xs:sequence>
  </xs:complexType>
</xs:schema>';
begin
        dbms_xmlschema.registerSchema('http://www.oracle.com/section.xsd', doc);
end;

The following SQL types are generated.


Note:

The section attribute is declared as a varray of REFs to XMLType instances. Since there can be more than one occurrence of embedded sections, the attribute is a VARRAY. And it's a VARRAY of REFs to XMLTypes in order to avoid forming a cycle of SQL objects.


create type BODY_COLL as varray(32767) of VARCHAR2(4000);

create type SECTION_T as object 
(
  SYS_XDBPD$  xdb.xdb$raw_list_t,
  "title" varchar2(4000),
  "body" BODY_COLL,
  "section" XDB.XDB$REF_LIST_T
) not final;

Further Guidelines for Creating XML Schema-Based XML Tables

Assume that your XML schema, identified by "http://www.oracle.com/PO.xsd", has been registered. An XMLType table, myPOs, can then be created to store instances conforming to element, PurchaseOrder, of this XML schema, in an object-relational format as follows:

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

Figure 5-7 illustrates schematically how a complexTypes can reference or cycle itself.

See Also:

"Cyclical References Between XML Schemas"

Figure 5-7 complexType Self Referencing Within an XML Schema

Text description of adxdb037.gif follows
Text description of the illustration adxdb037.gif


Hidden columns are created. These correspond to the object type to which the PurchaseOrder element has been mapped. In addition, an XMLExtra object column is created to store the top-level instance data such as namespace declarations.


Note:

XMLDATA is a pseudo-attribute of XMLType that enables direct access to the underlying object column. See Chapter 4, "Using XMLType", under "Changing the Storage Options on an XMLType Column Using XMLData".


Specifying Storage Clauses in XMLType CREATE TABLE Statements

To specify storage, the underlying columns can be referenced in the XMLType storage clauses using either Object or XML notation:

Referencing XMLType Columns Using CREATE INDEX

As shown in the preceding examples, columns underlying an XMLType column can be referenced using either an object or XML notation in the CREATE TABLE statements. The same is true in CREATE INDEX statements:

CREATE INDEX ponum_idx ON MyPOs (xmldata.ponum);
CREATE INDEX ponum_idx ON MyPOs p (ExtractValue(p, '/ponum');

Specifying Constraints on XMLType Columns

Constraints can also be specified for underlying XMLType columns, using either the object or XML notation:

Inserting New Instances into XMLType Columns

New instances can be inserted into an XMLType columns as follows:

INSERT INTO MyPOs VALUES
     (xmltype.createxml('<PurchaseOrder>.....</PurchaseOrder>'));

Query Rewrite with XML Schema-Based Structured Storage

What Is Query Rewrite?

When the XMLType is stored in structured storage (object-relationally) using an XML schema and queries using XPath are used, they are rewritten to go directly to the underlying object-relational columns. 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 query 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.

Example 5-22 Query Rewrite

For example a query such as:

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

is trying to get the value of the Company element and compare it with the literal 'Oracle'. Since the MyPOs table has been created with XML schema-based structured storage, the extractValue operator gets rewritten to the underlying relational column that stores the company information for the purchaseorder.

Thus the preceding query is rewritten to the following:

SELECT VALUE(p) FROM MyPOs p
     WHERE p.xmldata.company = 'Oracle';
See Also:

Chapter 4, "Using XMLType"

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.

When Does Query Rewrite Occur?

Query rewrite happens for the following SQL functions:

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.

Example 5-23 SELECT Statement and Query Rewrites

This example gets the existing purchase orders:

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

Here are some examples of statements that get rewritten to use underlying columns:

Example 5-24 DML Statement and Query Rewrites

This example deletes all purchaseorders where the Company is not Oracle:

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

Example 5-25 CREATE INDEX Statement and Query Rewrites

This example creates an index on the Company column, since this is stored object relationally and the query rewrite happens, a regular index on the underlying relational column will be created:

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

In this case, if the rewrite of the SQL functions results in a simple relational column, then the index is turned into a B*Tree or a domain index on the column, rather than a function-based index.

What XPath Expressions Are Rewritten?

XPath involving simple expressions with no wild cards or descendant axes get rewritten. The XPath may select an element or an attribute node. Predicates are supported and get rewritten into SQL predicates.

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

Table 5-10 Supported XPath Expressions for Translation to Underlying SQL Queries 
XPath Expression for Translation Description

Simple XPath expressions:

/PurchaseOrder/@PurchaseDate

/PurchaseOrder/Company

Involves traversals over object type attributes only, where the attributes are simple scalar or object types themselves. The only axes supported are the child and the attribute axes.

Collection traversal expressions:

/PurchaseOrder/Item/Part

Involves traversal of collection expressions. The only axes supported are child and attribute axes. Collection traversal is not supported if the SQL operator is used during CREATE INDEX or updateXML().

Predicates:

[Company="Oracle"]

Predicates in the XPath are rewritten into SQL predicates. Predicates are not rewritten for updateXML()

List indexe:

lineitem[1]

Indexes are rewritten to access the n'th item in a collection. These are not rewritten for updateXML().

Unsupported XPath Constructs

The following XPath constructs do not get rewritten:

Unsupported XML Schema Constructs

The following XML schema constructs are not supported. This means that if the XPath expression includes nodes with the following XML schema construct then the entire expression will not get rewritten:

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.

Example 5-26 Rewriting XPaths During Object Type Generation

Consider the following purchaseorder XML schema:

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"   
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="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;

-- A table is created conforming to this schema
CREATE TABLE MyPOs OF XMLTYPE 

   ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder";

-- The inserted XML document is partially validated against the schema before 
-- it is inserted. 
insert into MyPos 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>'));

Since the XML schema did not specify anything about maintaining the ordering, the default is to maintain the ordering and DOM fidelity. Hence the types have SYS_XDBPD$ attribute to store the extra information needed to maintain the ordering of nodes and to capture extra items such as comments, processing instructions and so on.

The SYS_XDBPD$ attribute also maintains the existential information for the elements (that is, whether the element was present or not in the input document). This is needed for elements with scalar content, since they map to simple relational columns. In this case, both empty and missing scalar elements map to NULL values in the column and only the SYS_XDBPD$ attribute can help distinguish the two cases. The query rewrite mechanism takes into account the presence or absence of the SYS_XDBPD$ attribute and rewrites queries appropriately.

Assuming that this XML schema is registered with the schema URL: http://www.oracle.com/PO.xsd

you can create the po_tab table with this schema as follows:

CREATE TABLE po_tab OF XMLTYPE 

XMLSCHEMA "http://www.oracle.com/PO.xsd" ELEMENT "PurchaseOrder";

Now this table has a hidden XMLData column of type "PurchaseOrder_T" that stores the actual data.

Rewriting XPath Expressions: Mapping Types and Issues

XPath expression mapping of types and topics are described in the following sections:

Mapping for a Simple XPath

A rewrite for a simple XPath involves accessing the attribute corresponding to the XPath expression. Table 5-11 lists the XPath map:

Table 5-11 Simple XPath Mapping for purchaseOrder XML Schema  
XPath Expression Maps to

/PurchaseOrder

column XMLData

/PurchaseOrder/@PurchaseDate

column XMLData."PurchaseDate"

/PurchaseOrder/PONum

column XMLData."PONum"

/PurchaseOrder/Item

elements of the collection XMLData."Item"

/PurchaseOrder/Item/Part

attirbute "Part" in the collection XMLData."Item"

Mapping for Scalar Nodes

An XPath expression can contain a text() operator which maps to the scalar content in the XML document. When rewriting, this maps directly to the underlying relational columns.

For example, the XPath expression "/PurchaseOrder/PONum/text()" maps to the SQL column XMLData."PONum" directly.

A NULL value in the PONum column implies that the text value is not available, either because the text node was not present in the input document or the element itself was missing. This is more efficient than accessing the scalar element, since we do not need to check for the existence of the element in the SYS_XBDPD$ attribute.

For example, the XPath "/PurchaseOrder/PONum" also maps to the SQL attribute XMLData."PONum",

However, in this case, query rewrite also has to check for the existence of the element itself, using the SYS_XDBPD$ in the XMLData column.

Mapping of Predicates

Predicates are mapped to SQL predicate expressions.

Example 5-27 Mapping Predicates

For example the predicate in the XPath expression:

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

maps to the SQL predicate:

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

For example, the following query is rewritten to the structured (object-relational) equivalent, and will not require Functional evaluation of the XPath.

select extract(value(p),'/PurchaseOrder/Item').getClobval()
   from mypos p
   where existsNode(value(p),'/PurchaseOrder[PONum=1001 and Company = "Oracle 
Corp"]') =1;
Mapping of Collection Predicates

XPath expressions may involve relational operators with collection expressions. In Xpath 1.0, conditions involving collections are existential checks. In other words, even if one member of the collection satisfies the condition, the expression is true.

Example 5-28 Mapping Collection Predicates

For example the collection predicate in the XPath:

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

For example, the following query is rewritten to the structured equivalent.

select extract(value(p),'/PurchaseOrder/Item').getClobval()
   from mypos p
   where existsNode(value(p),'/PurchaseOrder[Item/Price > 400]') = 1;

More complicated rewrites occur when you have a collection <condition> collection. In this case, if at least one combination of nodes from these two collection arguments satisfy the condition, then the predicate is deemed to be satisfied.

Example 5-29 Mapping Collection Predicates, Using existsNode()

For example, consider a fictitious XPath which checks to see if a Purchaseorder has Items such that the price of an item is the same as some part number:

/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"))

For example, the following query is rewritten to the structured equivalent:

select extract(value(p),'/PurchaseOrder/Item').getClobval()
    from mypos p
    where existsNode(value(p),'/PurchaseOrder[Item/Price = Item/Part]') = 1;
Document Ordering with Collection Traversals

Most of the rewrite preserves the original document ordering. However, since the SQL system does not guarantee ordering on the results of subqueries, when selecting elements from a collection using the extract() function, the resultant nodes may not be in document order.

Example 5-30 Document Ordering with Collection Traversals

For example:

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

is rewritten to use subqueries as shown in the following:

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

Though in most cases, the result of the aggregation would be in the same order as the collection elements, this is not guaranteed and hence the results may not be in document order. This is a limitation that may be fixed in future releases.

Collection Index

An XPath expression can also access a particular index of a collection For example, "/PurchaseOrder/Item[1]/Part" is rewritten to extract out the first Item of the collection and then access the Part attribute within that.

If the collection has been stored as a VARRAY, then this operation retrieves the nodes in the same order as present in the original document. If the mapping of the collection is to a nested table, then the order is undetermined. If the VARRAY is stored as an Ordered Collection Table (OCT), (the default for the tables created by the schema compiler, if storeVarrayAsTable="true" is set), then this collection index access is optimized to use the IOT index present on the VARRAY.

Non-Satisfiable XPath Expressions

An XPath expression can contain references to nodes that cannot be present in the input document. Such parts of the expression map to SQL NULLs during rewrite. For example the XPath expression: "/PurchaseOrder/ShipAddress" cannot be satisfied by any instance document conforming to the PO.xsd XML schema, since the XML schema does not allow for ShipAddress elements under PurchaseOrder. Hence this expression would map to a SQL NULL literal.

Namespace Handling

Namespaces are handled in the same way as the function-based evaluation. For schema based documents, if the function (like existsNode() or extract()) does not specify any namespace parameter, then the target namespace of the schema is used as the default namespace for the XPath expression.

Example 5-31 Handling Namespaces

For example, the XPath expression /PurchaseOrder/PONum is treated as /a:PurchaseOrder/a:PONum with xmlns:a="http://www.oracle.com/PO.xsd" if the SQL function does not explicitly specify the namespace prefix and mapping. In other words:

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

is equivalent to the query:

SELECT * FROM po_tab p
   WHERE  EXISTSNODE(value(p),'/PurchaseOrder/PONum',
       'xmlns="http://www.oracle.com/PO.xsd') = 1;

When performing query rewrite, the namespace for a particular element is matched with that of the XML schema definition. If the XML schema contains elementFormDefault="qualified" then each node in the XPath expression must target a namespace (this can be done using a default namespace specification or by prefixing each node with a namespace prefix).

If the elementFormDefault is unqualified (which is the default), then only the node that defines the namespace should contain a prefix. For instance if the PO.xsd had the element form to be unqualified, then the existsNode() function should be rewritten as:

EXISTSNODE(value(p),'/a:PurchaseOrder/PONum',
                    'xmlns:a="http://www.oracle.com/PO.xsd") = 1;


Note:

For the case where elementFormDefault is unqualified, omitting the namespace parameter in the SQL function existsNode() in the preceding example, would cause each node to default to the target namespace. This would not match the XML schema definition and consequently would not return any result. This is true whether the function is rewritten or not.


Date Format Conversions

The default date formats are different for XML schema and SQL. Consequently, when rewriting XPath expressions involving comparisons with dates, you need to use XML formats.

Example 5-32 Date Format Conversions

For example, the expression:

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

cannot be simply rewritten as:

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

since the default date format for SQL is not YYYY-MM-DD. Hence during query rewrite, the XML format string is added to convert text values into date datatypes correctly. Thus the preceding predicate would be rewritten as:

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

Similarly when converting these columns to text values (needed for extract(), and so on), XML format strings are added to convert them to the same date format as XML.

XPath Expression Rewrites for existsNode()

existsNode() returns a numerical value 0 or 1 indicating if the XPath returns any nodes (text() or element nodes). Based on the mapping discussed in the earlier section, an existsNode() simply checks if a scalar element is non-NULL in the case where the XPath targets a text() node or a non-scalar node and checks for the existence of the element using the SYS_XDBPD$ otherwise. If the SYS_XDBPD$ attribute is absent, then the existence of a scalar node is determined by the NULL information for the scalar column.

existsNode Mapping with Document Order Maintained

Table 5-12 shows the mapping of various XPaths in the case of existsNode() when document ordering is preserved, that is, when SYS_XDBPD$ exists and maintainDOM="true" in the schema document.

Table 5-12 XPath Mapping for existsNode() with Document Ordering Preserved    
XPath Expression Maps to

/PurchaseOrder

CASE WHEN XMLData IS NOT NULL THEN 1 ELSE 0 END

/PurchaseOrder/@PurchaseDate

CASE WHEN Check_Node_Exists(XMLData.SYS_XDBPD$, 'PurchaseDate') = 1

THEN 1 ELSE 0 END

/PurchaseOrder/PONum

CASE WHEN Check_Node_Exists(XMLData.SYS_XDBPD$, 'PONum') = 1

THEN 1 ELSE 0 END

/PurchaseOrder[PONum = 2100]

CASE WHEN XMLData."PONum" = 2100 THEN 1 ELSE 0

/PurchaseOrder[PONum = 2100]/@PurchaseDate

CASE WHEN XML Data."PONum" = 2100 AND Check_Node_Exists(XMLData.SYS_XDBPD$, 'PurchaseDate') = 1

THEN 1 ELSE 0 END

/PurchaseOrder/PONum/text()

CASE WHEN XMLData."PONum" IS NOT NULL THEN 1 ELSE 0

/PurchaseOrder/Item

CASE WHEN EXISTS (

SELECT NULL FROM TABLE ( XMLData."Item" ) x

WHERE value(x) IS NOT NULL) THEN 1 ELSE 0 END

/PurchaseOrder/Item/Part

CASE WHEN EXISTS (

SELECT NULL FROM TABLE (XMLData."Item" ) x

WHERE Check_Node_Exists(x.SYS_XDBPD$, 'Part') = 1)

THEN 1 ELSE 0 END

/PurchaseOrder/Item/Part/text()

CASE WHEN EXISTS (

SELECT NULL FROM TABLE (XMLData."Item" ) x

WHERE x."Part" IS NOT NULL) THEN 1 ELSE 0 END

Example 5-33 existsNode Mapping with Document Order Maintained

Using the preceding mapping, a query which checks whether the purchaseorder with number 2100 contains a part with price greater than 2000:

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

would become:

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;

The CASE expression gets further optimized due to the constant relational equality expressions and this query becomes:

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

which would use relational indexes for its evaluation, if present on the Part and PONum columns.

existsNode Mapping Without Maintaining Document Order

If the SYS_XDBPD$ does not exist (that is, if the XML schema specifies maintainDOM="false") then NULL scalar columns map to non-existent scalar elements. Hence you do not need to check for the node existence using the SYS_XDBPD$ attribute. Table 5-13 shows the mapping of existsNode() in the absence of the SYS_XDBPD$ attribute.

Table 5-13 XPath Mapping for existsNode Without Document Ordering  
XPath Expression Maps to

/PurchaseOrder

CASE WHEN XMLData IS NOT NULL THEN 1 ELSE 0 END

/PurchaseOrder/@PurchaseDate

CASE WHEN XMLData.'PurchaseDate' IS NOT NULL THEN 1 ELSE 0 END

/PurchaseOrder/PONum

CASE WHEN XMLData."PONum" IS NOT NULL THEN 1 ELSE 0 END

/PurchaseOrder[PONum = 2100]

CASE WHEN XMLData."PONum" = 2100 THEN 1 ELSE 0 END

/PurchaseOrder[PONum = 2100]/@PurchaseOrderDate

CASE WHEN XMLData."PONum" = 2100 AND

XMLData."PurchaseDate" NOT NULL THEN 1 ELSE 0 END

/PurchaseOrder/PONum/text()

CASE WHEN XMLData."PONum" IS NOT NULL THEN 1 ELSE 0 END

/PurchaseOrder/Item

CASE WHEN EXISTS (

SELECT NULL FROM TABLE (XMLData."Item" ) x

WHERE value(x) IS NOT NULL) THEN 1 ELSE 0 END

/PurchaseOrder/Item/Part

CASE WHEN EXISTS (

SELECT NULL FROM TABLE (XMLData."Item") x

WHERE x."Part" IS NOT NULL) THEN 1 ELSE 0 END

/PurchaseOrder/Item/Part/text()

CASE WHEN EXISTS (

SELECT NULL FROM TABLE (XMLData."Item") x

WHERE x."Part" IS NOT NULL) THEN 1 ELSE 0 END

Rewrite for extractValue()

extractValue() is a shortcut for extracting text nodes and attributes using extract() and then using a getStringVal() or getNumberVal() to get the scalar content. extractValue returns the text nodes for scalar elements or the values of attribute nodes. extractValue() cannot handle returning multiple values or non-scalar elements.

Table 5-14 shows the mapping of various XPath expressions in the case of extractValue(). If an XPath expression targets an element, extractValue retrieves the text node child of the element. Thus the two XPath expressions, /PurchaseOrder/PONum and /PurchaseOrder/PONum/text() are handled identically by extractValue and both of them retrieve the scalar content of PONum.

Table 5-14 XPath Mapping for extractValue()  
XPath Expression Maps to

/PurchaseOrder

Not supported - ExtractValue can only retrieve values for scalar elements and attributes

/PurchaseOrder/@PurchaseDate

XMLData."PurchaseDate"

/PurchaseOrder/PONum

XMLData."PONum"

/PurchaseOrder[PONum = 2100]

(SELECT TO_XML(x.XMLData) FROM Dual WHERE x."PONum" = 2100)

/PurchaseOrder[PONum = 2100]/@PurchaseDate

(SELECT x.XMLData."PurchaseDate")

FROM Dual

WHERE x."PONum" = 2100)

/PurchaseOrder/PONum/text()

XMLData."PONum"

/PurchaseOrder/Item

Not supported - ExtractValue can only retrieve values for scalar elements and attributes

/PurchaseOrder/Item/Part

Not supported - ExtractValue cannot retrieve multiple scalar values

/PurchaseOrder/Item/Part/text()

Not supported - ExtractValue cannot retrieve multiple scalar values

Example 5-34 Rewriting extractValue()

For example, an SQL query such as:

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

would become:

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

Since it gets rewritten to simple scalar columns, indexes if any, on the PONum attribute may be used to satisfy the query.

Creating Indexes

ExtractValue can be used in index expressions. If the expression gets rewritten into scalar columns, then the index is turned into a B*Tree index instead of a function-based index.

Example 5-35 Creating Indexes with extract

For example:

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

would get rewritten into:

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

and thus becomes a regular B*Tree index. This is useful, since unlike a function-based index, the same index can now satisfy queries which target the column such as:

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

Rewrite for extract()

extract() retrieves the results of XPath as XML. The rewrite for extract() is similar to that of extractValue() for those Xpath expressions involving text nodes.

Extract Mapping with Document Order Maintained

Table 5-15 shows the mapping of various XPath in the case of extract() when document order is preserved (that is, when SYS_XDBPD$ exists and maintainDOM="true" in the schema document).


Note:

The examples show XMLElement() and XMLForest() with an empty alias string "" to indicate that you create a XML instance with only text values. This is shown for illustration only.


Table 5-15 XPath Mapping for extract() with Document Ordering Preserved  
XPath Maps to

/PurchaseOrder

XMLForest(XMLData as "PurchaseOrder")

/PurchaseOrder/@PurchaseDate

CASE WHEN Check_Node_Exists(XMLData.SYS_XDBPD$, 'PurchaseDate') = 1

THEN XMLElement("" , XMLData."PurchaseDate") ELSE NULL END

/PurchaseOrder/PONum

CASE WHEN Check_Node_Exists(XMLData.SYS_XDBPD$, 'PONum') = 1

THEN XMLElement("PONum" , XMLData."PONum") ELSE NULL END

/PurchaseOrder[PONum = 2100]

(SELECT XMLForest(XMLData as "PurchaseOrder") from Dual

where x."PONum" = 2100)

/PurchaseOrder[PONum = 2100]/@PurchaseDate

(SELECT CASE WHEN

Check_Node_Exists(x.XMLData.SYS_XDBPD$,'PurchaseDate") = 1

THEN XMLElement("", XMLData."PurchaseDate")

ELSE NULL END

from Dual where x."PONum" = 2100)

/PurchaseOrder/PONum/text()

XMLElement("", XMLData.PONum)

/PurchaseOrder/Item

(SELECT XMLAgg(XMLForest(value(p) as "Item"))

from TABLE ( x.XMLData."Item" ) p

where value(p) IS NOT NULL )

/PurchaseOrder/Item/Part

(SELECT XMLAgg(

CASE WHEN Check_Node_Exists(p.SYS_XDBPD$,'Part") = 1

THEN XMLForest(p."Part" as "Part") ELSE NULL END)

from TABLE ( x.XMLData."Item" ) p)

/PurchaseOrder/Item/Part/text()

(SELECT XMLAgg(XMLElement(" ", p."Part") )

from TABLE ( x.XMLData."Item" ) x )

Example 5-36 XPath Mapping for extract() with Document Ordering Preserved

Using the mapping in Table 5-15, a query that extracts the PONum element where the purchaseorder contains a part with price greater than 2000:

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

would become:

SELECT (SELECT CASE WHEN Check_Node_Exists(p.XMLData.SYS_XDBPD$, 'PONum')  = 1
                    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 po_tab p;

Check_Node_Exists is an internal function that is for illustration purposes only.

Extract Mapping Without Maintaining Document Order

If the SYS_XDBPD$ does not exist, that is, if the XML schema specifies maintainDOM="false", then NULL scalar columns map to non-existent scalar elements. Hence you do not need to check for the node existence using the SYS_XDBPD$ attribute. Table 5-16 shows the mapping of existsNode() in the absence of the SYS_XDBPD$ attribute.

Table 5-16 XPath Mapping for extract() Without Document Ordering Preserved    
XPath Equivalent to

/PurchaseOrder

XMLForest(XMLData AS "PurchaseOrder")

/PurchaseOrder/@PurchaseDate

XMLForest(XMLData."PurchaseDate" AS "")

/PurchaseOrder/PONum

XMLForest(XMLData."PONum" AS "PONum")

/PurchaseOrder[PONum = 2100]

(SELECT XMLForest(XMLData AS "PurchaseOrder")

from Dual where x."PONum" = 2100)

/PurchaseOrder[PONum = 2100]/@PurchaseDate

(SELECT XMLForest(XMLData."PurchaseDate" AS "")

from Dual where x."PONum" = 2100)

/PurchaseOrder/PONum/text()

XMLForest(XMLData.PONum AS "")

/PurchaseOrder/Item

(SELECT XMLAgg(XMLForest(value(p) as "Item")

from TABLE ( x.XMLData."Item" ) p

where value(p) IS NOT NULL )

/PurchaseOrder/Item/Part

(SELECT XMLAgg(XMLForest(p."Part" AS "Part")

from TABLE ( x.XMLData."Item" ) p)

/PurchaseOrder/Item/Part/text()

(SELECT XMLAgg( XMLForest(p. "Part" AS "Part") )

from TABLE ( x.XMLData."Item" ) p )

Optimizing Updates Using updateXML()

A regular update using updateXML() involves updating a value of the XML document and then replacing the whole document with the newly updated document.

When XMLType is stored object relationally, using XML schema mapping, updates are optimized to directly update pieces of the document. For example, updating the PONum element value can be rewritten to directly update the XMLData.PONum column instead of materializing the whole document in memory and then performing the update.

updateXML() must satisfy the following conditions for it to use the optimization:

If all the preceding conditions are satisfied, then the updateXML is rewritten into a simple relational update. For example:

UPDATE po_tab p SET value(p) =

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

becomes:

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

Date datatypes such as DATE, gMONTH, gDATE, and so on, have different format in XML schema and SQL. In such cases, if the updateXML() has a string value for these columns, the rewrite automatically puts the XML format string to convert the string value correctly. Thus string value specified for DATE columns, must match the XML date format and not the SQL DATE format.

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

Ordered Collections in Tables (OCTs)

Arrays in XML schemas (elements with maxOccurs > 1) are usually stored in VARRAYs, which can be stored either in a Large Object (LOB) or in a separate store table, similar to a nested table.


Note:

When elements of a VARRAY are stored in a separate table, the VARRAY is referred to as an Ordered Collection in Tables (OCT). In the following paragraphs, references to OCT also assume that you are using Index Organized Table (IOT) storage for the "store" table.


This allows the elements of a VARRAY to reside in a separate table based on an IOT. The primary key of the table is (NESTED_TABLE_ID, ARRAY_INDEX). NESTED_TABLE_ID is used to link the element with their containing parents while the ARRAY_INDEX column keeps track of the position of the element within the collection.

Using OCT for VARRAY Storage

There are two ways to specify an OCT storage:

The advantages of using OCTs for VARRAY storage include faster access to elements and better queryability. Indexes can be created on attributes of the element and these can aid in better execution for query rewrite.

Cyclical References Between XML Schemas

XML schema documents can have cyclic dependencies that can prevent them from being registered one after the other in the usual manner. Examples of such XML schemas follow:

Example 5-37 Cyclic Dependencies

An XML schema that includes another xml schema cannot be created if the included xml schema does not exist.

begin dbms_xmlschema.registerSchema('xm40.xsd',
'<schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:my="xm40" 
targetNamespace="xm40">
  <include schemaLocation="xm40a.xsd"/>
  <!-- Define a global complextype here -->
  <complexType name="Company">
    <sequence>
      <element name="Name" type="string"/>
      <element name="Address" type="string"/>
    </sequence>
  </complexType>
  <!-- Define a global element depending on included schema -->
  <element name="Emp" type="my:Employee"/>
</schema>',
true, true, false, true); end;
/

It can however be created with the FORCE option:

begin dbms_xmlschema.registerSchema('xm40.xsd',

'<schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:my="xm40" 
targetNamespace="xm40">
  <include schemaLocation="xm40a.xsd"/>
  <!-- Define a global complextype here -->
  <complexType name="Company">
    <sequence>
      <element name="Name" type="string"/>
      <element name="Address" type="string"/>
    </sequence>
  </complexType>
  <!-- Define a global element depending on included schema -->
  <element name="Emp" type="my:Employee"/>
</schema>',
true, true, false, true, true); end;
/

Attempts to use this schema and recompile will fail:

create table foo of sys.xmltype xmlschema "xm40.xsd" element "Emp";

Now create the second XML schema with FORCE option. This should also make the first XML schema valid:

begin dbms_xmlschema.registerSchema('xm40a.xsd',
'<schema xmlns="http://www.w3.org/2001/XMLSchema" xmlns:my="xm40" 
targetNamespace="xm40">
  <include schemaLocation="xm40.xsd"/>
  <!-- Define a global complextype here -->
  <complexType name="Employee">
    <sequence>
      <element name="Name" type="string"/>
      <element name="Age" type="positiveInteger"/>
      <element name="Phone" type="string"/>
    </sequence>
  </complexType>
  <!-- Define a global element depending on included schema -->
  <element name="Comp" type="my:Company"/>
</schema>',
true, true, false, true, true); end;
/

Both XML schemas can be used to create tables, and so on:

create table foo of sys.xmltype xmlschema "xm40.xsd" element "Emp";
create table foo2 of sys.xmltype xmlschema "xm40a.xsd" element "Comp";
 

To register both these XML schemas which have a cyclic dependency on each other, you must use the FORCE parameter in DBMS_XMLSCHEMA.registerSchema as follows:

  1. Step 1: Register "s1.xsd" in FORCE mode:
    dbms_xmlschema.registerSchema("s1.xsd", "<schema ...", ..., force => true)
    
    

    At this point, s1.xsd is invalid and cannot be used.

  2. Step 2: Register "s2.xsd" in FORCE mode:
    dbms_xmlschema.registerSchema("s2.xsd", "<schema ..", ..., force => true)
    
    

    The second operation automatically compiles s1.xsd and makes both XML schemas valid.

See Figure 5-8. The preceding example is illustrated in the lower half of the figure.

Figure 5-8 Cyclical References Between XML Schemas

Text description of adxdb013.gif follows
Text description of the illustration adxdb013.gif


Frequently Asked Questions (FAQs): XML DB, XML Schema-Based Issues

Why Do I Appear to get Memory Leaks When Using Bind Variables for XPath Expressions?

Consider the following simple XML document that is blown up to 3.6 Megabytes:

<?xml version="1.0"?>
<PurchaseOrder xmlns="http://www.vector.com/po.xsd"
xmlns:xdb="http://xmlns.oracle.com/xdb"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://www.vector.com/po.xsd
http://www.vector.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>
     <Item>
       <Part>7i Doc Set</Part>
       <Price>50</Price>
     </Item>
 </PurchaseOrder>

This document is stored in an XMLTYPE table object-relationally. The XML schema was not annotated.

 <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
 <xsd:complexType name="PurchaseOrderType">
   <xsd:sequence>
    <xsd:element name="PONum" type="xsd:decimal"/>
    <xsd:element name="Company">
     <xsd:simpleType>
      <xsd:restriction base="xsd:string">
       <xsd:maxLength value="100"/>
      </xsd:restriction>
     </xsd:simpleType>
    </xsd:element>
   <xsd:element name="Item" maxOccurs="2147483647">
     <xsd:complexType>
      <xsd:sequence>
...

Name                                      Null?    Type
----------------------------------------- -------- -------------------
FILENAME                                  NOT NULL VARCHAR2(20)
CONTENT                                   NOT NULL
XMLTYPE(XMLSchema "http://www.vector.com/po.xsd" 
Element "PurchaseOrder") STORAGE Object-relational TYPE
 "PurchaseOrderType1627_T"

The statement:

SQL> select existsnode(srp.content,'/PurchaseOrder/Item[Part="7i Doc Set"]')
     into :i from xmltable srp where filename='po6.xml';

takes about 6 seconds on my laptop. When I use a bind variable such as:

SQL> var xpath varchar2(50)
SQL> exec :xpath:='/PurchaseOrder/Item[Part="7i Doc Set"]'

PL/SQL procedure successfully completed.
SQL> select existsnode(srp.content,:xpath) into :i from xmltable srp
     where filename='po6.xml';

I wait ...The statement hangs; the CPU is busy by 100% and the memory consumption is high:

Answer: If you use bind variables, Oracle does not rewrite the query, hence you are seeing a full function-based XPath versus a relational rewrite.

Question 2: We need bind variables for SQL sharing. What happens if you set the CURSOR_SHARING to FORCE?

Answer 2: Basically, query rewrite means that Oracle is changing the input XPath expression into some underlying columns. This means that for a given XPath, there is a particular set of columns/tables,... that will be referenced underneath. This has to be a compile time operation, since the shared cursor has to know exactly which tables and columns, .... it references. This cannot change with each row or instantiation of the cursor.

So if the XPath expression itself is a bind variable, Oracle cannot do any rewrite, since each instantiation of the cursor can have totally different XPath. This is akin to the user binding the name of the table/column in a SQL query. For example, SELECT * FROM table(:1).


Note:

You can specify bind variables in the right-hand side of the query and that would work fine. For example:

SELECT * FROM purchaseorder p WHERE extractvalue(value(p),'/PurchaseOrder/LineItems/LineItem/ItemNumber') = :1;

would use Oracle's usual bind variable sharing. ...


When CURSOR_SHARING is set to FORCE, by default all string constants including XPath become a bind variable. At that time when Oracle encounters extractvalue(),existsnode(),..., Oracle look at the XPath bind variables to check if they are really constants. If so Oracle uses them and rewriteS the query.

Hence there the big difference inwhere the bind variable is used.

How Do I Check that Query Rewrite is Working Correctly?

I have a question about query rewrite with XML schema-Based object-relational storage. How can I check that query rewrite is working correctly? Should I use an SQL trace, events, and so on?

Answer: You can check query rewrites in two ways:

Question 2: According to your suggestions,I used event 19021. Here is the test case I used:

1--set event
SQL> alter session set events '19021 trace name context forever, level 2';
Session altered.
2--extract function used

I tried this with both XML schema-based and non-schema-based.

For XML schema-based object-relational storage:

SQL> SELECT value(x).extract('/a:PO/Company',
   2  'xmlns:a="http://www.oracle.com/PO"')
   3  FROM po_tab x;

But I get the following error:

ERROR:
ORA-19022: XML XPath functions are disabled
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at line 1

For non-schema-based CLOB storage:

SQL> SELECT extract(value(p),'/PO/PODATE')
   2  FROM po_tab p;

results in the following error:

ERROR:
ORA-19022: XML XPath functions are disabled

This result indicates that query rewrite does not occur. Are there other ways to check query rewrite?

Answer 2: You are doing the right thing. Setting event 19021 turns off functional evaluation, so that all XMLType functions will be turned off, and if query rewrite does not happen, you will get error ORA-19022 (XML XPath functions are disabled).

The reason the second example (non-schema based) is not working is that query rewrite can happen only for non-schema-based (NSB) XMLType views (XVs), defined over objects. It does not happen for non-schema-based XMLType tables, since the storage is CLOB-based.

The reason the first example (schema-based) is not working is probably the namespace parameter.

Query rewrite does not currently function for extract() or existsNode() XMLType methods. You can however use the operator equivalents instead of the XMLType methods. For example, use extract() operator instead of xmltype.extract() method.

Why Does the XML DB Query Not Use My Index?

I ran the demo script that orchestrates running all the other scripts. It creates an index like this:

create index director_name on movies( 
extractValue(movieDoc,'/Movie/Director/Last') );

But when I try an Explain Plan on this query:

SELECT extractValue(movieDoc,'/Movie/@Title')
FROM movies
 WHERE extractValue(movieDoc,'/Movie/Director/Last') = 'Minghella'

It does not appear to use my index. Here is the EXPLAIN plan from the JDeveloper9i Explain Plan GUI:

SELECT STATEMENT
  - Filter
    - Table Access (FULL) SCOTT.MOVIES
    - Collection Iterator (PICKLER FETCH)

Is this because I do not have enough movies in my table, so the Optimizer decides that a full table scan is fastest? I also tried:

SELECT /*+ INDEX(movies director_name) */ 
extractValue(movieDoc,'/Movie/@Title')
  FROMmovies
 WHERE extractValue(movieDoc,'/Movie/Director/Last') = 'Minghella'

but it still does a full table scan of MOVIES.

Answer: When you create a non-schema-based index on XMLType, the indexes end up as function-based indexes. You can check user_functional_indexes. For a function-based index, the string must match exactly and you need to use ALTER SESSION as follows:

ALTER SESSION SET query_rewrite_enabled=true
ALTER SESSION SET  query_Rewrite_integrity=trusted

for it to detect the indexes.

How Do I Specify Attributes in a complexType XML Schema Declaration?

Answer: If you have an element based on a global complexType, the SQLType (and SQLSchema) attributes should 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, XML DB creates an SQLType with an internally generated name. The elements that reference this global type cannot then have a different value for SQLType. In other words, this 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 okay:

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

Why Do the XML Schema and Element Not Match?

Given this table definition

SQL> describe "rechnung";

Name                     Null?        Type
------------------------ -----        ---------------
ID                       NOT NULL     NUMBER(10)

rechnung

SYS.XMLTYPE(XMLSchema "http://cczarski.de.oracle.com/Rec                              
hnung/Test001.xsd"
Element "rechnung") STORAGE Object-relational TYPE "RECHNUNG_T"
DATUM DATE

And this schema

<?xml version="1.0" encoding="iso-8859-1"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://cczarski.de.oracle.com/Rechnung/Test001.xsd"
            xmlns:xdb="http://xmlns.oracle.com/xdb"
xmlns:rechn="http://cczarski.de.oracle.com/Rechnung/Test001.xsd"
            elementFormDefault="qualified"
            version="1.0">
<!-- Zundchst wird der Kunde definiert --

Why does inserting this document:

<rechnung xmlns="http://.../Test001.xsd" xmlns:xsi="
http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://cczarski.d
e.oracle.com/Rechnung/Test001.xsd">
        <kunde>
...

Report

ERROR at line 2:
ORA-19007: Schema and element do not match

Answer: xsi:schemaLocation takes two parameters: "NS SchemaURL"

try:

xsi:schemaLocation="http://cczarski.de.oracle.com/Rechnung/Test001.xsd
http://cczarski.de.oracle.com/Rechnung/Test001.xsd">

How Do I Pull a Stylesheet From RESOURCE_VIEW [S/MIME]?

I am having trouble pulling out the saved stylesheet from RESOURCE_VIEW with the following statement:

SELECT EXTRACT
  (rtab.res,
   'r:Resource/r:Contents/node()/xsl:stylesheet',
   'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd" '||
   'xmlns:xdb="http://xmlns.oracle.com/xdb" ' ||
   'xmlns:xsl="http://www.w3.org/1999/XSL/Transform"'
  ).getclobval()
  FROM resource_view rtab
  WHERE rtab.any_path =
   '/public/spec_proto/XDB_Stylesheet_Render_XML.xsl'
/

Did I err in terms of the namespace?

Answer: Do you have the XSL schema registered? At this time, extracting from resource contents will not work unless the contents are from a registered XML schema.

Why for Our XML Parser Does selectSingleNode return NULL When the xmlns Attribute is Added?

Our code parses an XML file that is an instance of our own XML schema ApplicationStructure. It works fine, until we add xmlns="http://www.oracle.com/JHeadstart/ApplicationStructure" in the top-level tag. Then the call to selectSingleNode suddenly returns NULL. If we remove the xmlns attribute, selectSingleNode again returns the node we want. Our code:

import oracle.xml.parser.v2.*;
...
private XMLDocument mXmldoc;
mXmldoc = XMLLoader.getXMLDocument(mSource);
// Select Service node
XMLNode serviceNode = (XMLNode)mXmldoc.selectSingleNode("Service");

What are we doing wrong?

It seems that there is another constructor of selectSingleNode, which accepts a second parameter NSResolver. How must we use this and can we make it work with XML files with the xmlns attribute and without it?

We would like to be able to use the xmlns attribute, so that we can benefit from the JDeveloper9i Code Insight for our own registered XML Schema.

Answer: XPath has no syntax for searching on elements that are qualified with the default namespace. The XPath pattern "foo" always searches for the element <foo> with a null namespace. So that even though the default namespace *SYNTACTICALLY* allows you the convenience of writing:

<foo xmlns="urn:mynamespace"/>

To the XML Parser internally, this is an Element named: <{urn:mynamespace}:foo> and not just:

<foo>

So, moral of the story, is that to search for anything with a namespace URI, including default namespace, use the following:

Here's an example:

package test;
import oracle.xml.parser.v2.*;
import org.w3c.dom.*;
import java.io.*;
public class Demo {
  private static final String URI =
"http://www.oracle.com/JHeadstart/ApplicationStructure";
  private static final String TESTDOC =
  "<foo xmlns='"+URI+"'/>";
  private static final NSResolver nsr = new MyNSResolver();
  public static void main(String[] args) throws Throwable {
    System.out.println("Document to parse is");
    System.out.println(TESTDOC);
    DOMParser dp = new DOMParser();
    dp.parse( new StringReader(TESTDOC));
    XMLDocument doc = dp.getDocument();
    Node n = doc.selectSingleNode("xxx:foo", nsr); // Provide NSResolver!
    System.out.println( "Found " + ((n!=null) ? " it! " : " nothing"));
  }

  static class MyNSResolver implements NSResolver {
    public String resolveNamespacePrefix(String pref) {
      if (pref.equals("xxx")) return URI;
      else return null;
    }
  }
}

Why Do I Get Error ORA-19007: Schema and Element Do Not Match?

This script runs successfully until I insert sample data into the table. It then fails with ORA-19007: Schema and element do not match. It should work:

---- testPo.sql
set serverout on

drop table po_tab1;

declare
    urlvar  varchar2(100);
    xsdfile varchar2(2000);
begin
    urlvar := 'http://www.oracle.com/PO.xsd';

   --         xmlns:po="http://www.oracle.com/PO.xsd">
    xsdfile :=
    '<schema xmlns="http://www.w3.org/2001/XMLSchema"
        targetNamespace="http://www.oracle.com/PO.xsd"
        xmlns:po="http://www.oracle.com/PO.xsd">
        <complexType name="PurchaseOrderType">
          <sequence>
            <element name="PONum" type="decimal"/>
            <element name="Company" type="string"/>
            <element name="Item" maxOccurs="1000">
              <complexType>
                <sequence>
                  <element name="Part" type="string"/>
                  <element name="Price" type="decimal"/>
                </sequence>
              </complexType>
            </element>
          </sequence>
          <attribute name = "PurchaseDate" type = "date"/>
        </complexType>
        <element name="PurchaseOrder" type = "po:PurchaseOrderType"/>
      </schema>';

      begin

dbms_xmlschema.deleteschema(urlvar,dbms_xmlschema.delete_cascade_force);
      exception
         when others then null;
      end;

      dbms_xmlschema.registerschema(urlvar,xsdfile);
end;
/

set heading off
set pagesize 0
set long 10000
set maxdata 12000
set arraysize 1

select a.schema.getstringval() from user_xml_schemas a
where a.schema_url = 'http://www.oracle.com/PO.xsd';

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

insert into po_tab1 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"
     PurchaseDate="1967-08-13">
   <PONum>1</PONum>
   <Company>The Business</Company>
   <Item>
    <Part>Part 1</Part>
    <Price>1000</Price>
   </Item>
</PurchaseOrder>'));

select * from po_tab1;

Answer: The schemaLocation attribute should be a pair of <namespace> and <schemaloc> values as follows:

xsi:schemaLocation="http://www.oracle.com/PO.xsd
http://www.oracle.com/PO.xsd"

Is it Possible to Register XML Schema for Schemas?

I am trying to register the XML schema for schemas with something like. I have downloaded XMLSchema.xsd, XMLSchema.dtd, datatypes.dtd and made them available at my local webserver after checking that XMLSchema.xsd can be validated:

begin
  dbms_xmlschema.registeruri(    schemaURL =>
'http://www.denmark.dk/MD/XMLSchema'
                , schemaDocUri => 'http://144.21.226.78/XMLSchema.xsd'
    , local => false
                );
end;
 /
declare
*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00233: namespace prefixes starting with "xml" are reserved
Error at line 70
ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 0
ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 160
ORA-06512: at line 34

I need a table for storing all the XML schemas. The content of this table I want to be validated against schema for schemas as well as the built-in capability of searching through the object-relational structures afterwards in order to do analyses that are more complex. Is it possible to Register XML schema for schemas?

In the original XMLSchema.xsd no XML namespace is declared; hence I could not validate it in XMLSpy. The I added the xmlns:xml namespace declaration to validate success in XMLSpy, but I get an error.

Answer: It's illegal to have namespaces that start with "xml". Pick another namespace prefix such as, "foo" or "xsd" or "xs" or "x".