7 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 the following:

  • Register, update, and delete an XML schema

  • Create storage structures for XML schema-based data

  • Map XML Schema data types to SQL data types

This chapter contains these topics:

See Also:

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 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 7-1 shows 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-9, "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 replaced here by an ellipsis (...).

Example 7-1 XML Schema Instance purchaseOrder.xsd

<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 7-2 shows an XML document that conforms to XML schema purchaseOrder.xsd:

Example 7-2 purchaseOrder.xml: Document That Conforms to 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 is a name that uniquely identifies the registered XML schema within the database: http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd. This need not point to a location where 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 with XML Schema

Oracle XML DB exploits the strong typing and other powerful properties of XML Schema to process XML database data safely and efficiently.

Note:

With XML data that is stored using binary XML storage, you can use a DTD to obtain the XML entities defined there. However, the structural and type information in the DTD is not used by Oracle XML DB. The entities are the only information used.

Oracle XML DB uses annotated XML schemas as metadata. The standard XML Schema definitions are used, along with several Oracle namespace attributes. These attributes determine how XML instance documents get mapped to the database. Because these attributes are in a different namespace from the XML Schema namespace, such annotated XML schemas are 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 identifies the XML schema in the database. It is associated with parameter SCHEMAURL of PL/SQL procedure DBMS_XMLSCHEMA.registerSchema.

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

  • Registering W3C-compliant XML schemas, both local and global.

  • Validating your XML documents against registered XML schema definitions.

  • Generating XML schemas from SQL object types.

  • Referencing an XML schema owned by another user.

  • Explicitly referencing a global XML schema when a local XML schema exists with the same name.

  • Generating a database mapping from your XML schemas during XML schema registration. This includes generating SQL object types, collection types, and default tables, and capturing the mapping information using XML schema attributes.

  • Specifying a particular SQL data type mapping when there are multiple allowed mappings.

  • Creating XMLType tables, views, and columns based on registered XML schemas.

  • Manipulating and querying XML schema-based XMLType tables.

  • Automatically inserting data into default tables when XML schema-based documents are inserted into Oracle XML DB Repository using protocols (FTP, HTTP(S)/WebDAV) and languages besides SQL.

Why XML Schema?

XMLType is an abstract data type that facilitates storing XML data in database columns and tables. XML Schema offers you additional storage and access options for XML data. You can use XML schemas to define which XML elements and attributes, which kinds of element nesting, and which data types can be used.

XML Schema lets you verify that your XML data conforms to its intended definition: the data is validated against the XML schemas that define its proper structure. This definition includes data types, numbers of allowed item occurrences, and allowed lengths of items. When storing XML Schema-based documents in Oracle XML DB using protocols such as FTP or HTTP(S), the XML schema information can improve the efficiency of document insertion. When XML instances must be handled without any prior information about them, XML schemas can be useful in predicting optimum storage, fidelity, and access.

You can take advantage of XML Schema, including its strong typing, for XML data that is unstructured, semi-structured, or structured by storing it as binary XML.

If your XML data is highly structured, then consider storing it object-relationally. In that case, XML Schema is used to efficiently map XML (Schema) data types to SQL data types and object-relational tables and columns.

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 or binary XML 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.dtd.

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:

  • registerSchema – Register an XML schema with Oracle Database

  • deleteSchema – Delete a previously registered XML schema.

  • copyEvolve – Update a registered XML schema. See Chapter 10, "XML Schema Evolution".

Registering an XML Schema with Oracle XML DB

The main parameters 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, but 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 never tries to access a Web server identified by the specified URL.

    Note:

    You cannot register an XML schema using the same SCHEMAURL as any system-defined XML schema.
  • 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.

  • OPTIONS – Options that specify how the XML schema should be registered. The most important option is REGISTER_BINARYXML, which indicates that the XML schema is used for binary XML storage. Another option is REGISTER_NT_AS_IOT, which forces OCTs to be stored as index-organized tables (IOTs).

    Note:

    If you specify option REGISTER_BINARYXML, then you must also set parameter GENTYPES to FALSE.

The code in Example 7-3 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.

Example 7-3 Registering an XML Schema using DBMS_XMLSCHEMA.REGISTERSCHEMA

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

Delete and Reload Documents Before Registering Their XML Schema

When you register an XML schema, keep in mind that the act of registering a schema has no effect on the status of any instance documents already loaded into Oracle XML DB Repository that reference the XML schema. Because the XML schema 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:

  • Mapping XML Schema data types to Oracle XML DB storage. When XML schema-based data is stored, its storage data types are derived from the XML Schema data types using a default mapping and, optionally, using mapping information that you specify using XML schema annotations. For binary XML storage, XML Schema types are mapped to binary XML encoding types. For object-relational storage, XML schema registration creates the appropriate SQL object types for the structured storage of conforming documents.

  • Creating default tables. XML schema registration 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 XML schema registration, documents that reference the XML schema using the XML Schema instance mechanism can be processed automatically by Oracle XML DB. For XML data that is stored object-relationally, XMLType tables and columns can be created that are constrained to the global elements defined by the XML schema.

Atomic Nature of XML Schema Registration

Like all DDL operations, XML schema registration is non-transactional. However, registration is atomic, in this sense:

  • 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 types and tables thus created. 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 Oracle XML DB XML schemas is called the root XML Schema, XDBSchema.xsd. The root XML schema describes any valid XML schema that can be registered with Oracle XML DB. You can access XDBSchema.xsd at Oracle XML DB Repository location /sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBSchema.xsd.

Debugging XML Schema Registration for XML Data Stored Object-Relationally

For XML data stored object-relationally, you can monitor the object types and tables created during XML schema registration by setting the following event before invoking PL/SQL procedure DBMS_XMLSCHEMA.registerSchema:

ALTER SESSION SET EVENTS = '31098 TRACE NAME CONTEXT FOREVER'

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

SQL Object Types Created During XML Schema Registration, for Structured Storage

If parameter GENTYPES is TRUE when an XML schema is registered for use with XML data stored object-relationally, then Oracle XML DB creates the appropriate SQL object types that enable structured storage of conforming XML documents. By default, all SQL object types are created in the database schema of the user who registers the XML schema. If annotation xdb:defaultSchema 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 create these object types.

Example 7-4 shows the SQL object types that are created automatically when XML schema purchaseOrder.xsd is registered with Oracle XML DB.

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

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 SQL object types and attributes are system-generated. This is the case in Example 7-4. If the XML schema does not contain attribute SQLName, then the SQL name is derived from the XML name. You can use XML schema annotations to provide user-defined names (see "Oracle XML Schema Annotations" for details).

Default Tables Created During XML Schema Registration

As part of XML schema registration for XML data, you can create default tables. Default tables are most useful when documents conforming to the XML schema are inserted through APIs and protocols such as FTP and HTTP(S) that do not provide any table specification. In such cases, the XML instance is inserted into the default table.

Example 7-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 xdb:defaultTable, then the XMLType table is created with that name. Otherwise it is created with an internally generated name.

Any text specified using attributes xdb:tableProps and xdb:columnProps is appended to the generated CREATE TABLE statement.

Do Not Use Internal Constructs Generated during XML Schema Registration

In general, the SQL constructs generated during XML schema registration are internal to Oracle XML DB. Oracle recommends that you do not use them in your code.

More precisely, generated SQL data types, nested tables, and tables associated with out-of-line storage are all internal. They are based on specific XML schema-to-object type mappings that are subject to change and redefinition by Oracle at any time.

In general:

  • Do not use any generated SQL data types.

  • Do not access or modify any generated nested tables or out-of-line tables.

You can, however, modify the storage options, such as partitioning, of generated tables, and you can create indexes and constraints on generated tables. You can also freely use any XML schema annotations provided by Oracle XML DB, including to name generated constructs for your convenience.

Generated Names are Case Sensitive

The names of any SQL tables, object, and attributes generated by XML schema registration are case sensitive. For instance, in Example 7-3, a table named PurchaseOrder1669_TAB is created automatically during registration of the XML schema. Because this table name was derived from the element name, PurchaseOrder, the table name is also mixed case. You must therefore refer to this table in SQL code by 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.

Database Objects That Depend on Registered XML Schemas

The following database objects are dependent on registered XML schemas:

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

  • Other XML schemas that include or import a given XML schema as part of their definition.

  • Cursors that reference an XML schema. This includes references within functions of package DBMS_XMLGEN. Such cursors are purely transient objects.

Listing All Registered XML Schemas

Example 7-6 shows how to use PL/SQL procedure DBMS_XMLSCHEMA.registerSchema to obtain a list of all XML schemas registered with Oracle XML DB. You can also examine views USER_XML_SCHEMAS, ALL_XML_SCHEMAS, USER_XML_TABLES, and ALL_XML_TABLES.

Example 7-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)
HIER_TYPE          VARCHAR2(11)
BINARY             VARCHAR2(3)
SCHEMA_ID          RAW(16)
HIDDEN             VARCHAR2(3)

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)
ANYSCHEMA          VARCHAR2(3)
NONSCHEMA          VARCHAR2(3)

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

