Skip Headers
Oracle® XML DB Developer's Guide
10g Release 2 (10.2)

Part Number B14259-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
Contact Us

Go to previous page
Previous
Go to next page
Next
View PDF

5 XML Schema Storage and Query: Basic

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.

This chapter provides basic information about using XML Schema with Oracle XML DB. It explains how to do all of the following:

See Also:

This chapter contains these topics:

Overview of 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, purchaseOrder.xsd, describes the structure and other properties of purchase-order XML documents.

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

Example 5-1 XML Schema Instance purchaseOrder.xsd

The following is an XML schema that declares a complexType called purchaseOrderType and a global element PurchaseOrder of this type. This is the same schema as Example 3-7, "Purchase-Order XML Schema, purchaseOrder.xsd", with the exception of the lines in bold here, which are additional. For brevity, part of the schema is omitted here (marked ...).

<xs:schema
  targetNamespace="http://xmlns.oracle.com/xdb/documentation/purchaseOrder"
  xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder"
  xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">
  <xs:element name="PurchaseOrder" type="po:PurchaseOrderType"/>
    <xs:complexType name="PurchaseOrderType">
      <xs:sequence>
        <xs:element name="Reference" type="po:ReferenceType"/>
        <xs:element name="Actions" type="po:ActionsType"/>
        <xs:element name="Reject" type="po:RejectionType" minOccurs="0"/>
        <xs:element name="Requestor" type="po:RequestorType"/>
        <xs:element name="User" type="po:UserType"/>
        <xs:element name="CostCenter" type="po:CostCenterType"/>
        <xs:element name="ShippingInstructions" 
                    type="po:ShippingInstructionsType"/>
        <xs:element name="SpecialInstructions" 
                    type="po:SpecialInstructionsType"/>
        <xs:element name="LineItems" type="po:LineItemsType"/>
        <xs:element name="Notes" type="po:NotesType"/>
      </xs:sequence>
    </xs:complexType>
    <xs:complexType name="LineItemsType">
      <xs:sequence>
        <xs:element name="LineItem" type="po:LineItemType" 
                    maxOccurs="unbounded"/>
      </xs:sequence>
    </xs:complexType>

    ...

    <xs:simpleType name="DescriptionType">
      <xs:restriction base="xs:string">
        <xs:minLength value="1"/>
        <xs:maxLength value="256"/>
      </xs:restriction>
    </xs:simpleType>
    <xs:simpleType name="NotesType">
      <xs:restriction base="xs:string">
        <xs:minLength value="1"/>
        <xs:maxLength value="32767"/>
      </xs:restriction>
    </xs:simpleType>
  </xs:schema>

Example 5-2 purchaseOrder.XML: Document That Conforms to purchaseOrder.xsd

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

<po:PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
  xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder" 
  xsi:schemaLocation=
    "http://xmlns.oracle.com/xdb/documentation/purchaseOrder
     http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd">
  <Reference>SBELL-2002100912333601PDT</Reference>
  <Actions>
    <Action>
      <User>SVOLLMAN</User>
    </Action>
  </Actions>
  <Reject/>
  <Requestor>Sarah J. Bell</Requestor>
  <User>SBELL</User>
  <CostCenter>S30</CostCenter>
  <ShippingInstructions>
    <name>Sarah J. Bell</name>
    <address>400 Oracle Parkway
             Redwood Shores
             CA
             94065
             USA
    </address>
    <telephone>650 506 7400</telephone>
  </ShippingInstructions>
  <SpecialInstructions>Air Mail</SpecialInstructions>
  <LineItems>
    <LineItem ItemNumber="1">
      <Description>A Night to Remember</Description>
      <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="2">
      <Description>The Unbearable Lightness Of Being</Description>
      <Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
    </LineItem>
    <LineItem ItemNumber="3">
      <Description>Sisters</Description>
      <Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
    </LineItem>
  </LineItems>
  <Notes>Section 1.10.32 of &quot;de Finibus Bonorum et Malorum&quot;, 
         written by Cicero in 45 BC
 
&quot;Sed ut perspiciatis unde omnis iste natus error sit voluptatem accusantium
 doloremque laudantium, totam rem aperiam, eaque ips
...
tiae consequatur, vel illum qui dolorem eum fugiat quo voluptas nulla
 pariatur?&quot;
 
1914 translation by H. Rackham
 
&quot;But I must explain to you how all this mistaken idea of denouncing
 pleasure and praising pain was born and I will give you a c
...
o avoids a pain that produces no resultant pleasure?&quot;
 
Section 1.10.33 of &quot;de Finibus Bonorum et Malorum&quot;, written by Cicero
in 45 BC
 
&quot;At vero eos et accusamus et iusto odio dignissimos ducimus qui blanditiis
 praesentium voluptatum deleniti atque corrupti quos
...
 delectus, ut aut reiciendis voluptatibus maiores alias
consequatur aut perferendis doloribus asperiores repellat.&quot;
 
