You can store an ordered collection as a varray in an ordered collection table (OCT), which can be either a heap-based table (recommended) or an index-organized table (IOT). You can store the actual data out of line by using varray entries that are REFs to the data.

When you register an XML schema for XMLType data that is stored object-relationally and you set registration parameter GENTABLES to TRUE, default tables are created automatically to store the associated XML instance documents.

Order is preserved among XML collection elements when they are stored. The result is an ordered collection. You can store data in an ordered collection in these ways:

• Varray in a table. Each element in the collection is mapped to a SQL object. The collection of SQL objects is stored as a set of rows in a table, called an ordered collection table (OCT). All collections are stored in OCTs.

You can also use out-of-line storage for an ordered collection. This corresponds to XML schema annotation SQLInline = "false", and it means that a varray of REFs in the collection table (or the LOB) tracks the collection content, which is stored out of line.

There is no requirement to annotate an XML schema before using it. Oracle XML DB uses a set of default assumptions when processing an XML schema that contains no annotations.

If you do not supply any of the annotations mentioned in this section, then Oracle XML DB stores a collection as a heap-based OCT. You can force OCTs to be stored as index-organized tables (IOTs) instead, by passing REGISTER_NT_AS_IOT in the OPTIONS parameter of DBMS_XMLSCHEMA.registerSchema.

Use TRUE as the value of parameter GENTYPES when you register an XML schema for use with XML data stored object-relationally (TRUE is the default value). Oracle XML DB then creates the appropriate SQL object types that enable object-relational 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 18-1 shows the SQL object types that are created automatically when XML schema purchaseOrder.xsd is registered with Oracle XML DB.

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

You can create default tables as part of XML schema registration. Default tables are most useful when documents are inserted using APIs and protocols such as FTP and HTTP(S), which do not provide any table specification.

In such cases, the XML instance is inserted into the default table.

Example 18-2 describes the default purchase-order table.

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.

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"

In general, the SQL constructs (data types, nested tables, and tables associated with out-of-line storage) that are automatically 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 based on specific internal 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 annotations that name generated constructs.

The names of any SQL tables, objects, and attributes generated by XML schema registration are case sensitive.

For instance, in Example 18-2, the name of table PurchaseOrder1669_TAB is derived from the name of element PurchaseOrder, so it too is mixed case. You must therefore refer to this table 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.

In order to provide DOM fidelity for XML data that is stored object-relationally, Oracle XML DB records all information that cannot be stored in any of the other object attributes as instance-level metadata using the system-defined binary object attribute SYS_XDBPD$ (positional descriptor, or PD).

With object-relational storage of XML data, the elements and attributes declared in an XML schema are mapped to separate attributes of 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

In order to provide DOM fidelity for XML data stored object-relationally, Oracle XML DB uses a separate mechanism to keep track of this information: it is recorded as 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.

You can annotate an XML schema to customize the names of object-relational tables, objects, and object attributes or to allow XPath rewrite when XQuery-expression arguments target recursive XML data.

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 object-relational storage of XMLType data are easy to recognize and compliant with any application-naming standards. Set parameter GENTYPES or GENTABLES to TRUE for this (TRUE is the default value for each of these parameters).

• 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 object-relational 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, NVARCHAR2, or NCLOB as the value of a SQLType annotation.

  Note:

  Starting with Oracle Database 12c Release 2 (12.2.0.1), if you register an XML schema for object-relational storage for an application common user then you must annotate each complex type in the schema with xdb:SQLType, to name the SQL data type. Otherwise, an error is raised.

• 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

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 object-relational storage, then Oracle recommends that you specify the names of at least the top-level SQL types, so that you can reference them later.

A sample XML schema illustrates some of the most important Oracle XML DB annotations.

The XML schema in Example 18-3 is similar to the one in Example A-2, but it also defines a Notes element and its type, NotesType.

• The schema element includes the declaration of the xdb namespace.

• 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 annotation is 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 18-4 shows some of the tables and objects that are created when the annotated XML schema of Example 18-3 is registered.

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.

<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">
  <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:simpleType name="NotesType">
    <xs:restriction base="xs:string">
      <xs:minLength value="1"/>
      <xs:maxLength value="32767"/>
    </xs:restriction>
  </xs:simpleType>
</xs: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.

PL/SQL package DBMS_XMLSCHEMA_ANNOTATE provides subprograms to annotate an XML schema. Using these subprograms can often be more convenient and less error prone than manually editing the XML schema.

In particular, you can use the PL/SQL subprograms in a script, which you can run at any time or multiple times, as needed. This can be especially useful if you are using a large XML schema or a standard or other third-party XML schema that you do not want to modify manually.

There are specific PL/SQL subprograms for each Oracle annotation. For example, you use PL/SQL procedure setDefaultTable to add a xdb:defaultTable annotation, and removeDefaultTable to remove a xdb:defaultTable annotation.

Each annotation subprogram has the following as its parameters:

• The XML schema to be annotated. This parameter is IN OUT.

• The name of the global element where the annotation is to be added or removed.

• The annotation (XML attribute) value.

• A Boolean flag indicating whether any corresponding existing annotation is to be overwritten. By default, it is overwritten.

If the element to be annotated is not a global element then you provide the local element name as an additional parameter. The global and local names together identify the target element. The element with the local name must be a descendent of the element with the global name.

If you use SQL*Plus, you can use PL/SQL procedure DBMS_XMLSCHEMA_ANNOTATE.printWarnings to enable and disable printing of SQL*Plus warnings during the use of other DBMS_XMLSCHEMA_ANNOTATE subprograms. By default, no warnings are printed. An example of a warning is an inability to annotate the XML schema because there is no element with the name you provided to the annotation subprogram.