You can delete a registered XML schema by using procedure DBMS_XMLSCHEMA.deleteSchema. This does the following, by default:

  1. Checks that the current user has the appropriate privileges to delete the resource corresponding to the XML schema within Oracle XML DB Repository. You can control which users can delete which XML schemas, by setting the appropriate ACLs on the XML schema resources.

  2. Checks whether there are any tables dependent on the XML schema that is to be deleted. If so, raises an error and cancels the deletion. This check is not performed if option delete_invalidate or delete_cascade_force is used. In that case, no error is raised.

  3. Removes the XML schema document from the Oracle XML DB Repository (folder /sys/schemas).

  4. Removes the XML schema document from DBA_XML_SCHEMAS, unless it was registered for use with binary XML instances and neither delete_invalidate nor delete_cascade_force is used.

  5. Drops the default table, if either delete_cascade or delete_cascade_force is used. Raises an error if delete_cascade_force is specified and there are instances in other tables that are also dependent on the XML schema.

DBMS_XMLSCHEMA.DELETESCHEMA Options

The following values are available for option DELETE_OPTION of procedure DBMS_XMLSCHEMA.deleteSchema:

  • DELETE_RESTRICT – Raise an error and cancel deletion if dependencies are detected. This is the default behavior.

  • DELETE_INVALIDATE – Do not raise an error if dependencies are detected. Instead, mark each of the dependencies as being invalid.

  • DELETE_CASCADE – Drop all types and default tables that were generated during XML schema registration. Raise an error if there are instances that depend upon the XML schema that are stored in tables other than the default table. However, do not raise an error for any such instances that are stored in XMLType columns that were created using ANY_SCHEMA. If the XML schema was registered for use with binary XML, do not remove it from DBA_XML_SCHEMAS.

  • DELETE_CASCADE_FORCE – Drop all types and default tables that were generated during XML schema registration. Do not raise an error if there are instances that depend upon the XML schema that are stored in tables other than the default table. Instead, mark each of the dependencies as being invalid. Remove the XML schema from DBA_XML_SCHEMAS.

Example 7-7 illustrates the use of DELETE_CASCADE_FORCE.

Example 7-7 Deleting an XML Schema 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;
/

If an XML schema was registered for use with binary XML, it is not removed from DBA_XML_SCHEMAS when you delete it using option DELETE_RESTRICT (the default value) or DELETE_CASCADE. As a consequence, although you can no longer use the XML schema to encode new XML instance documents, any existing documents in Oracle XML DB that reference the XML schema can still be decoded using it.

This remains the case, until you remove the XML schema from DBA_XML_SCHEMAS using DBMS_XMLSCHEMA.purgeSchema. Oracle recommends that, in general, you use delete_restrict or delete_cascade. Instead of using DELETE_CASCADE_FORCE, call DBMS_XMLSCHEMA.purgeSchema when you are sure you no longer need the XML schema.

Procedure purgeSchema removes the XML schema completely from Oracle XML DB. In particular, it removes it from DBA_XML_SCHEMAS. Before you use DBMS_XMLSCHEMA.purgeSchema, be sure that you have transformed all existing XML documents that reference the XML schema to be purged, so they reference a different XML schema or no XML schema. Otherwise, it will be impossible to decode them after the purge.

XMLType Methods Related to XML Schema

Table 7-1 lists some of the XMLType methods that are useful for working with XML schemas.

Table 7-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() 

The XML schema URL for an XMLType instance.

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

Validate an XMLType instance against a registered XML schema.

See Chapter 11, "Transforming and Validating XMLType Data".


Local and Global XML Schemas

XML schemas can be registered as local or global:

  • A local xml schema is, by default, visible only to its owner.

  • A global xml schema is, by default, visible and usable by all database users.

When you register an XML schema, PL/SQL package DBMS_XMLSCHEMA adds a corresponding resource to Oracle XML DB Repository. The XML schema URL determines the path name of the XML schema resource in the repository (and it is associated with parameter SCHEMAURL of PL/SQL procedure DBMS_XMLSCHEMA.registerSchema).

Note:

In Oracle Enterprise Manager, local and global registered XML schemas are referred to as private and public, respectively.

Local XML Schema

By default, an XML schema belongs to you after you register it 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. By default, they are usable only by you, the owner. In Oracle XML DB, local XML schema resources are created under folder /sys/schemas/username. The rest of the repository path name is derived from the schema URL.

Example 7-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 QUINE, it is given this path name:

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

Database users need appropriate permissions and Access Control Lists (ACLs) 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 XML DB supports fully qualified XML schema URLs. For example: http://xmlns.oracle.com/xdb/schemas/QUINE/xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd. Privileged users can use such an extended URL to specify XML schemas belonging to other users.

Global XML Schema

In contrast to local schemas, a privileged user can register an XML schema as global by specifying an argument in the DBMS_XMLSCHEMA registration function. Global XML schemas are visible to all users. They are stored under folder /sys/schemas/PUBLIC/ in Oracle XML DB Repository.

Note:

Access to folder /sys/schemas/PUBLIC is controlled by access control lists (ACLs). By default, this folder is writable only by a database administrator. You need write privileges on this folder to register global XML schemas. Role XDBADMIN provides write access to this folder, assuming that it is protected by the default ACLs. See Chapter 27, "Repository Access Control".

You can register a local schema with the same URL as an existing global schema. A local schema always shadows (hides) any global schema with the same name (URL). Example 7-9 illustrates registration of a global schema.

Example 7-9 Registering a Global XML Schema

GRANT XDBADMIN TO QUINE;

Grant succeeded.

CONNECT quine
Enter password: password

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 QUINE, it is given this path name:

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

Database users need appropriate permissions (ACL access) 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.

What is DOM Fidelity?

DOM fidelity means that all information in an XML document is preserved, except whitespace that is insignificant. With DOM fidelity, XML data retrieved from the database has the same information as before it was inserted into the database, with the single exception of insignificant whitespace. The term "DOM fidelity" is used because this kind of fidelity is particularly important for DOM traversals.

With binary XML storage of XML data, all of the significant information is encoded in the binary XML format, ensuring DOM fidelity. With structured storage of XML data, the elements and attributes declared in an XML schema are mapped to separate attributes in the corresponding SQL object types. However, the following information in XML instance documents is not stored in these object attributes:

  • Namespace declarations

  • Comments

  • Prefix information

Instead, Oracle XML DB uses a separate mechanism to keep track of this information: it is recorded as instance-level metadata.

SYS_XDBPD$ and DOM Fidelity for Structured Storage

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

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

If DOM fidelity is not required, you can suppress the use of SYS_XDBPD$ by setting attribute xdb:maintainDOM to false in the XML schema, at the type level.

Note:

For clarity, object attribute SYS_XDBPD$ is omitted in many examples in this book. However, it is always present as a positional descriptor (PD) column in all SQL object types that are generated by the XML schema registration process.

In general, Oracle recommends that you do not suppress the PD attribute, because the extra information, such as comments and processing instructions, could be lost if there is no PD column.

XML Translations

You can store XML documents in Oracle XML DB Repository as XMLType instances. You can use any storage model for these instances: structured (object-relational storage), unstructured (CLOB), or binary XML. These documents sometimes contain strings that must be translated into various (natural) languages. You typically store both the original strings and their translations in the repository. You can retrieve and operate on these strings, depending on your language settings.

Note:

XML schemas stored object-relationally are not translatable.

Changing an XML Schema and XML Instance Documents for Translation

This section describes the changes that are required to be made to an XML schema and an associated XML instance document to make the document translatable.

Indicating Translatable Elements in an XML Schema

Attribute xdb:translate must be specified in the XML schema for each element that is to be translated. The following restrictions apply to attribute xdb:translate.

  1. Attribute xdb:translate can be specified only on complexType elements that have simpleContent. Here, simpleContent must be an extension or a restriction of type string. However, if a complexType element has the xdb:translate flag set, then none of its descendants can have this flag set.

  2. Attribute xdb:translate can be set only on a single-valued element, which has exactly one translation. For such an element, the value of maxoccurs must be 0 or 1. If you want to set this attribute on a multiple-valued element, the element must have an ID attribute, which uniquely identifies the element.

During XML schema registration, PL/SQL procedure DBMS_XMLSCHEMA.registerSchema checks whether the XML schema satisfies these restrictions.

Indicating Translation Language Attributes in an XML Instance Document

The following translation language attributes are supported:

  • xml:lang: For an instance document associated with an XML schema that supports translations, you must specify the translation language. You can do this by annotating each translation with attribute xml:lang. The allowed values of the xml:lang attribute are the language identifiers identified by IETF RFC 3066.

  • xdb:srclang: For multiple-valued elements, that is, elements that can have multiple translations, only one translation can be used as the source language translation. That translation is specified by attribute xdb:srclang. This is the default translation, which is returned when the session language is not specified.

Making XML Documents Translatable

This section uses the translation-specifying XML schema attributes to make elements in a sample document translatable. Example 7-10 shows an XML schema that defines documents that contain a title string that needs to be translatable.