1914 translation by H. Rackham
 
&quot;On the other hand, we denounce with righteous indignation and dislike men
 who are so beguiled and demoralized by the charms of
...
secure other greater pleasures, or else he endures pains to avoid worse
 pains.&quot;
  </Notes>
</po:PurchaseOrder>

Note:

The URL used here is simply a name that uniquely identifies the registered XML schema within the database: http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd. This need not be the physical URL at the which the XML schema document is located. The target namespace of the XML schema is another URL, different from the XML schema location URL, which 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. The target namespace is commonly the same as the URL of the XML schema.

An XML instance document must specify the namespace of the root element (same as the target namespace of the XML schema) 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 schema URL.

Using Oracle XML DB and XML Schema

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

When using Oracle XML DB with XML Schema, you must first register the XML schema. You can then use the XML schema URLs while creating XMLType tables, columns, and views. The XML schema URL, in other words, the URL that identifies the XML schema in the database, is associated with parameter schemaurl of PL/SQL procedure DBMS_XMLSCHEMA.registerSchema.

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

Why We Need XML Schema

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

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

XML Schema Provides Flexible XML-to-SQL Mapping Setup

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

  • If your data is highly structured (mostly XML), then each element in the XML documents can be stored as a column in a table.

  • If your data is unstructured (all or most is not XML data), then the data can be stored in a Character Large Object (CLOB).

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

XML Schema Allows XML Instance Validation

Another advantage of using XML Schema with Oracle XML DB is that you can perform XML instance validation according to XML schemas 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 schemas in Oracle XML DB, when inserting and storing XML instances using protocols such as FTP or HTTP(S), 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 schemas can be useful in predicting optimum storage, fidelity, and access.

DTD Support in Oracle XML DB

A DTD is a set of rules that define the allowable structure of an XML document. DTDs are text files that derive their format from SGML and can be associated with an XML document either by using the DOCTYPE element or by using an external file through a DOCTYPE reference. In addition to supporting XML Schema, which provides 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 Oracle XML DB 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 path /public/flights.xsd.

Managing XML Schemas with DBMS_XMLSCHEMA

Before an XML schema can be used by Oracle XML DB, it must be registered with Oracle Database. You register an XML schema using the PL/SQL package DBMS_XMLSCHEMA.

Some of the main DBMS_XMLSCHEMA procedures are these:

Registering an XML Schema

The main arguments to procedure DBMS_XMLSCHEMA.registerSchema are these:

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

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

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

Example 5-3 Registering an XML Schema with DBMS_XMLSCHEMA.REGISTERSCHEMA

The following code registers the XML schema at URL http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd. This example shows how to register an XML schema using the BFILE mechanism to read the source document from a file on the local file system of the database server.

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

Schema Registration Considerations

When you register an XML schema, keep in mind the following considerations:

  • The act of registering a schema has no effect on the status of any instance documents already loaded into Oracle XML DB Repository that claim to be members of the class defined the schema.

    Because the schema they reference was not yet registered, such instance documents were non-schema-based when they were loaded. They remain non-schema-based after the schema is registered.

    You must delete such instance documents, and reload them after registering the schema, in order to obtain schema-based documents.

Storage and Access Infrastructure

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

  • Creating types: When an XML schema is registered, Oracle Database creates the appropriate SQL object types that enable the structured storage of XML documents that conform to this XML schema. You can use XML-schema annotations to control how these object types are named and generated. See "SQL Object Types" for details.

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

After registration has completed:

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

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

Transactional Action of XML Schema Registration

Registration of an XML schema is non-transactional and auto-committed, as follows:

  • If registration succeeds, then the operation is auto-committed.

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

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

Managing and Storing XML Schemas

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

The XML schema for XML schemas is called the root XML Schema, XDBSchema.xsd. XDBSchema.xsd describes any valid XML schema document that can be registered by Oracle XML DB. You can access XDBSchema.xsd through Oracle XML DB Repository at /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBSchema.xsd.

Debugging XML Schema Registration

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

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

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

SQL Object Types

Assuming that the parameter GENTYPES is set to TRUE when an XML schema is registered, Oracle XML DB creates the appropriate SQL object types that enable structured storage of XML documents that conform to this XML schema. By default, all SQL object types are created in the database schema of the user who registers the XML schema. If the defaultSchema annotation is used, then Oracle XML DB attempts to create the object type using the specified database schema. The current user must have the necessary privileges to perform this.

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

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

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

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

Note:

By default, the names of the object types and attributes in the preceding example are system-generated.
  • Developers can use XML-schema annotations to provide user-defined names (see "Oracle XML Schema Annotations" for details).

  • If the XML schema does not contain the SQLName attribute, then the name is derived from the XML name.

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 and protocols that do not have any table specification, such as FTP or HTTP(S). In such cases, the XML instance is inserted into the default table.

Example 5-5 Default Table for Global Element PurchaseOrder

DESCRIBE "purchaseorder1669_tab"

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

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

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