Example 18-5 uses subprograms in PL/SQL package DBMS_XMLSCHEMA_ANNOTATE to produce the annotated XML schema shown in Example 18-3.

CREATE TABLE annotation_tab (id NUMBER, inp XMLType, out XMLType);
INSERT INTO annotation_tab VALUES (1, ... unannotated XML schema...);

DECLARE
  schema XMLType;
BEGIN
  SELECT t.inp INTO schema FROM annotation_tab t WHERE t.id = 1;

  DBMS_XMLSCHEMA_ANNOTATE.setDefaultTable(schema, 'PurchaseOrder', 'PURCHASEORDER');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLType(schema, 'PurchaseOrderType', 'PURCHASEORDER_T');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLName(schema, 'complexType', 'PurchaseOrderType', 'element', 'Reference',
                                     'REFERENCE');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLName(schema, 'complexType', 'PurchaseOrderType', 'element', 'Actions',
                                     'ACTIONS_COLLECTION');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLName(schema, 'complexType', 'PurchaseOrderType', 'element', 'User', 'EMAIL');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLName(schema, 'complexType', 'PurchaseOrderType', 'element', 'LineItems',
                                     'LINEITEM_COLLECTION');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLType(schema, 'complexType', 'PurchaseOrderType', 'element', 'Notes', 'CLOB');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLType(schema, 'LineItemsType', 'LINEITEMS_T');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLCollType(schema, 'complexType', 'LineItemsType', 'LineItem', 'LINEITEM_V');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLName(schema, 'complexType', 'LineItemsType', 'element', 'LineItem',
                                     'LINEITEM_VARRAY');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLType(schema, 'LineItemType', 'LINEITEM_T');
  DBMS_XMLSCHEMA_ANNOTATE.setSQLType(schema, 'PartType', 'PART_T');
  DBMS_XMLSCHEMA_ANNOTATE.disableMaintainDom(schema, 'PartType');

UPDATE annotation_tab t SET t.out = schema WHERE t.id = 1;
END;
/

The Oracle XML DB annotations that you can specify in element and attribute declarations are described, along with the PL/SQL subprograms in package DBMS_XMLSCHEMA_ANNOTATE that you can use to manipulate them.

xdb:columnProps

xdb:defaultTable

setDefaultTable
removeDefaultTable
enableDefaultTableCreation
disableDefaultTableCreation

xdb:maintainDOM

enableMaintainDOM
disableMaintainDOM

xdb:SQLCollType

setSQLCollType
removeSQLCollType

xdb:SQLInline

setOutOfLine
removeOutOfLine

xdb:SQLName

setSQLName
removeSQLName

xdb:SQLType

setSQLType
removeSQLType

xdb:tableProps

setTableProps
removeTableProps

^Footnote 1

See Use DBMS_XMLSCHEMA to Map XML Schema Data Types to SQL Data Types.

xdb:maintainDOM

enableMaintainDom
disableMaintainDom

xdb:SQLType

setSQLType
removeSQLType

^Footnote 2

See Use DBMS_XMLSCHEMA to Map XML Schema Data Types to SQL Data Types.

For XMLType data stored object-relationally, careful planning is called for, to optimize performance. Similar considerations are in order as for relational data: entity-relationship models, 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.

• Avoid Creation of Unnecessary Tables for Unused Top-Level Elements
  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 associated 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 your queries. This 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 instead, especially for tables that you are sure to be interested in.
• Turn Off DOM Fidelity If Not Needed
  By default, XML schema registration generates tables that maintain DOM fidelity. 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.
• 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 object-relational XMLType data needs additional properties specified, such as partition, tablespace, or compression, use annotation xdb:tableProps or xdb:columnProps. You can do this to add primary keys or constraints, for example.
• Store Large Collections Out of Line
  If you have large collections then you might need to use annotations xdb:defaultTable and xdb:SQLInline to specify that collection elements be stored out of line.

You can query database views USER_XML_SCHEMAS and ALL_XML_SCHEMAS to obtain a registered XML schema with all of its annotations. The registered version of an XML schema contains a full set of Oracle XML DB annotations. These annotations were supplied by a user or set by default during XML schema registration.

SELECT SCHEMA FROM USER_XML_SCHEMAS
  WHERE SCHEMA_URL = 'http://xmlns.oracle.com/xdb/documentation/purchaseOrder.xsd';

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.

• You Can Apply Annotations from One XML Schema to Another
  Sometimes you need to apply the annotations from one XML schema to another XML schema. A typical use case is applying the annotations from an older version of a schema to a new version. You can get and set annotations using PL/SQL subprograms getSchemaAnnotations and setSchemaAnnotations, respectively.

An example illustrates mapping XML Schema data types to SQL data types.

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb"
           version="1.0">
  <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>

An XML attribute declaration can specify its XML Schema data type in terms of a primitive type, a local simpleType, a global simpleType, or a reference to a global attribute (ref=".."). The SQL data type and its associated information are derived from the base XML Schema type.

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

• You Can Override the SQLType Value in an XML Schema When Declaring Attributes
  You can explicitly specify a SQLType value in an XML schema, as an annotation. The SQL data type that you specify is used for XML schema validation, overriding the default SQL data types.

An XML element declaration can specify its XML Schema data type using a primitive type, a local or global simpleType, a local or global complexType, or a reference to a global element (ref=".."). The SQL data type and its associated information are derived from the base XML Schema type.

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

• Any of the ways for specifying type for an attribute declaration. See XML Schema Attribute Data Types Mapped 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.

• Override of 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 its sub-elements and attributes. You can override this mapping by explicitly specifying a value for attribute SQLType in the input XML schema.

XML simpleType is mapped to SQL object types in various ways, depending on how the simpleType is defined.