Example 7-10 XML Schema Defining Documents with a Title To Be Translated

<schema xmlns="http://www.w3.org/2001/XMLSchema"
        xmlns:xdbsc="http://xmlns.oracle.com/xdb/security.xsd"
        xmlns:xdb="http://xmlns.oracle.com/xdb.xsd"
        targetNamespace="http://xmlns.oracle.com/xdb/security.xsd"
        elementFormDefault="qualified" version="1.0">
  <annotation>
    <documentation>
This XML schema describes the structure of Security Class documents.
    </documentation>
  </annotation>
  <element name="securityClass" xdb:defaultTable="">
    <complexType>
      <sequence>
        <element name="name" type="string"/>
        <element name="title" minOccurs="0" maxOccurs="unbounded"/>
        <element name="inherits-from" type="QName" minOccurs="0" maxOccurs="unbounded"/>
        <element name="privlist" minOccurs="0" maxOccurs="unbounded">
          <complexType>
            <choice minOccurs="0" maxOccurs="unbounded">
              <element ref="xdbsc:privilege"/>
                <element ref="xdbsc:aggregatePrivilege"/>
            </choice>
          </complexType>
        </element>
        <!-- this "any" contains all application specific information
             for a security class in general e.g. reason for creation  -->
        <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
      </sequence>
      <attribute name="targetNamespace" type="anyURI" use="required"/>
      <!-- all privileges in this security class are under this target namespace -->
    </complexType>
  </element>
  <element name="aggregatePrivilege">
    <complexType>
      <sequence>
        <element name="title" minOccurs="0" maxOccurs="unbounded"/>
        <sequence maxOccurs="unbounded">
          <element name="privilegeRef">
            <complexType>
              <attribute name="name" type="QName" use="required"/>
            </complexType>
          </element>
          <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
        </sequence>
        <!-- this "any" contains all application specific information   
             an aggregate privilege e.g. translations  -->
        <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
      </sequence>
      <attribute name="name" type="string" use="required"/>
    </complexType>
  </element>
  <element name="privilege">
    <complexType>
      <sequence minOccurs="0">
        <element name="title" minOccurs="0" maxOccurs="unbounded"/>
        <sequence minOccurs="0" maxOccurs="unbounded">
          <element name="columnRef">
            <complexType>
              <attribute name="schema" type="string" use="required"/>
              <attribute name="table" type="string" use="required"/>
              <attribute name="column" type="string" use="required"/>
            </complexType>
          </element>
          <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
        </sequence>
        <!-- this "any" contains all application specific information   
             for a privilege e.g. translations  -->
        <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
      </sequence>
      <attribute name="name" type="string" use="required"/>
    </complexType>
  </element>
</schema>

Example 7-11 shows a document that is associated with the XML schema of Example 7-10.

Example 7-11 Untranslated Instance Document

<securityClass xmlns="http://xmlns.oracle.com/xdb/security.xsd"
               xmlns:is="xmlns.oracle.com/iStore"
               xmlns:oa="xmlns.oracle.com/OracleApps"
               targetNamespace="xmlns.oracle.com/example">
  <name>
securityClassExample
  </name>
  <title>
Security Class Example
  </title>
  <inherits-from>is:iStorePurchaseOrder</inherits-from>
  <privlist>
  <privilege name="privilege1"/>
  <aggregatePrivilege name="iStorePOApprover">
    <title>
iStore Purchase Order Approver
    </title>
    <privilegeRef name="is:privilege1"/>
    <privilegeRef name="oa:submitPO"/>
    <privilegeRef name="oa:privilege3"/>
  </aggregatePrivilege>
  <privilege name="privilege2">
    <title>
secondary privilege
    </title>
    <columnRef schema="APPS" table="PurchaseOrder" column="POId"/>
    <columnRef schema="APPS" table="PurchaseOrder" column="Amount"/>
  </privilege>
  </privlist>
</securityClass>

To make the top-level title translatable, set xdb:translate to true. This is a single-valued element (xdb:maxOccurs is 1). Example 7-12 shows the new XML schema, where attribute xdb:translate is true.

Example 7-12 XML Schema with Attribute xdb:translate for a Single-Valued Element

<schema xmlns="http://www.w3.org/2001/XMLSchema"
        xmlns:xdbsc="http://xmlns.oracle.com/xdb/security.xsd"
        xmlns:xdb="http://xmlns.oracle.com/xdb.xsd"
        targetNamespace="http://xmlns.oracle.com/xdb/security.xsd"
        elementFormDefault="qualified" version="1.0">
<xs:import namespace="http://www.w3.org/XML/1998/namespace"
        schemaLocation="http://www.w3.org/2001/xml.xsd"/>
<xs:import namespace-"http://xmlns.oracle.com/xdb"
        schemaLocation="http://xmlns.oracle.com/xdb/xmltr.xsd"/>
  <annotation>
    <documentation>
This XML schema describes the structure of Security Class documents.
    </documentation>
  </annotation>
  <element name="securityClass" xdb:defaultTable="">
    <complexType>
      <sequence>
        <element name="name" type="string"/>
        <element ref="titleref" minOccurs="0" maxOccurs="unbounded"
                 xdb:maxOccurs="1" xdb:translate="true"/>
        <element name="inherits-from" type="QName" minOccurs="0" maxOccurs="unbounded"/>
        <element name="privlist" minOccurs="0" maxOccurs="unbounded" xdb:maxOccurs="1">
          <complexType>
            <choice minOccurs="0" maxOccurs="unbounded">
              <element ref="xdbsc:privilege"/>
              <element ref="xdbsc:aggregatePrivilege"/>
            </choice>
          </complexType>
        </element>
        <!-- this "any" contains all application specific information
             for a security class in general e.g. reason for creation  -->
        <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
      </sequence>
      <attribute name="targetNamespace" type="anyURI" use="required"/>
      <!-- all privileges in this security class are under this target namespace -->
    </complexType>
  </element>
  <element name="aggregatePrivilege">
    <complexType>
      <sequence>
        <element name="title" minOccurs="0" maxOccurs="unbounded"/>
        <sequence maxOccurs="unbounded">
          <element name="privilegeRef">
            <complexType>
              <attribute name="name" type="QName" use="required"/>
            </complexType>
          </element>
          <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
        </sequence>
        <!-- this "any" contains all application specific information   
             an aggregate privilege e.g. translations  -->
        <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
      </sequence>
      <attribute name="name" type="string" use="required"/>
    </complexType>
  </element>
  <element name="privilege">
    <complexType>
      <sequence minOccurs="0">
        <element name="title" minOccurs="0" maxOccurs="unbounded"/>
        <sequence minOccurs="0" maxOccurs="unbounded">
          <element name="columnRef">
            <complexType>
              <attribute name="schema" type="string" use="required"/>
              <attribute name="table" type="string" use="required"/>
              <attribute name="column" type="string" use="required"/>
            </complexType>
          </element>
          <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
        </sequence>
        <!-- this "any" contains all application specific information   
             for a privilege e.g. translations  -->
        <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
      </sequence>
      <attribute name="name" type="string" use="required"/>
    </complexType>
  </element>
  <element name="titleref">
    <complexType>
      <simpleContent>
        <extension base="xs:string">
          <attribute ref="xml:lang"/>
          <attribute ref="xdb:srclang"/>
        </extension>
      </simpleContent>
    </complexType>
  </element>
</schema>

Example 7-13 shows an instance document after translation of the title text.

Example 7-13 Translated Document

<securityClass xmlns="http://xmlns.oracle.com/xdb/security.xsd"
               xmlns:is="xmlns.oracle.com/iStore"
               xmlns:oa="xmlns.oracle.com/OracleApps">
  <name>
securityClassExample
  </name>
  <title xdb:srclang="true">
Security Class Example
  </title>
  <title>
Security Class Example - Spanish
  </title>
  <title>
Security Class Example - French
  </title>    
  <inherits-from>is:iStorePurchaseOrder</inherits-from>
  <privlist>
    <privilege name="privilege1"/>
    <aggregatePrivilege name="iStorePOApprover">
      <title>
iStore Purchase Order Approver
      </title>
      <privilegeRef name="is:privilege1"/>
      <privilegeRef name="oa:submitPO"/>
      <privilegeRef name="oa:privilege3"/>
    </aggregatePrivilege>
    <privilege name="privilege2">
      <title>
secondary privilege
      </title>
      <columnRef schema="APPS" table="PurchaseOrder" column="POId"/>
      <columnRef schema="APPS" table="PurchaseOrder" column="Amount"/>
    </privilege>
  </privlist>
</securityClass>

To make the title translatable in the case of a multi-valued element, you would set xdb:maxOccurs to unbounded. However, xdb:translate cannot be set to true for a multiple-valued element, unless there is an identifier attribute that uniquely identifies each element. Example 7-14 shows an XML schema that uses an identifier attribute id for the title element.

Example 7-14 XML Schema with Attribute xdb:translate for a Multi-Valued Element