Generated Names are Case Sensitive

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

Objects That Depend on Registered XML Schemas

The following objects are dependent on registered XML schemas:

  • Tables or views that have an XMLType column that conforms to some element in the XML schema.

  • XML schemas that include or import this schema as part of their definition.

  • Cursors that reference the XML schema name, for example, within functions of package DBMS_XMLGEN. Such cursors are purely transient objects.

How to Obtain a List of Registered XML Schemas

To obtain a list of the XML schemas registered with Oracle XML DB using DBMS_XMLSCHEMA.registerSchema, use the code in Example 5-6. You can also examine USER_XML_SCHEMAS, ALL_XML_SCHEMAS, USER_XML_TABLES, and ALL_XML_TABLES.

Example 5-6 Data Dictionary Table for Registered Schemas

DESCRIBE DBA_XML_SCHEMAS

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

SELECT OWNER, LOCAL, SCHEMA_URL FROM DBA_XML_SCHEMAS;

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

13 rows selected.

DESCRIBE DBA_XML_TABLES

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

SELECT TABLE_NAME FROM DBA_XML_TABLES
  WHERE XMLSCHEMA = 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd';

TABLE_NAME
---------------------
PurchaseOrder1669_TAB

1 row selected.

Deleting an XML Schema with DBMS_XMLSCHEMA.DELETESCHEMA

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

  • That the current user has the appropriate privileges (ACLs) to delete the resource corresponding to the XML schema within Oracle XML DB Repository. You can thus control which users can delete which XML schemas, by setting the appropriate ACLs on the XML Schema resources.

  • For dependents. If there are any dependents, then it raises an error and the deletion operation fails. This is referred to as the RESTRICT mode of deleting XML Schemas.

FORCE Mode

When deleting XML Schemas, if you specify the FORCE mode option, then the XML Schema deletion proceeds even if it fails the dependency check. In this mode, XML Schema deletion marks all its dependents as invalid.

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

Example 5-7 Deleting an XML Schema with DBMS_XMLSCHEMA.DELETESCHEMA

The following example deletes XML schema purchaseOrder.xsd. Then, the schema is deleted using the FORCE and CASCADE modes with DBMS_XMLSCHEMA.DELETESCHEMA:

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

XML Schema-Related Methods of XMLType

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

Table 5-1 XMLType Methods Related to XML Schema

XMLType Method Description
isSchemaBased()

Returns TRUE if the XMLType instance is based on an XML schema, FALSE otherwise.

getSchemaURL() 
getRootElement()
getNamespace()

Return the XML schema URL, name of root element, and the namespace for an XML schema-based XMLType instance, respectively.

schemaValidate()
isSchemaValid()
isSchemaValidated()
setSchemaValidated()

An XMLType instance can be validated against a registered XML schema using these validation methods. See Chapter 9, "Transforming and Validating XMLType Data".


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 to Oracle XML DB Repository. The XML schema URL determines the path name of the resource in the repository (and is associated with the SCHEMAURL parameter of registerSchema) according to the following rules:

Local XML Schema

By default, an XML schema belongs to you after registering the XML schema with Oracle XML DB. A reference to the XML schema document is stored in Oracle XML DB Repository. Such XML schemas are referred to as local. In general, they are usable only by you, the owner.

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

Example 5-8 Registering A Local XML Schema

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

If this local XML schema is registered by user SCOTT, it is given this path name:

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

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

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 Database supports fully qualified XML schema URLs, which can be specified as:
http://xmlns.oracle.com/xdb/schemas/SCOTT/xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd

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

Global XML Schema

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

Global XML 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.

Role XDBAdmin also provides write access to this directory, assuming that it is protected by the default protected Access Control Lists (ACL). See Chapter 24, "Repository Resource Security" for further information on privileges and for details on role XDBAdmin.

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-9 Registering A Global XML Schema

GRANT XDBADMIN TO SCOTT;

Grant succeeded.

CONNECT scott/tiger

Connected.

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

If this global XML schema is registered by user SCOTT, it is given this path name:

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

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

DOM Fidelity

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

How Oracle XML DB Ensures DOM Fidelity with XML Schema

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

  • Comments

  • Namespace declarations

  • Prefix information

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

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

DOM Fidelity and SYS_XDBPD$

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

The positional descriptor attribute stores all information that cannot be stored in any of the other attributes. PD information is used to ensure the DOM fidelity of all XML documents stored in Oracle XML DB. Examples of such information include: ordering information, comments, processing instructions, and namespace prefixes.

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

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, it is not a good idea to suppress the PD attribute, because the extra information, such as comments and processing instructions, could be lost if there is no PD column.

Creating XMLType Tables and Columns Based on XML Schema

Using Oracle XML DB, developers can create XMLType tables and columns that are constrained to a global element defined by a registered XML schema. After an XMLType column has been constrained to a particular element and a particular XML schema, it can only contain documents that are compliant with the schema definition of that element. An XMLType table column is constrained to a particular element and a particular XML schema by adding the appropriate XMLSCHEMA and ELEMENT clauses to the CREATE TABLE operation.

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