Figure 18-1 illustrates one such mapping, XML string type to SQL VARCHAR2 or CLOB.

Figure 18-1 simpleType Mapping: XML Strings to SQL VARCHAR2 or CLOB

Description of "Figure 18-1 simpleType Mapping: XML Strings to SQL VARCHAR2 or CLOB"

Table 18-3 through Table 18-6 present the default mapping of XML Schema simpleType to SQL, as specified in the XML Schema definition.

For example:

• An 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.

• NCHAR, NVARCHAR2, and NCLOB SQLType Values Are Not Supported for SQLType
  Oracle XML DB does not support NCHAR, NVARCHAR2, and NCLOB as values for attribute SQLType: You cannot specify that an XML element or attribute is to be of type NCHAR, NVARCHAR2, or NCLOB. Also, if you provide your own data type, do not use any of these data types.
• simpleType: How XML Strings Are Mapped to SQL VARCHAR2 Versus CLOB
  If an XML schema specifies a data type as a string with maxLength less than 4000, it is mapped to a VARCHAR2 object attribute of the specified length. If maxLength is not specified in the schema then the XML Schema data type can only be mapped to a LOB.
• How XML Schema Time Zones Are Mapped to SQL
  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.

XML complexType is mapped to SQL object types in various ways, depending on how the complexType is defined.

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 is a varray value that is an ordered collections table (OCT).

• Attribute Specification in a complexType XML Schema Declaration
  When an element is based on a global complexType, attribute SQLType must be specified for the complexType declaration. You can optionally include the same SQLType attribute within the element declaration.

For complexType, Oracle XML DB handles inheritance in an XML schema differently for types that extend and types that restrict other complex types

• For complex types declared to extend other complex types, the SQL type corresponding to the base type is specified as the supertype for the current SQL type. Only the additional attributes and elements declared in the sub-complextype are added as attributes to the sub-object-type.

• For complex types declared to restrict other complex types, the SQL type for the sub-complex type is set to be the same as the SQL type for its base type. This is because SQL does not support restriction of object types through the inheritance mechanism. Any constraints are imposed by the restriction in XML schema.

Example 18-9 shows the registration of an XML schema that defines a base complexType Address and two extensions USAddress and IntlAddress.

CREATE TYPE addr_t AS OBJECT(SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                             "street" VARCHAR2(4000),
                             "city" VARCHAR2(4000)) NOT FINAL;
CREATE TYPE usaddr_t UNDER addr_t ("zip" VARCHAR2(4000)) NOT FINAL;
CREATE TYPE intladdr_t UNDER addr_t ("country" VARCHAR2(4000)) FINAL;

Example 18-10 shows the registration of an XML schema that defines a base complexType Address and a restricted type LocalAddress that prohibits the specification of country attribute.

Because SQL inheritance does not support a notion of restriction, the SQL data type corresponding to a restricted complexType is a empty subtype of the parent object type. For the XML schema of Example 18-10, Oracle XML DB generates the following SQL types:

CREATE TYPE addr_t AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                              "street"   VARCHAR2(4000),
                              "city"     VARCHAR2(4000),
                              "zip"      VARCHAR2(4000),
                              "country"  VARCHAR2(4000)) NOT FINAL;
CREATE TYPE usaddr_t UNDER addr_t;

DECLARE
  doc VARCHAR2(3000) :=
    '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
                xmlns:xdb="http://xmlns.oracle.com/xdb">
       <xs:complexType name="Address" xdb:SQLType="ADDR_T">
         <xs:sequence>
           <xs:element name="street" type="xs:string"/>
           <xs:element name="city" type="xs:string"/>
         </xs:sequence>
       </xs:complexType>
       <xs:complexType name="USAddress" xdb:SQLType="USADDR_T">
         <xs:complexContent>
           <xs:extension base="Address">
             <xs:sequence>
               <xs:element name="zip" type="xs:string"/>
             </xs:sequence>
           </xs:extension>
         </xs:complexContent>
       </xs:complexType>
       <xs:complexType name="IntlAddress" final="#all" xdb:SQLType="INTLADDR_T">
         <xs:complexContent>
           <xs:extension base="Address">
             <xs:sequence>
               <xs:element name="country" type="xs:string"/>
             </xs:sequence>
           </xs:extension>
         </xs:complexContent>
       </xs:complexType>
     </xs:schema>';
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://www.oracle.com/PO.xsd',
    SCHAMEDOC => doc);
END;

DECLARE
  doc varchar2(3000) :=
    '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
                xmlns:xdb="http://xmlns.oracle.com/xdb">
       <xs:complexType name="Address" xdb:SQLType="ADDR_T">
         <xs:sequence>
           <xs:element name="street" type="xs:string"/>
           <xs:element name="city" type="xs:string"/>
           <xs:element name="zip" type="xs:string"/>
           <xs:element name="country" type="xs:string" minOccurs="0"
                       maxOccurs="1"/>
         </xs:sequence>
       </xs:complexType>
       <xs:complexType name="LocalAddress" xdb:SQLType="USADDR_T">
         <xs:complexContent>
           <xs:restriction base="Address">
             <xs:sequence>
               <xs:element name="street" type="xs:string"/>
               <xs:element name="city" type="xs:string"/>
               <xs:element name="zip" type="xs:string"/>
               <xs:element name="country" type="xs:string" 
                           minOccurs="0" maxOccurs="0"/>
             </xs:sequence>
           </xs:restriction>
         </xs:complexContent>
       </xs:complexType>
     </xs:schema>';
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://www.oracle.com/PO.xsd',
    SCHEMADOC => doc);
END;