<schema xmlns="http://www.w3.org/2001/XMLSchema"
        xmlns:xdbsc="http://xmlns.oracle.com/xdb/security.xsd"
        xmlns:xdb="http://xmlns.oracle.com/xdb.xsd"
        targetNamespace="http://xmlns.oracle.com/xdb/security.xsd"
        elementFormDefault="qualified" version="1.0">
<xs:import namespace="http://www.w3.org/XML/1998/namespace"
        schemaLocation="http://www.w3.org/2001/xml.xsd"/>
<xs:import namespace-"http://xmlns.oracle.com/xdb"
        schemaLocation="http://xmlns.oracle.com/xdb/xmltr.xsd"/>
  <annotation>
    <documentation>
This XML schema describes the structure of Security Class documents.
    </documentation>
  </annotation>
  <element name="securityClass" xdb:defaultTable="">
    <complexType>
      <sequence>
        <element name="name" type="string"/>
        <element name="title" minOccurs="0" maxOccurs="unbounded" xdb:maxOccurs="1"/>
        <element name="inherits-from" type="QName" minOccurs="0" maxOccurs="unbounded"/>
        <element name="privlist" minOccurs="0" maxOccurs="unbounded" xdb:maxOccurs="1">
          <complexType>
            <choice minOccurs="0" maxOccurs="unbounded">
              <element ref="xdbsc:privilege"/>
              <element ref="xdbsc:aggregatePrivilege"/>
            </choice>
          </complexType>
        </element>
        <!-- this "any" contains all application specific information
             for a security class in general e.g. reason for creation  -->
        <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
      </sequence>
      <attribute name="targetNamespace" type="anyURI" use="required"/>
      <!-- all privileges in this security class are under this target namespace -->
    </complexType>
  </element>
  <element name="aggregatePrivilege">
    <complexType>
      <sequence>
        <element name="titleref" minOccurs="0" maxOccurs="unbounded"
                 xdb:maxoccurs="unbounded" xdb:translate="true"/>
        <sequence maxOccurs="unbounded">
          <element name="privilegeRef">
            <complexType>
              <attribute name="name" type="QName" use="required"/>
            </complexType>
          </element>
          <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
        </sequence>
        <!-- this "any" contains all application specific information   
             an aggregate privilege e.g. translations  -->
        <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
      </sequence>
      <attribute name="name" type="string" use="required"/>
    </complexType>
  </element>
  <element name="privilege">
    <complexType>
      <sequence minOccurs="0">
        <element name="titleref" minOccurs="0" maxOccurs="unbounded"
                 xdb:maxoccurs="unbounded" xdb:translate="true"/>
        <sequence minOccurs="0" maxOccurs="unbounded">
          <element name="columnRef">
            <complexType>
              <attribute name="schema" type="string" use="required"/>
              <attribute name="table" type="string" use="required"/>
              <attribute name="column" type="string" use="required"/>
            </complexType>
          </element>
          <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
        </sequence>
        <!-- this "any" contains all application specific information   
             for a privilege e.g. translations  -->
        <any namespace="##other" minOccurs="0" maxOccurs="unbounded"/>
      </sequence>
      <attribute name="name" type="string" use="required"/>
    </complexType>
  </element>
  <element name="titleref">
    <complexType>
      <simpleContent>
        <extension base="xs:string">
          <attribute ref="xml:lang"/>
          <attribute ref="xdb:srclang"/>
          <attribute name="id" type="integer"/>
        </extension>
      </simpleContent>
    </complexType>
  </element>
</schema>

Example 7-15 shows a document associated with the XML schema in Example 7-14.

Example 7-15 Translated Document for an XML Schema with Multiple-Valued Elements

<securityClass xmlns="http://xmlns.oracle.com/xdb/security.xsd"
               xmlns:is="xmlns.oracle.com/iStore"
               xmlns:oa="xmlns.oracle.com/OracleApps">
  <name>
securityClassExample
  </name>
  <title>
Security Class Example
  </title>
  <inherits-from>is:iStorePurchaseOrder</inherits-from>
  <privlist>
    <privilege name="privilege1"/>
    <aggregatePrivilege name="iStorePOApprover">
      <title>
iStore Purchase Order Approver
      </title>
      <privilegeRef name="is:privilege1"/>
      <privilegeRef name="oa:submitPO"/>
      <privilegeRef name="oa:privilege3"/>
    </aggregatePrivilege>
    <privilege name="privilege2">
      <title id="2" xdb:srclang="true">
secondary privilege - english
      </title>
      <title id="1">
primary privilege - french
      </title>
      <title id="1" xdb:srclang="true">
primary privilege - english
      </title>       
      <columnRef schema="APPS" table="PurchaseOrder" column="POId"/>
      <columnRef schema="APPS" table="PurchaseOrder" column="Amount"/>
    </privilege>
  </privlist>
</securityClass>

Operations on Translated Documents

You can perform the following operations on translated documents:

  • Insert: You can insert a document into Oracle XML DB, if it conforms to an XML schema that supports translations. For the document that contains translations, you can either provide the language information or use the session language translation.

    • When the document does not contain the language information and the xml:lang attribute is not set, the session language is used for translation. Example 7-16 describes a document with a session language of Japanese. Attribute xml:lang is set to session language, and attribute xdb:srclang is set to true.

      Example 7-16 Inserting a Document with No Language Information

      <securityClass xmlns="http://xmlns.oracle.com/xdb/security.xsd"
                     xmlns:is="xmlns.oracle.com/iStore"
                     xmlns:oa="xmlns.oracle.com/OracleApps"
                     targetNamespace="xmlns.oracle.com/example">
        <name>
      securityClassExample
        </name>
        <title>
      Security Class Example
        </title>
        <inherits-from>is:iStorePurchaseOrder</inherits-from>
      </securityClass>
      

      Example 7-17 shows the document after it is inserted into Oracle XML DB Repository.

      Example 7-17 Document After Insertion into the Repository

      <securityClass xmlns="http://xmlns.oracle.com/xdb/security.xsd"
                     xmlns:is="xmlns.oracle.com/iStore"
                     xmlns:oa="xmlns.oracle.com/OracleApps"
                     targetNamespace="xmlns.oracle.com/example">
        <name>
      securityClassExample
        </name>
        <title xdb:srclang="true">
      Security Class Example
        </title>
        <inherits-from>is:iStorePurchaseOrder</inherits-from>
      </securityClass>
      
    • When you provide the language information, you set attribute xml:lang by either explicitly marking a translation as xdb:srclang=true or using the session language translation in attribute xdb:srclang. If you do neither, then an arbitrary translation is picked, for which xdb:srclang is set to true.

      Example 7-18 describes a document with a session language of Japanese.

      Example 7-18 Inserting a Document with Language Information

      <securityClass xmlns="http://xmlns.oracle.com/xdb/security.xsd"
                     xmlns:is="xmlns.oracle.com/iStore"
                     xmlns:oa="xmlns.oracle.com/OracleApps"
                     targetNamespace="xmlns.oracle.com/example">
        <name>
      securityClassExample
        </name>
        <title xml:lang="en">
      Security Class Example
        </title>
        <title xml:lang="fr">
      Security Class Example - FR
        </title>
        <inherits-from>is:iStorePurchaseOrder</inherits-from>
      </securityClass>
      

      Example 7-19 shows the document after it is inserted into Oracle XML DB Repository.

      Example 7-19 Document After Insertion

      <securityClass xmlns="http://xmlns.oracle.com/xdb/security.xsd"
                     xmlns:is="xmlns.oracle.com/iStore"
                     xmlns:oa="xmlns.oracle.com/OracleApps"
                     targetNamespace="xmlns.oracle.com/example">
        <name>
      securityClassExample
        </name>
        <title xml:lang="en" xdb:srclang="true">
      Security Class Example
        </title>
        <title xml:lang="fr">
      Security Class Example - FR
        </title>
        <inherits-from>is:iStorePurchaseOrder</inherits-from>
      </securityClass>
      
  • Query: If you query nodes that involve translated elements, the query displays the translation's default behavior. In order to specify that the translation's default behavior should be applied to the query result, you need to use the Oracle XPath function ora:translate. This is the syntax of the function:

    Nodeset ora:translate(Nodeset parent, String childname, String childnsp)
    

    Parameter parent is the parent node under which you want to search for the translated nodes; childname is the name of the child node; and childnsp is the namespace URL of the child node.

    Function ora:translate returns a positive integer when the name of the parent node matches the name of the specified child node, and the xml:lang value is same as the session language or the language for which xdb:srclang is true.

    When SQL functions such as XMLQuery are applied to translated documents, they return the session language translation, if present, or the source language translation, otherwise. For example, this query returns the session language translation:

    SELECT XMLQuery('$x/ora:translate(securityClass, "title")'
                    PASSING x.OBJECT_VALUE AS "x" RETURNING CONTENT)
      FROM some_table x;
    

    This is the output of that query:

    <title>
    Security Class Example - FR
    </title>
    

    To obtain the result in a particular language, specify it in the XPath expression.

    SELECT XMLQuery('$x/securityClass/title[@xml:lang="en"]'
                    PASSING x.OBJECT_VALUE AS "x" RETURNING CONTENT)
      FROM some_table x;
    

    This is the output of that query:

    <title xdb:srclang="true">
    Security Class Example
    </title>
    

    Because you can store translated documents only as text (CLOB) or binary XML, only functional evaluation and queries with a function-based index, an XMLIndex index, or a CONTEXT index are possible. For XMLIndex index and CONTEXT index queries, if the document has a session language translation, then that is returned, otherwise the source language translation is returned. However, for queries with a function-based index, you need to create an index with an explicit xml:lang predicate for every language for which you want to use the index.

    When you retrieve the complete document using SQL functions such as XMLSerialize and XDBURIType, only the translations that match the session language translations are returned. For protocols, you can set your language preferences, and the document is returned in that language only.

    The following PL/SQL procedures and functions support XML translations:

    • DBMS_XMLTRANSLATIONS.translateXML: Translate a document to the specified language. If the specified language translation is present, it is returned, otherwise, the source language translation is returned.

      For example, if you write translateXML(doc, 'fr') to specify French as the translation language for the Example 7-19, it returns the following code and ignores all other translations:

      securityClass xmlns="http://xmlns.oracle.com/xdb/security.xsd"
                     xmlns:is="xmlns.oracle.com/iStore"
                     xmlns:oa="xmlns.oracle.com/OracleApps"
                     targetNamespace="xmlns.oracle.com/example">
          <name>
              securityClassExample
          </name>
          <title>
              Security Class Example - FR
          </title>
          <inherits-from>is:iStorePurchaseOrder</inherits-from>
      </securityClass>
      
    • DBMS_XMLTRANSLATIONS.enableTranslation, DBMS_XMLTRANSLATIONS.disableTranslation: Enable or disable translations at the session level. Queries work on the base document if the translation is disabled and on the translated document if it is enabled.

    • DBMS_XMLTRANSLATIONS.getBaseDocument: Returns the entire document, with all of the translations.

  • Update: You can use Oracle SQL function updateXML to update the translated nodes. However, an error is raised if you try to update a translated node without specifying the translation language. The following PL/SQL procedures support update operations on translated documents:

    • DBMS_XMLTRANSLATIONS.updateTranslation: This function updates the translation at a specified xpath in a particular language. If the translation in a particular language is not present, then it is inserted.

    • DBMS_XMLTRANSLATIONS.setSourceLang: This procedure sets the source language at a specified xpath to the specified language.