CREATE [GLOBAL TEMPORARY] TABLE [schema.] table OF XMLType
  [(object_properties)] [XMLType XMLType_storage] [XMLSchema_spec]
  [ON COMMIT {DELETE | PRESERVE} ROWS] [OID_clause] [OID_index_clause]
  [physical_properties] [table_properties];

Figure 5-1 Creating an XMLType Table

Description of Figure 5-1 follows
Description of "Figure 5-1 Creating an XMLType Table"

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. See also Chapter 4, "XMLType Operations".

Example 5-10 Creating XML Schema-Based XMLType Tables and Columns

This example shows CREATE TABLE statements. The first creates an XMLType table, purchaseorder_as_table. The second creates a relational table, purchaseorder_as_column, with an XMLType column, xml_document. In both, the XMLType value is constrained to the PurchaseOrder element defined by the schema registered under the URL http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd.

CREATE TABLE purchaseorder_as_table OF XMLType
  XMLSCHEMA "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd"
  ELEMENT "PurchaseOrder";

CREATE TABLE purchaseorder_as_column (id NUMBER, xml_document XMLType)
  XMLTYPE COLUMN xml_document
  ELEMENT
    "http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd#PurchaseOrder";

There are two ways to specify the XMLSchema and Element:

  • as separator clauses

  • using the Element clause with an XPointer notation

The data associated with an XMLType table or column that is constrained to an XML schema can be stored in two different ways:

  • Shred the contents of the document and store it as a set of objects. This is known as structured storage.

  • Stored the contents of the document as text, using a single LOB column. This is known as unstructured storage.

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

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

Example 5-11 Specifying CLOB Storage for Schema-Based XMLType Tables and Columns

This example shows how to create an XMLType table and a table with an XMLType column, where the contents of the XMLType are constrained to a global element defined by a registered XML schema, and the contents of the XMLType are stored using a single LOB column.

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

You can add LOB storage parameters to the STORE AS CLOB clause.

Specifying Storage Models for Structured Storage of Schema-Based XMLType

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

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

The collection and the LOB column must be identified using object-relational notation.

Example 5-12 Specifying Storage Options for Schema-Based XMLType Tables and Columns

This example shows how to create an XMLType table and a table with an XMLType column, where the contents of the XMLType are constrained to a global element defined by a registered XML schema, and the contents of the XMLType are stored using as a set of SQL objects.

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

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

The example also shows how to specify that the collection of Action elements and the collection of LineItem elements are stored as rows in nested tables, and how to specify LOB storage clauses for the LOB that will contain the content of the Notes element.

Note:

Use the thick JDBC driver with schema-based XMLType values stored object-relationally. (You can use either the thin or the thick driver with CLOB storage of XMLType values.)

Specifying Relational Constraints on XMLType Tables and Columns

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

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

Oracle XML Schema Annotations

Oracle XML DB gives application developers the ability to influence the objects and tables that are generated by the XML schema registration process. You use the schema annotation mechanism to do this.

Annotation involves adding extra attributes to the complexType, element, and attribute definitions that are declared by the XML schema. The attributes used by Oracle XML DB belong to the namespace http://xmlns.oracle.com/xdb. In order to simplify the process of annotationg an XML schema, it is recommended that a namespace prefix be declared in the root element of the XML schema.

Common reasons for wanting to annotate an XML schema include the following:

The most commonly used annotations are the following:

Note:

Annotation storeVarrayAsTable="true" causes element collections to be persisted as rows in an index-organized table (IOT). Oracle Text does not support IOTs. Do not use this annotation if you will need to use Oracle Text indexes for text-based ora:contains searches over a collection of elements. See "ora:contains Searches Over a Collection of Elements". To provide for searching with Oracle Text indexes:
  1. Set genTables="false" during schema registration.

  2. Create the necessary tables manually, without using the clause ORGANIZATION INDEX OVERFLOW, so the tables will be heap-organized instead of index-organized (IOT).

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

Example 5-13 shows a partial listing of the XML schema in Example 5-1, modified to include some of the most important XDB annotations.

Example 5-13 Using Common Schema Annotations