A complex type based on a simpleContent declaration is mapped to an object type with attributes corresponding to the XML attributes and an extra SYS_XDBBODY$ attribute, which corresponds to the body value. The data type of the body attribute is based on a simpleType that defines the body type.

For the XML schema of Example 18-11, Oracle XML DB generates the following type:

CREATE TYPE obj_t AS OBJECT(SYS_XDBPD$ XDB.XDB$RAW_LIST_T, 
                            SYS_XDBBODY$ VARCHAR2(4000));

DECLARE
  doc VARCHAR2(3000) :=
    '<schema xmlns="http://www.w3.org/2001/XMLSchema"               
             targetNamespace="http://www.oracle.com/emp.xsd"      
             xmlns:emp="http://www.oracle.com/emp.xsd" 
             xmlns:xdb="http://xmlns.oracle.com/xdb"> 
       <complexType name="name" xdb:SQLType="OBJ_T"> 
         <simpleContent> 
           <restriction base="string"> 
           </restriction> 
         </simpleContent> 
       </complexType>
     </schema>';
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://www.oracle.com/emp.xsd',
    SCHEMADOC => doc);
END;

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

• The namespace attribute can be used to restrict the contents so that they belong to a specified namespace.

• The processContents attribute within the any element declaration, indicates the level of validation required for the contents matching the any declaration.

The code in Example 18-12 declares an any element and maps it to the column SYS_XDBANY$, in object type obj_t. It also declares that attribute processContents does not validate contents that match the any declaration.

For the XML schema of Example 18-12, Oracle XML DB generates the following type:

CREATE TYPE obj_t AS OBJECT(SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
                            Name VARCHAR2(4000), 
                            Age NUMBER, 
                            SYS_XDBANY$ VARCHAR2(4000));

DECLARE
  doc VARCHAR2(3000) :=
    '<schema xmlns="http://www.w3.org/2001/XMLSchema"  
             targetNamespace="http://www.oracle.com/any.xsd" 
             xmlns:emp="http://www.oracle.com/any.xsd" 
             xmlns:xdb="http://xmlns.oracle.com/xdb">
       <complexType name="Employee" xdb:SQLType="OBJ_T">
         <sequence>
           <element name="Name" type="string"/>
           <element name="Age" type="decimal"/>
           <any namespace="http://www/w3.org/2001/xhtml"
                processContents="skip"/>
         </sequence>
       </complexType>
     </schema>';
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://www.oracle.com/emp.xsd',
    SCHEMADOC => doc);
END;

You can specify partitioning information for an object-relational XMLType base table during either the XML schema registration or the table creation. Examples here illustrate this.

<xs:element name="PurchaseOrder" type="PurchaseOrderType"
            xdb:defaultTable="PURCHASEORDER"
            xdb:tableProps =
              "VARRAY XMLDATA.LINEITEMS.LINEITEM
                 STORE AS TABLE lineitem_table
                   ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)))
                   PARTITION BY RANGE (XMLDATA.Reference)
                     (PARTITION p1 VALUES LESS THAN (1000)
                        VARRAY XMLDATA.LINEITEMS.LINEITEM
                          STORE AS TABLE lineitem_p1 (STORAGE (MINEXTENTS 13)),
                      PARTITION p2 VALUES LESS THAN (2000)
                        VARRAY XMLDATA.LINEITEMS.LINEITEM
                          STORE AS TABLE lineitem_p2 (STORAGE (MINEXTENTS 13)))"/>

CREATE TABLE purchaseorder OF XMLType
  XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"
  ELEMENT "PurchaseOrder"
  VARRAY "XMLDATA"."LINEITEMS"."LINEITEM" STORE AS TABLE lineitem_table
    ((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)))
    PARTITION BY RANGE (XMLDATA.Reference)
      (PARTITION p1 VALUES LESS THAN (1000)
         VARRAY "XMLDATA"."LINEITEMS"."LINEITEM" STORE AS TABLE lineitem_p1
           (STORAGE (MINEXTENTS 13)),
       PARTITION p2 VALUES LESS THAN (2000)
         VARRAY  "XMLDATA"."LINEITEMS"."LINEITEM" STORE AS TABLE lineitem_p2
           (STORAGE (MINEXTENTS 13)));

You need not define or maintain child-table partitions manually. When you perform partition maintenance on the base (parent) table, corresponding maintenance is automatically performed on the child tables as well.

There are a few exceptions to the general rule that you perform partition maintenance only on the base table. In the following cases you perform maintenance on a child table:

• Modify the default physical storage attributes of a collection partition

• Modify the physical storage attributes of a collection partition

• Move a collection partition to a different segment, possibly in a different tablespace

• Rename a collection partition

For example, if you change the tablespace of a base table, that change is not cascaded to its child-table partitions. You must manually use ALTER TABLE MOVE PARTITION on the child-table partitions to change their tablespace.

Other than those exceptional operations, you perform all partition maintenance on the base table only. This includes operations such as adding, dropping, and splitting a partition.

Online partition redefinition is also supported for child tables. You can copy unpartitioned child tables to partitioned child tables during online redefinition of a base table. You typically specify parameter values copy_indexes => 0 and copy_constraints => false for PL/SQL procedure DBMS_REDEFINITION.copy_table_dependents, to protect the indexes and constraints of the newly defined child tables.

To create constraints on elements that can occur more than once, store the varray as an ordered collection table (OCT). You can then create constraints on the OCT. You might, for example, want to create a unique key based on an attribute of an element that repeats itself (a collection).