Creating XMLType Tables and Columns Based on XML Schemas

Using Oracle XML DB, you 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. You constrain an XMLType table column to a particular element and XML schema by adding appropriate XMLSCHEMA and ELEMENT clauses to the CREATE TABLE operation.

Figure 7-1 through Figure 7-4 show the syntax for creating an XMLType table.

See Also:

Oracle Database SQL Language Reference for the complete description of CREATE TABLE, including syntax elements such as object_properties.

Note:

To create an XMLType table in a different database schema from your own, you must have not only privilege CREATE ANY TABLE but also privilege CREATE ANY INDEX. This is because a unique index is created on column OBJECT_ID when you create the table. Column OBJECT_ID stores a system-generated object identifier.

Figure 7-1 Creating an XMLType Table – CREATE TABLE

Description of Figure 7-1 follows
Description of "Figure 7-1 Creating an XMLType Table – CREATE TABLE"

Figure 7-2 Creating an XMLType Table – XMLType_table

Description of Figure 7-2 follows
Description of "Figure 7-2 Creating an XMLType Table – XMLType_table"

Figure 7-3 Creating an XMLType Table – table_properties

Description of Figure 7-3 follows
Description of "Figure 7-3 Creating an XMLType Table – table_properties"

Figure 7-4 Creating an XMLType Table – XMLType_virtual_columns

Description of Figure 7-4 follows
Description of "Figure 7-4 Creating an XMLType Table – XMLType_virtual_columns"

Note:

For XML data, virtual columns are used primarily for partitioning or defining SQL constraints. If your need is to project out specific XML data in order to access it relationally, then consider using SQL/XML function XMLTable or XMLIndex with a structured component. See also:

A subset of the XPointer notation can also be used to provide a single URL that contains the XML schema location and element name. See also Chapter 4, "XMLType Operations".

Example 7-20 shows two CREATE TABLE statements. The first creates XMLType table purchaseorder_as_table. The second creates relational table purchaseorder_as_column, which has XMLType column xml_document. In each table, the XMLType instance is constrained to the PurchaseOrder element that is defined by the XML schema registered with URL http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd.

Example 7-20 Creating XML Schema-Based XMLType Tables and Columns

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 XMLSchema and Element:

  • as separate clauses, XMLSchema and Element

  • using only 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 different ways:

  • Decomposed and stored object-relationally (structured storage)

  • Stored as text, using a single CLOB column (unstructured storage)

  • Stored as binary XML, using a single binary-XML column (binary XML storage)

Specifying XMLType Storage Options for XML Schema-Based Data

You can specify storage options to use when you manually create a table that stores XML instance documents that reference an XML schema. To specify a particular XMLType storage model, use a STORE AS clause in the CREATE TABLE statement. Otherwise, the storage model specified during registration of the XML schema is used. If no storage model was specified during registration, then object-relational storage is used.

This section describes what you need to know about specifying storage options for XML schema-based data. You can also specify storage options for tables that are created automatically, by using XML schema annotations.

Binary XML Storage of XML Schema-Based Data

If you specify STORE AS BINARY_XML, then binary XML storage is used. If you specify an XML schema that the XML documents must conform to, then you can use that XML schema only to create XMLType tables and columns that are stored as binary XML. You cannot use the same XML schema to create XMLType tables and columns that are stored object-relationally or as CLOB instances.

The converse is also true: If you use a storage model other than binary XML for the registered XML schema, then you can use only that XML schema to create XMLType tables and columns that are not stored object-relationally or as CLOB instances.

Binary XML storage offers a great deal of flexibility for XML data, especially concerning the use of XML schemas. Binary XML encodes XML data differently, depending upon whether or not an XML schema is used for the encoding, and it can encode the same data differently using different XML schemas.

When an XML schema is taken into account for encoding binary XML data, the XML Schema data types are mapped to encoded types for storage. Alternatively, you can encode XML data as non-schema-based binary XML, whether or not the data references an XML schema. In that case, any referenced XML schema is ignored, and there is no encoding of XML Schema data types.

When you create an XMLType table or column and you use binary XML storage, you can specify how to encode the column or table to make use of XML schemas. Choose from among these possibilities:

  • Encode the column or table data as non-schema-based binary XML. The XML data stored in the column can nevertheless conform to an XML schema, but it need not. Any referenced XML schema is ignored for encoding purposes, and documents are not automatically validated when they are inserted or updated.

    You can nevertheless explicitly validate an XML schema-based document that is encoded as non-schema-based binary XML. This represents an important use case: situations where you do not want to tie documents too closely to a particular XML schema, because you might change it or delete it.

  • Encode the column or table data to conform to a single XML schema. All rows (documents) must conform to the same XML schema. You can nevertheless specify, as an option, that non-schema-based documents can also be stored in the same column.

  • Encode the column or table data to conform to whatever XML schema it references Each row (document) can reference any XML schema, and that XML schema is used to encode that particular XML document. In this case also, you can specify, as an option, that non-schema-based documents can also be stored in the same column.

    You can use multiple versions of the same XML schema in this way. Store documents that conform to different versions. Each is encoded according to the XML schema that it references.

You can specify that any XML schema can be used for encoding by using option ALLOW ANYSCHEMA when you create the table.

Note:

  • If you use option ALLOW ANYSCHEMA, then any XML schema referenced by your instance documents is used only for validation. It is not used at query time. Queries of your data treat it as if it were non XML schema-based data.

  • Oracle recommends that you do not use option ALLOW ANYSCHEMA if you anticipate using copy-based XML schema evolution (see "Using Copy-Based Schema Evolution"). If you use this option, it is impossible to determine which rows (documents) might conform to the XML schema that is evolved. Conforming rows are not transformed during copy-based evolution, and afterward they are not decodable.

You can specify, for tables and columns that use XML schema-based encodings, that they can accept also non-schema-based documents by using option ALLOW NONSCHEMA. In the absence of keyword XMLSCHEMA, encoding is for non-schema-based documents. In the absence of the keywords ALLOW NONSCHEMA but the presence of keyword XMLSCHEMA, encoding is for the single XML schema specified. In the absence of the keywords ALLOW NONSCHEMA but the presence of the keywords ALLOW ANYSCHEMA, encoding is for any XML schema that is referenced.

An error is raised if you try to insert an XML document into an XMLType table or column that does not correspond to the document.

The various possibilities are summarized in Table 7-2.

Table 7-2 CREATE TABLE Encoding Options for Binary XML

Storage Options Encoding Effect
STORE AS BINARY XML

Encodes all documents using the non-schema-based encoding.

STORE AS BINARY XML
 XMLSCHEMA ...