<xs:schema
  targetNamespace="http://xmlns.oracle.com/xdb/documentation/purchaseOrder"
  xmlns:xs="http://www.w3.org/2001/XMLSchema"
  xmlns:xdb="http://xmlns.oracle.com/xdb"
  xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder"
  version="1.0" 
  xdb:storeVarrayAsTable="true">  <xs:element name="PurchaseOrder" type="po:PurchaseOrderType"
              xdb:defaultTable="PURCHASEORDER"/>
  <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T">
    <xs:sequence>
      <xs:element name="Reference" type="po:ReferenceType" minOccurs="1"
                  xdb:SQLName="REFERENCE"/>
      <xs:element name="Actions" type="po:ActionsType"
                  xdb:SQLName="ACTION_COLLECTION"/>
      <xs:element name="Reject" type="po:RejectionType" minOccurs="0"/>
      <xs:element name="Requestor" type="po:RequestorType"/>
      <xs:element name="User" type="po:UserType" minOccurs="1"
                  xdb:SQLName="EMAIL"/>
      <xs:element name="CostCenter" type="po:CostCenterType"/>
      <xs:element name="ShippingInstructions"
                  type="po:ShippingInstructionsType"/>
      <xs:element name="SpecialInstructions" type="po:SpecialInstructionsType"/>
      <xs:element name="LineItems" type="po:LineItemsType"
                  xdb:SQLName="LINEITEM_COLLECTION"/>
      <xs:element name="Notes" type="po:NotesType" xdb:SQLType="CLOB"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T">
    <xs:sequence>
      <xs:element name="LineItem" type="po:LineItemType" maxOccurs="unbounded"
                  xdb:SQLCollType="LINEITEM_V" xdb:SQLName="LINEITEM_VARRAY"/>
   </xs:sequence>
  </xs:complexType>
  <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T">
    <xs:sequence>
      <xs:element name="Description" type="po:DescriptionType"/>
      <xs:element name="Part" type="po:PartType"/>
    </xs:sequence>
    <xs:attribute name="ItemNumber" type="xs:integer"/>
  </xs:complexType>
  <xs:complexType name="PartType" xdb:SQLType="PART_T" xdb:maintainDOM="false">
    <xs:attribute name="Id">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:minLength value="10"/>
          <xs:maxLength value="14"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="Quantity" type="po:moneyType"/>
    <xs:attribute name="UnitPrice" type="po:quantityType"/>
  </xs:complexType>
</xs:schema>

Note:

As always:
  • SQL is case-insensitive, but names in SQL code are implicitly uppercase, unless you enclose them in double-quotes.

  • XML is case-sensitive. You must refer to SQL names in XML code using the correct case: uppercase SQL names must be written as uppercase.

For example, if you create a table named my_table in SQL without using double-quotes, then you must refer to it in XML as "MY_TABLE".

The schema element includes the declaration of the xdb namespace. It also includes the annotation xdb:storeVarrayAsTable="true". This causes all collections within the XML schema to be managed using nested tables.

The definition of the global element PurchaseOrder includes a defaultTable annotation that specifies that the name of the default table associated with this element is purchaseorder.

The global complexType PurchaseOrderType includes a SQLType annotation that specifies that the name of the generated SQL object type will be purchaseorder_t. Within the definition of this type, the following annotations are used:

The global complexType LineItemsType includes a SQLType annotation that specifies that the names of generated SQL object type will be lineitems_t. Within the definition of this type, the following annotations are used:

The global complexType LineItemType includes a SQLType annotation that specifies that the names of generated SQL object type will be lineitem_t.

The global complexType PartType includes a SQLType annotation that specifies that the names of generated SQL object type will be part_t. It also includes the annotation xdb:maintainDOM="false", specifying that there is no need for Oracle XML DB to maintain DOM fidelity for elements based on this type.

Example 5-14 Results of Registering an Annotated XML Schema

The following code shows some of the tables and objects created when the annotated XML schema is registered.

BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    schemaurl => 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd',
    schemadoc => bfilename('XMLDIR', 'purchaseOrder.Annotated.xsd'),
    local => TRUE,
    gentypes => TRUE,
    gentables => TRUE,
    CSID => nls_charset_id('AL32UTF8'));
END;
/
 
SELECT table_name, xmlschema, element_name FROM user_xml_tables;
 
TABLE_NAME     XMLSCHEMA                             ELEMENT_NAME
-------------  -----------------------------------   -------------
PURCHASEORDER  http://xmlns.oracle.com/xdb/documen   PurchaseOrder
               tation/purchaseOrder.xsd              
 
1 row selected.
 
DESCRIBE purchaseorder

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

PURCHASEORDER_T is NOT FINAL
Name                 Null? Type
-------------------- ----- --------------------------
SYS_XDBPD$                 XDB.XDB$RAW_LIST_T
REFERENCE                  VARCHAR2(30 CHAR)
ACTION_COLLECTION          ACTIONS_T
REJECT                     REJECTION_T
REQUESTOR                  VARCHAR2(128 CHAR)
EMAIL                      VARCHAR2(10 CHAR)
COSTCENTER                 VARCHAR2(4 CHAR)
SHIPPINGINSTRUCTIONS       SHIPPING_INSTRUCTIONS_T
SPECIALINSTRUCTIONS        VARCHAR2(2048 CHAR)
LINEITEM_COLLECTION        LINEITEMS_T
Notes                      CLOB

DESCRIBE lineitems_t
LINEITEMS_T is NOT FINAL
Name                 Null? Type
-------------------- ----- --------------------------
SYS_XDBPD$                 XDB.XDB$RAW_LIST_T
LINEITEM_VARRAY            LINEITEM_V