BEGIN DBMS_XMLSCHEMA.registerSchema(
  SCHEMAURL => 'emp.xsd',
  SCHEMADOC => '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
                           xmlns:xdb="http://xmlns.oracle.com/xdb">
                  <xs:element name="Employee" xdb:SQLType="EMP_TYPE">
                    <xs:complexType>
                      <xs:sequence>
                        <xs:element name="EmployeeId" type="xs:positiveInteger"/>
                        <xs:element name="PhoneNumber" maxOccurs="10"/>
                          <xs:complexType>
                            <xs:attribute name="No" type="xs:integer"/>
                          </xs:complexType>
                        </xs:element>
                      </xs:sequence>
                    </xs:complexType>
                  </xs:element>
                </xs:schema>',
   LOCAL     => FALSE, 
   GENTYPES  => FALSE); 
END;/

PL/SQL procedure successfully completed.

CREATE TABLE emp_tab OF XMLType
  XMLSCHEMA "emp.xsd" ELEMENT "Employee"
  VARRAY XMLDATA."PhoneNumber" STORE AS TABLE phone_tab;

Table created.

ALTER TABLE phone_tab ADD UNIQUE (NESTED_TABLE_ID, "No");

Table altered.

INSERT INTO emp_tab 
  VALUES(XMLType('<Employee>
                    <EmployeeId>1234</EmployeeId>
                    <PhoneNumber No="1234"/>
                    <PhoneNumber No="2345"/>
                  </Employee>').createSchemaBasedXML('emp.xsd'));

1 row created.

INSERT INTO emp_tab 
  VALUES(XMLType('<Employee>
                    <EmployeeId>3456</EmployeeId>
                    <PhoneNumber No="4444"/>
                    <PhoneNumber No="4444"/>
                  </Employee>').createSchemaBasedXML('emp.xsd'));

*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.SYS_C002136) violated

Set XML schema annotation xdb:SQLInline to false to store an XML fragment out of line. The element is mapped to a SQL object type with an embedded REF attribute, which points to another XMLType instance that is stored out of line and that corresponds to the XML fragment.

By default, a child XML element is mapped to an embedded SQL object attribute when XMLType data is stored object-relationally. However, there are scenarios where out-of-line storage offers better performance. In such cases, set XML schema annotation (attribute) xdb:SQLInline to false, so Oracle XML DB generates a SQL object type with an embedded REF attribute. The REF points to another XMLType instance that is stored out of line and that corresponds to the XML fragment. Default XMLType tables are also created, to store the out-of-line fragments.

Figure 18-2 illustrates the mapping of complexType to SQL for out-of-line storage.

Figure 18-2 Mapping complexType to SQL for Out-Of-Line Storage

Description of "Figure 18-2 Mapping complexType to SQL for Out-Of-Line Storage"

Note:

Starting with Oracle Database 11g Release 2 (11.2.0.2), you can create only one XMLType table that uses an XML schema that results in an out-of-line table. An error is raised if you try to create a second table that uses the same XML schema.

In Example 18-20, attribute xdb:SQLInline of element Addr has value false. The resulting SQL object type, obj_t2, has an XMLType column with an embedded REF object attribute. The REF attribute points to an XMLType instance of SQL object type obj_t1 in table addr_tab. Table addr_tab is stored out of line. It has columns street and city.

When registering this XML schema, Oracle XML DB generates the XMLType tables and types shown in Example 18-21.

Table emp_tab holds all of the employee information, and it contains an object reference that points to the address information that is stored out of line, in table addr_tab.

An advantage of this model is that it lets you query the out-of-line table (addr_tab) directly, to look up address information. Example 18-22 illustrates querying table addr_tab directly to obtain the distinct city information for all employees.

The disadvantage of this storage model is that, in order to obtain the entire Employee element, you must access an additional table for the address.

DECLARE
  doc VARCHAR2(3000) :=
    '<schema xmlns="http://www.w3.org/2001/XMLSchema"                     
             targetNamespace="http://www.oracle.com/emp.xsd"       
             xmlns:emp="http://www.oracle.com/emp.xsd"       
             xmlns:xdb="http://xmlns.oracle.com/xdb">
       <complexType name="EmpType" xdb:SQLType="EMP_T">
         <sequence>
           <element name="Name" type="string"/>
           <element name="Age" type="decimal"/>
           <element name="Addr" 
                    xdb:SQLInline="false"
                    xdb:defaultTable="ADDR_TAB">
             <complexType xdb:SQLType="ADDR_T">
               <sequence>
                 <element name="Street" type="string"/>
                 <element name="City" type="string"/>
               </sequence>
             </complexType>
           </element>
         </sequence>
       </complexType>
       <element name="Employee" type="emp:EmpType"
                xdb:defaultTable="EMP_TAB"/>
     </schema>';
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL        => 'emp.xsd',
    SCHEMADOC        => doc,
    ENABLE_HIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE);
END;
/

DESCRIBE emp_tab
 Name                          Null?    Type
 ----------------------------- -------- ----------------------------------------------------------
TABLE of SYS.XMLTYPE(XMLSchema "emp.xsd" Element "Employee") STORAGE Object-relational TYPE "EMP_T"
 
DESCRIBE addr_tab
 Name                          Null?    Type
 ----------------------------- -------- --------------------------------------------------------
TABLE of SYS.XMLTYPE(XMLSchema "emp.xsd" Element "Addr") STORAGE Object-relational TYPE "ADDR_T"

DESCRIBE emp_t
 emp_t is NOT FINAL
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 SYS_XDBPD$                             XDB.XDB$RAW_LIST_T
 Name                                   VARCHAR2(4000 CHAR)
 Age                                    NUMBER
 Addr                                   REF OF XMLTYPE

DESCRIBE addr_t
 Name                          Null?    Type
 ----------------------------- -------- --------------------
 SYS_XDBPD$                             XDB.XDB$RAW_LIST_T
 Street                                 VARCHAR2(4000 CHAR)
 City                                   VARCHAR2(4000 CHAR)
 

INSERT INTO emp_tab
  VALUES
    (XMLType('<x:Employee
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xmlns:x="http://www.oracle.com/emp.xsd"
                 xsi:schemaLocation="http://www.oracle.com/emp.xsd emp.xsd">
                <Name>Abe Bee</Name>
                <Age>22</Age>
                <Addr>
                  <Street>A Street</Street>
                  <City>San Francisco</City>
                </Addr>
              </x:Employee>'));
 
INSERT INTO emp_tab
  VALUES
    (XMLType('<x:Employee
                 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                 xmlns:x="http://www.oracle.com/emp.xsd"
                 xsi:schemaLocation="http://www.oracle.com/emp.xsd emp.xsd">
                <Name>Cecilia Dee</Name>
                <Age>23</Age>
                <Addr>
                  <Street>C Street</Street>
                  <City>Redwood City</City>
                </Addr>
              </x:Employee>'));
. . .

SELECT DISTINCT XMLCast(XMLQuery('/Addr/City' PASSING OBJECT_VALUE AS "." 
                                              RETURNING CONTENT)
                       AS VARCHAR2(20))
  FROM addr_tab;

CITY
-------------
Redwood City
San Francisco

You can store collection items out of line. Instead of a single REF column, the parent element contains a varray of REF values that point to the collection members.

DECLARE
  doc VARCHAR2(3000) :=
    '<schema xmlns="http://www.w3.org/2001/XMLSchema"
             targetNamespace="http://www.oracle.com/emp.xsd"
             xmlns:emp="http://www.oracle.com/emp.xsd"
             xmlns:xdb="http://xmlns.oracle.com/xdb">
       <complexType name="EmpType" xdb:SQLType="EMP_T">
         <sequence>
           <element name="Name" type="string"/>
           <element name="Age" type="decimal"/>
           <element name="Addr" xdb:SQLInline="false"
                    maxOccurs="unbounded" xdb:defaultTable="ADDR_TAB">
             <complexType xdb:SQLType="ADDR_T">
               <sequence>
                 <element name="Street" type="string"/>
                 <element name="City" type="string"/>
               </sequence>
             </complexType>
           </element>
         </sequence>
       </complexType>
       <element name="Employee" type="emp:EmpType"
                xdb:defaultTable="EMP_TAB"/>
     </schema>';
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL        => 'emp.xsd',
    SCHEMADOC        => doc,
    ENABLE_HIERARCHY => DBMS_XMLSCHEMA.ENABLE_HIERARCHY_NONE);
END;
/

DESCRIBE emp_t
 emp_t is NOT FINAL
 Name                                   Null?    Type
 -------------------------------------- -------- --------------------------
 SYS_XDBPD$                                      XDB.XDB$RAW_LIST_T
 Name                                            VARCHAR2(4000 CHAR)
 Age                                             NUMBER
 Addr                                            XDB.XDB$XMLTYPE_REF_LIST_T

DECLARE
  gen_name VARCHAR2 (4000);
BEGIN
  SELECT TABLE_NAME INTO gen_name FROM USER_NESTED_TABLES
    WHERE PARENT_TABLE_NAME = 'EMP_TAB';
  EXECUTE IMMEDIATE 'RENAME "' || gen_name || '"TO emp_tab_reflist';
END;
/
 
DESCRIBE emp_tab_reflist
 Name                    Null?    Type
 ----------------------- -------- ----------------
 COLUMN_VALUE                     REF OF XMLTYPE

SELECT em.name, ad.street
  FROM emp_tab,
       XMLTable(XMLNAMESPACES ('http://www.oracle.com/emp.xsd' AS "x"),
                '/x:Employee' PASSING OBJECT_VALUE
                COLUMNS name   VARCHAR2(20) PATH 'Name') em,
       XMLTable(XMLNAMESPACES ('http://www.oracle.com/emp.xsd' AS "x"),
                '/x:Employee/Addr' PASSING OBJECT_VALUE
                COLUMNS street VARCHAR2(20) PATH 'Street',
                        city   VARCHAR2(20) PATH 'City') ad
  WHERE ad.city = 'San Francisco';
 
NAME                 STREET
-------------------- --------------------
Abe Bee              A Street
Eve Fong             E Street
George Hu            G Street
Iris Jones           I Street
Karl Luomo           K Street
Marina Namur         M Street
Omar Pinano          O Street
Quincy Roberts       Q Street
 
8 rows selected.

|   4 |    TABLE ACCESS FULL         | EMP_TAB_REFLIST |    32 |   640 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS BY INDEX ROWID| EMP_TAB         |     1 |    29 |     1   (0)| 00:00:01 |
|*  6 |    INDEX UNIQUE SCAN         | SYS_C005567     |     1 |       |     0   (0)| 00:00:01 |

ALTER TABLE emp_tab_reflist ADD SCOPE FOR (COLUMN_VALUE) IS addr_tab;
CREATE INDEX reflist_idx ON emp_tab_reflist (COLUMN_VALUE);

|   4 |    TABLE ACCESS BY INDEX ROWID| EMP_TAB_REFLIST |     1 |    20 |     1   (0)| 00:00:01 |
|*  5 |     INDEX RANGE SCAN          | REFLIST_IDX     |     1 |       |     0   (0)| 00:00:01 |
|   6 |   TABLE ACCESS BY INDEX ROWID | EMP_TAB         |       |       |            |          |
|*  7 |    INDEX UNIQUE SCAN          | SYS_C005567     |     1 |       |     0   (0)| 00:00:01 |

The W3C XML Schema Recommendation lets complexTypes and global elements contain recursive references. This kind of structure allows for instance documents where the element in question can appear an infinite number of times in a recursive hierarchy.

For example, a complexType definition can contain an element based on that same complexType, or a global element can contain a reference to itself. In both cases the reference can be direct or indirect.

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
           xmlns:xdb="http://xmlns.oracle.com/xdb" 
           elementFormDefault="qualified" attributeFormDefault="unqualified">
  <xs:element name="person" type="personType" xdb:defaultTable="PERSON_TABLE"/>
  <xs:complexType name="personType" xdb:SQLType="PERSON_T">
    <xs:sequence>
      <xs:element name="descendant" type="personType" minOccurs="0"  
                  maxOccurs="unbounded" xdb:SQLName="DESCENDANT"
                  xdb:defaultTable="DESCENDANT_TABLE"/>
    </xs:sequence>
    <xs:attribute name="personName" use="required" xdb:SQLName="PERSON_NAME"> 
      <xs:simpleType>
        <xs:restriction base="xs:string">
          <xs:maxLength value="20"/>
        </xs:restriction>
      </xs:simpleType>
    </xs:attribute>
  </xs:complexType>
</xs:schema>

• For Circular XML Schema Dependencies Set Parameter GENTABLES to TRUE
  Oracle XML DB supports XML schemas that involve circular schema dependencies. It does this by detecting the cycles, breaking them, and storing the recursive elements as rows in a separate XMLType table that is created during XML schema registration.
• complexType Declarations in XML Schema: Handling Cycles
  SQL object types do not allow cycles. Cycles in an XML schema are broken while generating the object types, by introducing a REF attribute where the cycle would be completed. Part of the data is stored out of line, but it is retrieved as part of the parent XML document.
• Cyclical References Among XML Schemas
  XML schemas can depend on each other in such a way that they cannot be registered one after the other in the usual manner.

A REF to a recursive structure in an out-of-line table can make it difficult to rewrite XPath queries, because it is not known at compile time how deep the structure is. To enable XPath rewrite, a DOCID column points back to the root document in any recursive structure.

This enables some XPath queries to use the out-of-line tables directly and join back using this column.

A document-correlated recursive query is a query using a SQL function that accepts an XPath or XQuery expression and an XMLType instance, where that XPath or XQuery expression contains '//'. A document-correlated recursive query can be rewritten if it can be determined at query compilation time that both of the following conditions are met:

• All fragments of the XMLType instance that are targeted by the XPath or XQuery expression reside in a single out-of-line table.

• No other fragments of the XMLType instance reside in the same out-of-line table.

The rewritten query is a join with the out-of-line table, based on the DOCID column.

Other queries with '//' can also be rewritten. For example, if there are several address elements, all of the same type, in different sections of a schema, and you often query all address elements with '//', not caring about their specific location in the document, rewrite can occur.

During schema registration, an additional DOCID column is generated for out-of-line XMLType tables This column stores the OID (Object Identifier Values) of the document, that is, the root element. This column is automatically populated when data is inserted in the tables. You can export tables containing DOCID columns and import them later.

<schema targetNamespace="AbcNS" xmlns="http://www.w3.org/2001/XMLSchema"
          xmlns:abc="AbcNS" xmlnm:xdb="http://xmlns.oracle.com.xdb">
  <element name="AbcCode" xdb:defaultTable="ABCCODETAB">
    <complexType>
      <sequence>
        <element ref="abc:AbcSection"/>
      </sequence>
    </complexType>
  </element>
 
  <element name="AbcSection">
    <complexType>
      <sequence>
        <element name="ID" type="integer"/>
        <element name="Contents" type="string"/>
        <element ref="abc:AbcSection"/>
      </sequence>
    </complexType>
  </element>
</schema>

• defaultTable Shared Among Common Out-Of-Line Elements
  Out-of-line elements of the same qualified name (namespace and local name) and same type are stored in the same default table. As a special case, you can store the root element of a cyclic element structure out of line in the same table as the sub-elements.
• Query Rewrite when DOCID is Present
  Before processing // XPath expressions, check to find multiple occurrences of the same element. If all occurrences under the // share the same defaultTable then the query can be rewritten against that table, using the DOCID.
• DOCID Column Creation Disabling
  You can disable the creation of column DOCID by specifying an OPTIONS parameter when calling DBMS_XMLSCHEMA.registerSchema. This disables DOCID creation in all XMLType tables generated during schema registration.

You can specify the SQL data type to use for a complex element as being CLOB or BLOB.

In Figure 18-6, for example, an entire XML fragment is stored in a LOB attribute.

In Example 18-38, the XML schema defines element Addr using the annotation SQLType = "CLOB":

Figure 18-6 Mapping complexType XML Fragments to CLOB Instances

Description of "Figure 18-6 Mapping complexType XML Fragments to CLOB Instances"

DECLARE
  doc VARCHAR2(3000) :=
    '<schema xmlns="http://www.w3.org/2001/XMLSchema"       
             targetNamespace="http://www.oracle.com/emp.xsd"       
             xmlns:emp="http://www.oracle.com/emp.xsd"       
             xmlns:xdb="http://xmlns.oracle.com/xdb">
       <complexType name="Employee" xdb:SQLType="OBJ_T">
         <sequence>
           <element name="Name" type="string"/>
           <element name="Age" type="decimal"/>
           <element name="Addr" xdb:SQLType="CLOB">
             <complexType >
               <sequence>
                 <element name="Street" type="string"/>
                 <element name="City" type="string"/>
               </sequence>
             </complexType>
           </element>
         </sequence>
       </complexType>
     </schema>';
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://www.oracle.com/PO.xsd',
    SCHEMADOC => doc);
END;

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

Errors ORA-01792 and ORA-04031 can be raised when you work with large or complex XML schemas. You can encounter them when you register an XML schema or you create a table that is based on a global element defined by an XML schema.

• ORA-01792: maximum number of columns in a table or view is 1000

• ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")

These errors are raised when you try to create an XMLType table or column based on a global element and the global element is defined as a complexType that contains a very large number of element and attribute definitions.

They are raised only when creating an XMLType table or column that uses object-relational storage. The table or column is persisted using a SQL type, and each object attribute defined by the SQL type counts as one column in the underlying table. If the SQL type contains object attributes that are based on other SQL types, then the attributes defined by those types also count as columns in the underlying table.

If the total number of object attributes in all of the SQL types exceeds the Oracle Database limit of 1000 columns in a table, then the storage table cannot be created. 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.

SELECT count(*) FROM USER_TAB_COLS WHERE TABLE_NAME = '<the table>'

Error ORA-01792 reports that the 1000-column limit has been exceeded. Error ORA-04031 reports that memory is insufficient during the processing of a large number of element and attribute definitions. To resolve this problem of having too many element and attribute definitions, you must reduce the total number of object attributes in the SQL types that are used to create the storage tables.

There are two ways to achieve this reduction:

• Use a top-down technique, with multiple XMLType tables that manage the XML documents. This reduces the number of SQL attributes in the SQL type hierarchy for a given storage table. As long as none of the tables need to manage more than 1000 object attributes, the problem is resolved.

• Use a bottom-up technique, which reduces the number of SQL attributes in the SQL type hierarchy, collapsing some elements and attributes defined by the XML schema so that they are stored as a single CLOB value.

Both techniques rely on annotating the XML schema to define how a particular complexType is stored in the database.

For the top-down technique, annotations SQLInline = "false" and defaultTable force some subelements in the XML document to be stored as rows in a separate XMLType table. Oracle XML DB maintains the relationship between the two tables using a REF of XMLType. Good candidates for this approach are XML schemas that do either of the following:

• Define a choice, where each element within the choice is defined as a complexType

• Define an element based on a complexType that contains a large number of element and attribute definitions

The bottom-up technique involves reducing the total number of attributes in the SQL object types by choosing to store some of the lower-level complexType elements as CLOB values, rather than as objects. This is achieved by annotating the complexType or the usage of the complexType with SQLType = "CLOB".

Which technique you use depends on the application and the type of queries and updates to be performed against the data.

Oracle XML DB configuration file xdbconfig.xml has parameters that control the amount of memory used by the loading operation: xdbcore-loadableunit-size and xdbcore-xobmem-bound.

These let you optimize the loading process, provided the following conditions are met:

• The document is loaded using one of the following:

  • Protocols (FTP, HTTP(S), or DAV)

  • PL/SQL function DBMS_XDB_REPOS.createResource

  • A SQL INSERT statement into an XMLType table (but not an XMLType column)

• The document is XML schema-based and contains large collections (elements with maxOccurs set to a large number).

• Collections in the document are stored as OCTs. This is the default behavior.

In the following situations, the optimizations are sometimes suboptimal:

• When there are triggers on the base table.

• When the base table is partitioned.

• When collections are stored out of line (applies only to SQL INSERT).

The basic idea behind this optimization is that it lets the collections be swapped into or out of the memory in bounded sizes. As an illustration of this idea consider the following example conforming to a purchase-order XML schema:

<PurchaseOrder>
  <LineItem itemID="1">
    ...
  </LineItem>
    .
    .
  <LineItem itemID="10240">
    ...
  </LineItem>
</PurchaseOrder>

The purchase-order document here contains a collection of 10240 LineItem elements. Creating the entire document in memory and then pushing it out to disk can lead to excessive memory usage and in some instances a load failure due to inadequate system memory.

To avoid that, you can create the documents in finite chunks of memory called loadable units.

In the example case, assume that each line item needs 1 KB of memory and that you want to use loadable units of 512 KB each. Each loadable unit then contains 512 line items, and there are approximately 20 such units. If you want the entire memory representation of the document to never exceed 2 MB, then you must ensure that at any time no more than 4 loadable units are maintained in the memory. You can use an LRU mechanism to swap out the loadable units.

By controlling the size of the loadable unit and the bound on the size of the document you can tune the memory usage and performance of the load or retrieval. Typically a larger loadable unit size translates into a smaller number of disk accesses, but it requires more memory. This is controlled by configuration parameter xdbcore-loadableunit-size, whose default value is 16 KB. You can indicate the amount of memory to be given to a document by setting parameter xdbcore-xobmem-bound, which defaults to 1 MB. The values of these parameters are specified in kilobytes. So, the default value of xdbcore-xobmem-bound is 1024 and that of xdbcore-loadableunit-size is 16. These are soft limits that provide some guidance to the system about how to use the memory optimally.

When a document is loaded using FTP, the pattern in which the loadable units (LU) are created and flushed to the disk is as follows:

No LUs
Create LU1[LineItems(LI):1-512]
LU1[LI:1-512], Create LU2[LI:513-1024]
.
.
LU1[LI:1-512],...,Create LU4[LI:1517:2028]    <-   Total memory size = 2M
Swap Out LU1[LI:1-512], LU2[LI:513-1024],...,LU4[LI:1517-2028], Create LU5[LI:2029-2540]
Swap Out LU2[LI:513-1024], LU3, LU4, LU5, Create LU6[LI:2541-2052]
.
.
.
Swap Out LU16, LU17, LU18, LU10, Create LU20[LI:9729-10240]
Flush LU17,LU18,LU19,LU20

• Guidelines for Configuration Parameters xdbcore-loadableunit-size and xdbcore-xobmem-bound
  Use PGA size and trial and error to determine the best values for configuration parameters xdbcore-loadableunit-size and xdbcore-xobmem-bound.