Encodes all documents using an encoding based on the referenced XML schema.

Trying to insert or update a document that does not conform to the XML schema raises an error.

STORE AS BINARY XML
 XMLSCHEMA ...
 ALLOW NONSCHEMA

Encodes all XML schema-based documents using an encoding based on the referenced XML schema. Encodes all non-schema-based documents using the non-schema-based encoding.

Trying to insert or update an XML schema-based document that does not conform to the referenced XML schema raises an error.

STORE AS BINARY XML
 ALLOW ANYSCHEMA

Encodes all XML schema-based documents using an encoding based on the XML schema referenced by the document.

Trying to insert or update a document that does not reference a registered XML schema or that does not conform to the XML schema it references raises an error.

STORE AS BINARY XML
 ALLOW ANYSCHEMA
 ALLOW NONSCHEMA

Encodes all XML schema-based documents using an encoding based on the XML schema referenced by the document. Encodes all non-schema-based documents using the non-schema-based encoding.

Trying to insert or update an XML schema-based document that does not conform to the registered XML schema it references raises an error.


Note:

If you use CREATE TABLE with ALLOW NONSCHEMA but not ALLOW ANYSCHEMA, then all documents, even XML schema-based documents, are encoded using the non-schema-based encoding. If you later use ALTER TABLE with ALLOW ANYSCHEMA on the same table, this has no effect on the encoding of documents that were stored prior to the ALTER TABLE operation — all such documents continue to be encoded using the non-schema-based encoding, regardless of whether they reference an XML schema. Only XML schema-based documents that you insert in the table after the ALTER TABLE operation are encoded using XML schema-based encodings.

Unstructured Storage of XML Schema-Based Data

You use STORE AS CLOB during table creation to specify unstructured storage. In this case, an entire XML document is stored in a single CLOB column.

Example 7-21 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 CLOB column.

Example 7-21 Specifying CLOB Storage for Schema-Based XMLType Tables and Columns

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.

Structured Storage of XML Schema-Based Data

With structured storage, collections are mapped into SQL varray values. An XML collection is any element that has maxOccurs > 1, allowing it to appear multiple times. By default, the entire contents of such a varray is stored as a set of rows in an ordered collection table (OCT).

Example 7-22 illustrates specifying additional storage options. The LineItem collection varray is stored as a LOB, not as a table. USERS is the tablespace used for storing element Notes. The table is compressed for online transaction processing (OLTP).

Example 7-22 Specifying Structured Storage Options for XMLType Tables and Columns

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"."LineItems"."LineItem" STORE AS LOB lineitem_lob
  LOB ("XMLDATA"."Notes")
    STORE AS (TABLESPACE USERS ENABLE STORAGE IN ROW 
              STORAGE(INITIAL 4K NEXT 32K))
    COMPRESS FOR OLTP;

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"."LineItems"."LineItem" STORE AS LOB lineitem_lob
  LOB (xml_document."XMLDATA"."Notes")
    STORE AS (TABLESPACE USERS ENABLE STORAGE IN ROW 
              STORAGE(INITIAL 4K NEXT 32K))
    COMPRESS FOR OLTP;

Note:

In releases prior to Oracle Database 11gR2, the default behavior for CREATE TABLE was to store a collection using a varray stored as a LOB, not a varray stored as a table.

Note:

When compression is specified for a parent XMLType table or column, all descendant XMLType ordered collection tables (OCTs) are similarly compressed.

See Also:

As a convenience, if you need to specify that all varrays in an XMLType table or column are to be stored as LOBs, or all are to be stored as tables, then you can use the syntax clause STORE ALL VARRAYS AS, followed by LOBS or TABLES, respectively. This is a convenient alternative to using multiple VARRAY...STORE AS clauses, one for each collection. Example 7-23 illustrates this.

Example 7-23 Using STORE ALL VARRAYS AS

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"
  STORE ALL VARRAYS AS LOBS;

The storage method specified using STORE ALL VARRAYS AS overrides any storage method specified using xdb:storeVarrayAsTable in the corresponding XML schema.

See Also:

Specifying Relational Constraints on XMLType Tables and Columns

When you store XML data using structured storage, typical relational constraints can be specified for elements and attributes that occur only once in an XML document. Example 7-22 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

You can annotate XML schemas to influence the objects and tables that are generated by the XML schema registration process. You do this by adding Oracle-specific attributes to complexType, element, and attribute definitions that are declared by the XML schema.

Most XML attributes used by Oracle XML DB belong to the namespace http://xmlns.oracle.com/xdb. XML attributes used for encoding XML data as binary XML belong to the namespace http://xmlns.oracle.com/2004/CSX. To simplify the process of annotating an XML schema, Oracle recommends that you declare namespace prefixes in the root element of the XML schema.

Common Uses of XML Schema Annotations

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

  • To ensure that the names of the tables, objects, and object attributes created by PL/SQL procedure DBMS_XMLSCHEMA.registerSchema for structured storage of XML data are easy to recognize and compliant with any application-naming standards. Set parameter GENTYPES or GENTABLES to TRUE for this.

  • To map between the XML schema and existing objects and tables within the database. Set parameter GENTYPES or GENTABLES to FALSE for this.

  • To prevent the generation of mixed-case names that require the use of quoted identifiers when working directly with SQL.

  • To allow XPath rewrite for structured storage in the case of document-correlated recursive XPath queries. This applies to certain applications of SQL/XML access and query functions whose XQuery-expression argument targets recursive XML data.

The most commonly used XML schema annotations are the following:

  • xdb:defaultTable – Name of the default table generated for each global element when parameter GENTABLES is TRUE. Setting this to the empty string, "", prevents a default table from being generated for the element in question.

  • xdb:SQLName – Name of the SQL object attribute that corresponds to each element or attribute defined in the XML schema.

  • xdb:SQLType – For complexType definitions, the corresponding object type. For simpleType definitions, SQLType is used to override the default mapping between XML schema data types and SQL data types. A common use of SQLType is to define when unbounded strings should be stored as CLOB values, rather than as VARCHAR(4000) CHAR values (the default). Note: You cannot use data type NCHAR, NVARCHAR, or NCLOB as the value of a SQLType annotation.

  • xdb:SQLCollType – Used to specify the varray type that manages a collection of elements.

  • xdb:maintainDOM – Used to determine whether or not DOM fidelity should be maintained for a given complexType definition

  • xdb:storeVarrayAsTable – Specified in the root element of the XML schema. Used to force all collections to be stored as ordered collection tables (OCTs). An OCT is created for each element that is specified with maxOccurs > 1. The OCTs are created with system-generated names. The default value of storeVarrayAsTable is true.

You need not specify values for any of these attributes. Oracle XML DB provides appropriate values by default during the XML schema registration process. However, if you are using structured storage, then Oracle recommends that you specify the names of at least the top-level SQL types, so that you can reference them later.

XML Schema Annotation Example

Example 7-24 shows a partial listing of the XML schema in Example 7-1, modified to include some of the most important Oracle XML DB annotations.

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

The schema element includes the declaration of the xdb namespace. It also includes the annotation xdb:storeVarrayAsTable = "true" (which is the default value). This causes all collections within the XML schema to be managed using ordered collection tables (OCTs).

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

The definition of global complex type PurchaseOrderType includes a SQLType annotation that specifies that the generated SQL object type is named purchaseorder_t. Within the definition of this type, the following annotations are used:

  • The definition of element Reference includes a SQLName annotation that specifies that the SQL attribute corresponding to XML element Reference is named reference.

  • The definition of element Actions includes a SQLName annotation that specifies that the SQL attribute corresponding to XML element Actions is named action_collection.

  • The definition of element USER includes a SQLName annotation that specifies that the SQL attribute corresponding to XML element User is named email.

  • The definition of element LineItems includes a SQLName annotation that specifies that the SQL attribute corresponding to XML element LineItems is named lineitem_collection.

  • The definition of element Notes includes a SQLType annotation that specifies that the data type of the SQL attribute corresponding to XML element Notes is CLOB.

The definition of global complex type LineItemsType includes a SQLType annotation that specifies that the generated SQL object type is named lineitems_t. Within the definition of this type, the following annotations are used:

  • The definition of element LineItem includes a SQLName annotation that specifies that the data type of the SQL attribute corresponding to XML element LineItems is named lineitem_varray, and a SQLCollName annotation that specifies that the SQL object type that manages the collection is named lineitem_v.

The definition of global complex type LineItemType includes a SQLType annotation that specifies that generated SQL object type is named lineitem_t.

The definition of complex type PartType includes a SQLType annotation that specifies that the SQL object type is named 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 data type.

Example 7-25 shows some of the tables and objects that are created when the annotated XML schema is registered.

Example 7-25 Registering an Annotated XML Schema

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.

The following are results of this XML schema registration:

  • A table called purchaseorder was created.

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

  • The Notes attribute defined by purchaseorder_t is of data type CLOB.

  • Type part_t does not include a positional descriptor (PD) attribute.

  • Ordered collection tables (OCTs) were created to manage the collections of LineItem and Action elements.