DESCRIBE lineitem_v

LINEITEM_V VARRAY(2147483647) OF LINEITEM_T
LINEITEM_T is NOT FINAL
Name                 Null? Type
-------------------- ----- --------------------------
SYS_XDBPD$                 XDB.XDB$RAW_LIST_T
ITEMNUMBER                 NUMBER(38)
DESCRIPTION                VARCHAR2(256 CHAR)
PART                       PART_T

DESCRIBE part_t
 
PART_T is NOT FINAL
Name                 Null? Type
-------------------- ----- --------------------------
ID                         VARCHAR2(14 CHAR)
QUANTITY                   NUMBER(12,2)
UNITPRICE                  NUMBER(8,4)

SELECT table_name, parent_table_column FROM user_nested_tables
  WHERE parent_table_name = 'purchaseorder';

TABLE_NAME                       PARENT_TABLE_COLUMN
----------                       -----------------------   
SYS_NTNOHV+tfSTRaDTA9FETvBJw==   "XMLDATA"."LINEITEM_COLLECTION"."LINEITEM_VARRAY"
SYS_NTV4bNVqQ1S4WdCIvBK5qjZA==   "XMLDATA"."ACTION_COLLECTION"."ACTION_VARRAY"
 
2 rows selected.

A table called purchaseorder has been created.

Types called purchaseorder_t, lineitems_t, lineitem_v, lineitem_t, and part_t have been created. The attributes defined by these types are named according to supplied the SQLName annotations.

The Notes attribute defined by purchaseorder_t has a datatype of CLOB.

Type part_t does not include a Positional Descriptor attribute.

Nested tables have been created to manage the collections of LineItem and Action elements.

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

Table 5-2 Annotations 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 as a collection, if maxOccurs > 1).

If false, a REF value is stored (or a collection of REF values, if maxOccurs>1). This attribute is forced to false in certain situations, such as cyclic references, where SQL does 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 action 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 and protocols where table name is not specified, such as FTP and HTTP(S).


Table 5-3 Annotations 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.

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 action as the input.


Table 5-4 Annotations 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 a BLOB value, 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.


Note:

Annotation storeVarrayAsTable="true" causes element collections to be persisted as rows in an index-organized table (IOT). Oracle Text does not support IOTs. Do not use this annotation if you will need to use Oracle Text indexes for text-based ora:contains searches over a collection of elements. See "ora:contains Searches Over a Collection of Elements". To provide for searching with Oracle Text indexes:
  1. Set genTables="false" during schema registration.

  2. Create the necessary tables manually, without using the clause ORGANIZATION INDEX OVERFLOW, so the tables will be heap-organized instead of index-organized (IOT).

Querying a Registered XML Schema to Obtain Annotations

The registered version of an XML schema will contain a full set of XDB annotations. As was shown in Example 5-8, and Example 5-9, the location of the registered XML schema depends on whether the schema is a local or global schema.

This document can be queried to find out the values of the annotations that were supplied by the user, or added by the schema registration process. For instance, the following query shows the set of global complexType definitions declared by the XMLSchema and the corresponding SQL object types and DOM fidelity values.

Example 5-15 Querying Metadata from a Registered XML Schema