Available Oracle XML DB XML Schema Annotations

Table 7-3, Table 7-4, and Table 7-5 list Oracle XML DB annotations that you can specify in element and attribute declarations. All annotations except those that have the prefix csx are applicable to XML schemas registered for structured storage. This includes the portions of hybrid storage that are stored object-relationally.

The following annotations apply to XML schemas that are registered for unstructured storage:

  • xdb:defaultTable

  • xdb:defaultTableSchema

The following annotations apply to XML schemas that are registered for binary XML storage:

  • xdb:defaultTable

  • xdb:defaultTableSchema

  • xdb:tableProps

Table 7-3 Annotations in Elements

Attribute Values Default Description

xdb:columnProps

Any 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 SQL tables, namely top-level element declarations and out-of-line element declarations.

xdb:defaultTable

Any table name

Based on element name

Specifies the name of the SQL table into which XML instances of this XML schema are stored. This is most useful in cases where the XML data is inserted from APIs and protocols, such as FTP and HTTP(S), where the table name is not specified. Applicable to structured storage and binary XML storage.

xdb:defaultTableSchema

Any SQL user name

User registering XML schema

Name of the database user (database schema) who owns the type specified by xdb:defaultTable. Applicable to structured storage and binary XML storage.

 

xdb:maintainDOM

true | false

true

If true, then instances of this element are stored so 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, then the output is not guaranteed to have the same DOM action as the input.

xdb:maintainOrder

true | false

true

If true (generally recommended, and the default value), then the collection is mapped to a varray (stored in a LOB or an ordered collection table).

If false, then the collection is mapped to an unordered table, and document order is not preserved.

xdb:maxOccurs

Any positive integer

1

Specifies the maximum number of times an element can appear. If the value is unbounded, then there is no limit to the maximum number of occurrences.

xdb:SQLCollSchema

Any SQL user name

User registering XML schema

Name of the database user (database schema) who owns the type specified by xdb:SQLCollType.

xdb:SQLCollType

Any SQL collection type

Name generated from element name

Name of the SQL collection type that corresponds to this XML element. The XML element must be specified with maxOccurs > 1.

xdb:SQLInline

true | false

true

If true, then this element is stored inline as an embedded object attribute (or as a collection, if maxOccurs > 1).

If false, then 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.

xdb:SQLName

Any SQL identifier

Element name

Name of the attribute within the SQL object that maps to this XML element.

xdb:SQLSchema

Any SQL user name

User registering XML schema

Name of the database user (database schema) who owns the type specified by SQLType.

xdb:SQLType

Any SQL data typeFoot 1 , except NCHAR, NVARCHAR, and NCLOB

Name generated from element name

Name of the SQL type corresponding to this XML element declaration.

xdb:srclang

true | false

true

If true, then the given language translation is used as the default translation.

xdb:tableProps

Any 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. Applicable to structured storage and binary XML storage.

xdb:translate

true | false

true

If true, then instances of this element are translated. The maxOccurs attribute must be <=1 for this element to be set to true.


Footnote 1 See "Mapping XML Schema Data Types to SQL Data Types".

See Also:

"Structured Storage of XML Schema-Based Data" for information about specifying storage options when manually creating XMLType tables for object-relational storage

Table 7-4 Annotations in Elements Declaring Global complexType Elements

Attribute Values Default Description
 

xdb:maintainDOM

true | false

true

If true, then instances of this element are stored so 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, then the output is not guaranteed to have the same DOM action as the input.

xdb:SQLSchema

Any SQL user name

User registering XML schema

Name of the database user (database schema) who owns the type specified by SQLType.

xdb:SQLType

Any SQL data typeFoot 1  except NCHAR, NVARCHAR, and NCLOB

Name generated from element name

Name of the SQL type that corresponds to this XML element declaration.


Footnote 1 See "Mapping XML Schema Data Types to SQL Data Types".

Table 7-5 Annotations in XML Schema Declarations

Attribute Values Default Description

xdb:mapUnboundedStringToLob

true | false

false

If true, then unbounded strings are mapped to CLOB instances by default. Similarly, unbounded binary data gets mapped to a BLOB value, by default.

If false, then unbounded strings are mapped to VARCHAR2(4000) values, and unbounded binary components are mapped to RAW(2000) values.

xdb:storeVarrayAsTable

true | false

true

If true, then the varray is stored as a table (OCT).

If false, then the varray is stored in a LOB.


See Also:

"Changing an XML Schema and XML Instance Documents for Translation" for more information on xdb:maxOccurs, xdb:translate, and xdb:srclang.

XML Schema Annotation Guidelines for Structured Storage

For XMLType data stored object-relationally (structured storage), careful planning is called for, to optimize performance. Similar considerations are in order as for ordinary relational data: the entity-relationship model, indexing, data types, table partitions, and so on.

To enable XPath rewrite and achieve optimal performance, you implement many such design choices using XML schema annotations. This section provides annotation guidelines to optimize the use of XMLType data stored object-relationally.

Avoid Creation of Unnecessary Tables for Unused Top-Level Elements

By default, XML schema registration creates a top-level table for each top-level element defined in the XML schema. Some such elements are used as top-level elements in XML instances that conform to the XML schema. Others might not. It is common, for example, for elements in an XML schema to be top-level in order to be used as a REF target.

Whenever a top-level element in an XML schema is never used at the top level in any corresponding XML instance, you can avoid the creation of the associated unnecessary tables by adding annotation xdb:defaultTable = "" to the element in the XML schema. An empty value for this attribute prevents default-table creation.

Provide Your Own Names for Default Tables

For tuning purposes, you examine execution plan output for queries you are interested in. This output refers to the tables that underlie XMLType data stored object-relationally. By default, these tables have system-generated names. Oracle recommends that you provide your own table names, especially for tables that you are sure to be interested in. You do that using annotation xdb:defaultTable.

Turn Off DOM Fidelity If Not Needed

By default, XML schema registration generates tables that store XML data in such a way that DOM fidelity is maintained. It is often the case that for data-centric XML data DOM fidelity is not needed. You can improve the performance of storage, queries, and data modification by instead using object-relational tables that do not maintain DOM fidelity. You use the annotation xdb:maintainDOM = "false" to do that.

See Also:

"DOM Fidelity"

Use Unordered Collection Elements When Order Doesn't Matter

If the order among collection elements is not important, then use annotation xdb:maintainOrder = "false". This can allow more optimization in XPath rewrite and can generally lead to more efficient query execution.

Annotate Time-Related Elements with a Timestamp Data Type

If your application needs to work with time-zone indicators, then annotate any XML schema elements of type xs:time and xs:dateTime with xdb:SQLType = "TIMESTAMP WITH TIME ZONE". This ensures that values containing time-zone indicators can be stored, retrieved, and compared.

Add Table and Column Properties

If a table or column underlying XML data needs additional properties, such as partition, tablespace, or compression clauses, then use annotation xdb:tableProps or xdb:columnProps to provide them. This lets users add primary keys or constraints. For example, to achieve table compression for online transaction processing (OLTP), you would add COMPRESS FOR OLTP using a tableProps attribute.

See Also:

Example 7-22 for an example of specifying OLTP compression when creating XMLType tables and columns manually

Store Large Collections Out of Line

When the total number of elements and attributes defined by a complexType reaches 1000, it is not possible to create a single table that can manage the SQL objects that are generated when an instance of that type is stored in the database. If you have large collections, then you might run up against this limit of 1000 columns for a table.

You can use annotations xdb:defaultTable and xdb:SQLInline to specify that such collection elements be stored out of line. That means that their data is stored in a separate table — only a reference to a row in that table is stored in the main collection table. Use xdb:defaultTable to name the out-of -line table. Annotate each element of a potentially large collection with xdb:SQLInline = "false", to store it out of line.

Querying a Registered XML Schema to Obtain Annotations

The registered version of an XML schema contains a full set of Oracle XML DB annotations. As shown in Example 7-8 and Example 7-9, the location of the registered XML schema depends on whether it is local or global.

A registered XML schema can be queried for the annotations that were supplied by the user or added by the schema registration process. Example 7-26 shows the set of global complexType definitions declared by an XML schema for structured storage of XML data, and the corresponding SQL object types and DOM fidelity values.

Example 7-26 Querying Metadata from a Registered XML Schema

SELECT ct.xmlschema_type_name, ct.sql_type_name, ct.dom_fidelity
  FROM RESOURCE_VIEW,
       XMLTable(
         XMLNAMESPACES (
           'http://xmlns.oracle.com/xdb/XDBResource.xsd' AS "r",
           'http://xmlns.oracle.com/xdb/documentation/purchaseOrder' AS "po",
           'http://www.w3.org/2001/XMLSchema' AS "xs",
           'http://xmlns.oracle.com/xdb' AS "xdb"),
         '/r:Resource/r:Contents/xs:schema/xs:complexType' PASSING RES
         COLUMNS
           xmlschema_type_name VARCHAR2(30) PATH '@name',
           sql_type_name       VARCHAR2(30) PATH '@xdb:SQLType',
           dom_fidelity        VARCHAR2(6)  PATH '@xdb:maintainDOM') 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.

Mapping XML Schema Data Types to Oracle XML DB Storage

XML data that conforms to an XML schema is typed using XML Schema data types. When this XML data is stored in Oracle XML DB, its storage data types are derived from the XML Schema data types using a default mapping and, optionally, using mapping information that you specify using XML schema annotations.

Whenever you do not specify a data type to use for storage, Oracle XML DB uses the default mapping to annotate the XML schema appropriately, during registration. In this way, the registered XML schema has a complete set of data-type annotations.

  • For unstructured storage, the data-type mapping is trivial: all of the XML data is stored together as a single CLOB.

  • For structured storage, XML Schema data types are mapped to SQL data types.

  • For binary XML storage, XML Schema data types are mapped to Oracle XML DB binary XML encoding types.

Figure 7-5 shows how Oracle XML DB creates XML schema-based XMLType tables using an XML document and a mapping specified in an XML schema. Depending on the storage method specified in the XML schema, an XML instance document is stored either as a binary XML or CLOB value in a single XMLType column, or using multiple object-relational columns.

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

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

Mapping XML Schema Data Types to SQL Data Types

This section describes how to use PL/SQL package DBMS_XMLSCHEMA to map data types for XML Schema attributes and elements to SQL data types.

Note:

Do not directly access the SQL data types that are mapped from XML Schema data types during XML schema registration. These SQL types are part of the implementation of Oracle XML DB. They are not exposed for your use.

Oracle reserves the right to change the implementation at any time, including in a product patch. Such a change by Oracle will have no effect on applications that abide by the XML abstraction, but it might impact applications that directly access these data types.

Example of Mapping XML Schema Data Types to SQL

Example 7-27 shows a simple example of mapping XML Schema data types to SQL data types. It uses attribute SQLType to specify the data-type mapping. It also uses attribute SQLName to specify the object attributes to use for various XML elements and attributes.

Example 7-27 Mapping XML Schema Data Types to SQL Data Types using Attribute SQLType

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

Mapping XML Schema Attribute Data Types to SQL

An attribute declaration can specify its XML Schema data type 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 data type, its associated information (length, precision), and the memory mapping information are derived from the simpleType on which the attribute is based.

Overriding the SQLType Value in an XML Schema When Declaring Attributes

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

  • If the default SQL data type is STRING, you can override it with CHAR, VARCHAR, or CLOB.

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

Mapping XML Schema Element Data Types to SQL

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

  • Any of the ways for specifying type for an attribute declaration. See "Mapping XML Schema Attribute Data Types to SQL" .

  • 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 an XML Schema when Declaring Elements

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

  • VARCHAR2

  • RAW

  • CLOB

  • BLOB

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

For example, to override the SQLType from VARCHAR2 to CLOB, declare the xdb namespace using 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 might be created.

    • If SQLInline = "true", then a varray type is created whose element type is the SQL data type previously determined. Cardinality of the varray is based on the value of attribute maxOccurs. Either you specify the name of the varray type using attribute SQLCollType, or it is derived from the element name.

    • If SQLInline = "false", then the SQL data type is set to XDB.XDB$XMLTYPE_REF_LIST_T. This is a predefined data type that represents an array of REF values pointing to XMLType instances.

  • If the element is a global element, or if SQLInline = "false", then the system creates a default table. Either you specify the name of the default table, or it is derived from the element name.

See Also:

Chapter 9, "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 7-6 shows an example of this.

Figure 7-6 Mapping simpleType: XML Strings to SQL VARCHAR2 or CLOB

Description of Figure 7-6 follows
Description of "Figure 7-6 Mapping simpleType: XML Strings to SQL VARCHAR2 or CLOB"

Table 7-6 through Table 7-9 present the default mapping of XML Schema simpleType to SQL, as specified in the XML Schema definition. For example:

  • A XML Schema primitive type is mapped to the closest SQL data type. For example, DECIMAL, POSITIVEINTEGER, and FLOAT are all mapped to SQL NUMBER.

  • An XML Schema enumeration type is mapped to a SQL object type with a single RAW(n) object attribute. The value of n is determined by the number of possible values in the enumeration declaration.

  • An XML Schema list or a union type is mapped to a SQL string (VARCHAR2 or CLOB) data type.

Table 7-6 Mapping XML Schema String Data Types to SQL

XML Schema String Type Length or MaxLength Facet Default SQL Data Type Compatible SQL Data Type

string

n

VARCHAR2(n) if n < 4000, else VARCHAR2(4000)

CHAR, CLOB

string

-

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

CHAR, CLOB


Table 7-7 Mapping XML Schema Binary Data Types (hexBinary/base64Binary) to SQL

XML Schema Binary Type Length or MaxLength Facet Default SQL Data Type Compatible SQL Data Type

hexBinary, base64Binary

n

RAW(n) if n < 2000, else RAW(2000)

RAW, BLOB

hexBinary, base64Binary

-

RAW(2000) if mapUnboundedStringToLob = "false", BLOB

RAW, BLOB


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

XML Schema Simple Type Default SQL Data Type totalDigits (m), fractionDigits(n) Specified Compatible SQL Data Types

float

NUMBER

NUMBER(m+n,n)

FLOAT, DOUBLE, BINARY_FLOAT

double

NUMBER

NUMBER(m+n,n)

FLOAT, DOUBLE, BINARY_DOUBLE

decimal

NUMBER

NUMBER(m+n,n)

FLOAT, DOUBLE

integer

NUMBER

NUMBER(m+n,n)

NUMBER

nonNegativeInteger

NUMBER

NUMBER(m+n,n)

NUMBER

positiveInteger

NUMBER

NUMBER(m+n,n)

NUMBER

nonPositiveInteger

NUMBER

NUMBER(m+n,n)

NUMBER

negativeInteger

NUMBER

NUMBER(m+n,n)

NUMBER

long

NUMBER(20)

NUMBER(m+n,n)

NUMBER

unsignedLong

NUMBER(20)

NUMBER(m+n,n)

NUMBER

int

NUMBER(10)

NUMBER(m+n,n)

NUMBER

unsignedInt

NUMBER(10)

NUMBER(m+n,n)

NUMBER

short

NUMBER(5)

NUMBER(m+n,n)

NUMBER

unsignedShort

NUMBER(5)

NUMBER(m+n,n)

NUMBER

byte

NUMBER(3)

NUMBER(m+n,n)

NUMBER

unsignedByte

NUMBER(3)

NUMBER(m+n,n)

NUMBER


Table 7-9 Mapping XML Schema Date and Time Data Types to SQL

XML Schema Date or Time Type Default SQL Data Type Compatible SQL Data Types

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 7-10 Default Mapping of Other XML Schema Primitive and Derived Data Types to SQL

XML Schema Primitive or Derived Type Default SQL Data Type Compatible SQL Data Types

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

none

normalizedString

VARCHAR2(4000)

none

token

VARCHAR2(4000)

none


NCHAR, NVARCHAR, and NCLOB SQLType Values are Not Supported

Oracle XML DB does not support NCHAR, NVARCHAR, and NCLOB as values for attribute SQLType: you cannot specify that an XML element or attribute is to be of type NCHAR, NVARCHAR, or NCLOB. Also, if you provide your own data type, do not use any of these data types.

simpleType: Mapping XML Strings to SQL VARCHAR2 Versus CLOB

If an XML schema specifies an XML Schema data type to be a string with a maxLength less than 4000, then it is mapped to a VARCHAR2 object 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 data 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, XML schema registration maps xsd:dateTime and xsd:time to SQL data type TIMESTAMP and all the other data types to SQL data type DATE. SQL data types TIMESTAMP and DATE do not permit a time-zone indicator.

If your application needs to work with time-zone indicators, then use attribute SQLType to specify the SQL data type as TIMESTAMP WITH TIME ZONE. This ensures that values containing time-zone indicators can be stored and retrieved correctly. For 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 lets the time-zone component be specified as Z, to indicate UTC time zone. When a value with a trailing Z is stored in a SQL TIMESTAMP WITH TIME ZONE column, the time zone is actually stored as +00:00. Thus, the retrieved value contains the trailing +00:00, not the original Z. For example, if the value in the input XML document is 1973-02-12T13:44:32Z, the output is 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:

  • XML attributes declared within the complexType are mapped to SQL object attributes. The simpleType defining an XML attribute determines the SQL data type of the corresponding object attribute.

  • XML elements declared within the complexType are also mapped to SQL object attributes. The simpleType or complexType defining an XML element determines the SQL data type of the corresponding object attribute.

If the XML element is declared with attribute maxOccurs > 1, then it is mapped to a SQL collection (object) attribute. The collection could be a varray value (the default, recommended) or an unordered table (if you set attribute xdb:maintainOrder to false). The default storage of a varray value is an ordered collections table (OCT). You can choose LOB storage instead, by setting attribute xdb:storeVarrayAsTable to false.

Specifying Attributes in a complexType XML Schema Declaration

When you have an element based on a global complexType, both 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.

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

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