SELECT extractValue(value(ct),
                    '/xs:complexType/@name',
                    'xmlns:xs="http://www.w3.org/2001/XMLSchema"
                     xmlns:xdb="http://xmlns.oracle.com/xdb"')
       XMLSCHEMA_TYPE_NAME,
       extractValue(value(ct),
                    '/xs:complexType/@xdb:SQLType',
                    'xmlns:xs="http://www.w3.org/2001/XMLSchema"
                     xmlns:xdb="http://xmlns.oracle.com/xdb"')
       SQL_TYPE_NAME,
       extractValue(value(ct),
                    '/xs:complexType/@xdb:maintainDOM',
                    'xmlns:xs="http://www.w3.org/2001/XMLSchema"
                     xmlns:xdb="http://xmlns.oracle.com/xdb"')
       DOM_FIDELITY
  FROM RESOURCE_VIEW,
       table(
         XMLSequence(
           extract(
             res,
             '/r:Resource/r:Contents/xs:schema/xs:complexType',
             'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"
              xmlns:po="http://xmlns.oracle.com/xdb/documentation/purchaseOrder"
              xmlns:xs="http://www.w3.org/2001/XMLSchema"
              xmlns:xdb="http://xmlns.oracle.com/xdb"'))) ct
  WHERE
    equals_path(
      res,
     '/sys/schemas/SCOTT/xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd')
    =1;

XMLSCHEMA_TYPE_NAME        SQL_TYPE_NAME            DOM_FIDELITY
-------------------------  -----------------------  ------------
PurchaseOrderType          PURCHASEORDER_T          true
LineItemsType              LINEITEMS_T              true
LineItemType               LINEITEM_T               true
PartType                   PART_T                   true
ActionsType                ACTIONS_T                true
RejectionType              REJECTION_T              true
ShippingInstructionsType   SHIPPING_INSTRUCTIONS_T  true

7 rows selected.

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 Types with 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-16 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:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb"
           version="1.0"
           xdb:storeVarrayAsTable="true">
  <xs:element name="PurchaseOrder" type="PurchaseOrderType" xdb:defaultTable="PURCHASEORDER"/>
  <xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T">
    <xs:sequence>
      <xs:element name="Reference" type="ReferenceType" minOccurs="1" xdb:SQLName="REFERENCE"/>
      <xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS"/>
      <xs:element name="Reject" type="RejectionType" minOccurs="0" xdb:SQLName="REJECTION"/>
      <xs:element name="Requestor" type="RequestorType" xdb:SQLName="REQUESTOR"/>
      <xs:element name="User" type="UserType" minOccurs="1" xdb:SQLName="USERID"/>
      <xs:element name="CostCenter" type="CostCenterType" xdb:SQLName="COST_CENTER"/>
      <xs:element name="ShippingInstructions" type="ShippingInstructionsType" 
                  xdb:SQLName="SHIPPING_INSTRUCTIONS"/>
      <xs:element name="SpecialInstructions" type="SpecialInstructionsType" 
                  xdb:SQLName="SPECIAL_INSTRUCTIONS"/>
      <xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"/>
      <xs:element name="Notes" type="po:NotesType" xdb:SQLType="CLOB"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T">
    <xs:sequence>
      <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" 
                  xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T">
    <xs:sequence>
      <xs:element name="Description" type="DescriptionType" 
                  xdb:SQLName="DESCRIPTION"/>
      <xs:element name="Part" type="PartType" xdb:SQLName="PART"/>
    </xs:sequence>
    <xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER" 
                  xdb:SQLType="NUMBER"/>
  </xs:complexType>
  <xs:complexType name="PartType" xdb:SQLType="PART_T">
    <xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:minLength value="10"/>
          <xs:maxLength value="14"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="Quantity" type="moneyType" xdb:SQLName="QUANTITY"/>
    <xs:attribute name="UnitPrice" type="quantityType" xdb:SQLName="UNITPRICE"/>
  </xs:complexType>

  ...

  <xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T">
    <xs:sequence>
      <xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION" xdb:SQLCollType="ACTION_V">
        <xs:complexType xdb:SQLType="ACTION_T">
          <xs:sequence>
            <xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY"/>
            <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_ACTIONED"/>
          </xs:sequence>
        </xs:complexType>
      </xs:element>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T">
    <xs:all>
      <xs:element name="User" type="UserType" minOccurs="0" xdb:SQLName="REJECTED_BY"/>
      <xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_REJECTED"/>
      <xs:element name="Comments" type="CommentsType" minOccurs="0" xdb:SQLName="REASON_REJECTED"/>
    </xs:all>
  </xs:complexType>
  <xs:complexType name="ShippingInstructionsType" xdb:SQLType="SHIPPING_INSTRUCTIONS_T">
    <xs:sequence>
      <xs:element name="name" type="NameType" minOccurs="0" xdb:SQLName="SHIP_TO_NAME"/>
      <xs:element name="address" type="AddressType" minOccurs="0" xdb:SQLName="SHIP_TO_ADDRESS"/>
      <xs:element name="telephone" type="TelephoneType" minOccurs="0" xdb:SQLName="SHIP_TO_PHONE"/>
    </xs:sequence>
  </xs:complexType>
  ...
</xs:schema>

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.

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

Description of Figure 5-2 follows
Description of "Figure 5-2 How Oracle XML DB Maps XML Schema-Based XMLType Tables"

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 value in one XMLType column, or stored object-relationally and spread out across several columns in the table.

Mapping Types with DBMS_XMLSCHEMA

Use PL/SQL package DBMS_XMLSCHEMA to map types for attributes and elements.

Setting Attribute Mapping Type Information

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

  • Primitive type

  • Global simpleType, declared within this XML schema or in an external XML schema

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

  • Local simpleType

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

Overriding the SQLType Value in XML Schema When Declaring Attributes

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

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

  • If the default type is RAW, then you can override it with RAW or BLOB.

Setting Element Mapping Type Information

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

  • Any of the ways for specifying type for an attribute declaration. See "Setting Attribute Mapping Type Information" .

  • Global complexType, specified within this XML schema document or in an external XML schema.

  • Reference to a global element (ref="..."), which could itself be within this XML schema document or in an external XML schema.

  • Local complexType.

Overriding the SQLType Value in XML Schema When Declaring Elements

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

  • VARCHAR2

  • RAW

  • CLOB

  • BLOB

These represent storage of the XML in a text or unexploded form in the database.

For example, to override the SQLType from VARCHAR2 to CLOB declare the XDB namespace as follows:

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

and then use xdb:SQLType="CLOB".

The following special cases are handled:

  • If a cycle is detected when processing the complexType values that are used to declare elements and the elements declared within the complexType, the SQLInline attribute is forced to be false and the correct SQL mapping is set to REF XMLType.

  • If maxOccurs > 1, a varray type may be created.

    • If SQLInline ="true", then a varray type is created whose element type is the SQL type previously determined.

      • Cardinality of the varray is determined based on the value of maxOccurs attribute.

      • The name of the varray type is either explicitly specified by the user using SQLCollType attribute or obtained by mangling the element name.

    • If SQLInline="false", then the SQL type is set to XDB.XDB$XMLTYPE_REF_LIST_T, a predefined type representing an array of REF values to XMLType.

  • If the element is a global element, or if SQLInline="false", then the system creates a default table. The name of the default table is specified by you or derived by mangling the element name.

See Also:

Chapter 7, "XML Schema Storage and Query: Advanced" for more information about mapping simpleType values and complexType values to SQL.

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

Description of Figure 5-3 follows
Description of "Figure 5-3 Mapping simpleType: XML Strings to SQL VARCHAR2 or CLOBs"

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

string

-

VARCHAR2(4000) if mapUnboundedStringToLob="false", CLOB

CHAR, 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)

FLOAT, DOUBLE, BINARY_FLOAT

double

NUMBER

NUMBER(m,n)

FLOAT, DOUBLE, BINARY_DOUBLE

decimal

NUMBER

NUMBER(m,n)

FLOAT, DOUBLE

integer

NUMBER

NUMBER(m,n)

NUMBER

nonNegativeInteger

NUMBER

NUMBER(m,n)

NUMBER

positiveInteger

NUMBER

NUMBER(m,n)

NUMBER

nonPositiveInteger

NUMBER

NUMBER(m,n)

NUMBER

negativeInteger

NUMBER

NUMBER(m,n)

NUMBER

long

NUMBER(20)

NUMBER(m,n)

NUMBER

unsignedLong

NUMBER(20)

NUMBER(m,n)

NUMBER

int

NUMBER(10)

NUMBER(m,n)

NUMBER

unsignedInt

NUMBER(10)

NUMBER(m,n)

NUMBER

short

NUMBER(5)

NUMBER(m,n)

NUMBER

unsignedShort

NUMBER(5)

NUMBER(m,n)

NUMBER

byte

NUMBER(3)

NUMBER(m,n)

NUMBER

unsignedByte

NUMBER(3)

NUMBER(m,n)

NUMBER


Table 5-8 Mapping XML Date Datatypes to SQL

XML Primitive Type Default Mapping Compatible Datatypes

datetime

TIMESTAMP

TIMESTAMP WITH TIME ZONE, DATE

time

TIMESTAMP

TIMESTAMP WITH TIME ZONE, DATE

date

DATE

TIMESTAMP WITH TIME ZONE

gDay

DATE

TIMESTAMP WITH TIME ZONE

gMonth

DATE

TIMESTAMP WITH TIME ZONE

gYear

DATE

TIMESTAMP WITH TIME ZONE

gYearMonth

DATE

TIMESTAMP WITH TIME ZONE

gMonthDay

DATE

TIMESTAMP WITH TIME ZONE

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

--


NVARCHAR and NCHAR SQLType Values are Not Supported

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

simpleType: Mapping XML Strings to SQL VARCHAR2 Versus CLOBs

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

Working with Time Zones

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

  • xsd:dateTime

  • xsd:time

  • xsd:date

  • xsd:gYear

  • xsd:gMonth

  • xsd:gDay

  • xsd:gYearMonth

  • xsd:gMonthDay

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

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

Example:

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

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

Using Trailing Z to Indicate UTC Time Zone

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

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

Mapping complexType to SQL

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

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

Specifying Attributes in a complexType XML Schema Declaration

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

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

<xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T">
    <xs:sequence>
      <xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded" 
                  xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V"/>
    </xs:sequence>
  </xs:complexType>
  <xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T">
    <xs:sequence>
      <xs:element name="Description" type="DescriptionType" 
                  xdb:SQLName="DESCRIPTION"/>
      <xs:element name="Part" type="PartType" xdb:SQLName="PART"/>
    </xs:sequence>
    <xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER" 
                  xdb:SQLType="NUMBER"/>
  </xs:complexType>
  <xs:complexType name="PartType" xdb:SQLType="PART_T">
    <xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2">
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:minLength value="10"/>
          <xs:maxLength value="14"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
    <xs:attribute name="Quantity" type="moneyType" xdb:SQLName="QUANTITY"/>
    <xs:attribute name="UnitPrice" type="quantityType" xdb:SQLName="UNITPRICE"/>
  </xs:complexType>

Note:

As always:
  • SQL is case-insensitive, but names in SQL code are implicitly uppercase, unless you enclose them in double-quotes.

  • XML is case-sensitive. You must refer to SQL names in XML code using the correct case: uppercase SQL names must be written as uppercase.

For example, if you create a table named my_table in SQL without using double-quotes, then you must refer to it in XML as "MY_TABLE".