Skip Headers
Oracle® XML DB Developer's Guide
12c Release 1 (12.1)

E17603-09
Go to Documentation Home
Home
Go to Book List
Book List
Go to Table of Contents
Contents
Go to Index
Index
Go to Master Index
Master Index
Go to Feedback page
Contact Us

Go to previous page
Previous
Go to next page
Next
PDF · Mobi · ePub

9 XML Schema Storage and Query: Advanced

This chapter describes advanced techniques for storing structured XML schema-based XMLType objects.

See Also:

This chapter contains these topics:

Adding Unique Constraints to the Parent Element of an Attribute

After creating an XMLType table based on an XML schema, how can you add a unique constraint to the parent element of an attribute? You might, for example, want to create a unique key based on an attribute of an element that repeats itself (a collection). 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.

Example 9-1 shows an XML schema that lets attribute No of element <PhoneNumber> appear more than once. The example shows how you can add a unique constraint to ensure that the same phone number cannot be repeated within a given instance document.

Example 9-1 Adding a Unique Constraint to the Parent Element of an Attribute

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

This returns the expected result:

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

The constraint in this example applies to each collection, and not across all instances. This is achieved by creating a concatenated index with the collection id column. To apply the constraint across all collections of all instance documents, omit the collection id column.

Note:

You can create only a functional constraint as a unique or foreign key constraint on XMLType data stored as binary XML.

Setting Annotation Attribute SQLInline to false for Out-Of-Line Storage

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 9-1 illustrates the mapping of complexType to SQL for out-of-line storage.

Figure 9-1 Mapping complexType to SQL for Out-Of-Line Storage

Description of Figure 9-1 follows
Description of "Figure 9-1 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.

Example 9-2 Setting SQLInline to False for Out-Of-Line Storage

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

In Example 9-2, 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 9-3.

Example 9-3 Generated XMLType Tables and Types

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)
 

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 9-4 illustrates querying table addr_tab directly to obtain the distinct city information for all employees.

Example 9-4 Querying an Out-Of-Line Table

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

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.

XPath Rewrite for Out-Of-Line Tables

XPath expressions that involve elements stored out of line can be rewritten. The rewritten query involves a join with the out-of-line table. Example 9-5 shows such a query.

Example 9-5 XPath Rewrite for an Out-Of-Line Table

SELECT XMLCast(XMLQuery('declare namespace x = "http://www.oracle.com/emp.xsd"; (: :)
                         /x:Employee/Name' PASSING OBJECT_VALUE RETURNING CONTENT)
               AS VARCHAR2(20))
  FROM emp_tab
  WHERE XMLExists('declare namespace x = "http://www.oracle.com/emp.xsd"; (: :)
                   /x:Employee/Addr[City="San Francisco"]' PASSING OBJECT_VALUE);

XMLCAST(XMLQUERY(...
--------------------
Abe Bee
Eve Fong
George Hu
Iris Jones
Karl Luomo
Marina Namur
Omar Pinano
Quincy Roberts
 
8 rows selected.

The XQuery expression here is rewritten to a SQL EXISTS subquery that queries table addr_tab, joining it with table emp_tab using the object identifier column in addr_tab. The optimizer uses full table scans of tables emp_tab and addr_tab. If there are many entries in the addr_tab, then you can try to make this query more efficient by creating an index on the city, as shown in Example 9-6. An explain-plan fragment for the same query as in Example 9-5 shows that the city index is picked up.

Example 9-6 Using an Index with an Out-Of-Line Table

CREATE INDEX addr_city_idx
  ON addr_tab (extractValue(OBJECT_VALUE, '/Addr/City'));

|   2 |   TABLE ACCESS BY INDEX ROWID| ADDR_TAB      |     1 |  2012 |     1   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | ADDR_CITY_IDX |     1 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL          | EMP_TAB       |    16 | 32464 |     2   (0)| 00:00:01 |

Note:

When gathering statistics for the optimizer on an XMLType table that is stored object-relationally, Oracle recommends that you gather statistics on all of the tables defined by the XML schema, that is, all of the tables in USER_XML_TABLES. You can use procedure DBMS_STATS.gather_schema_stats to do this, or use DBMS_STATS.gather_table_stats on each such table. This informs the optimizer about all of the dependent tables that are used to store the XMLType data.

Storing Collections in Out-Of-Line Tables

You can also map collection items to be stored out of line. In this case, instead of a single REF column, the parent element contains a varray of REF values that point to the collection members. For example, suppose that there is a list of addresses for each employee and that list is mapped to out-of-line storage, as shown in Example 9-7.

Example 9-7 Storing a Collection Out of Line

DECLARE
  doc VARCHAR2(3000) :=
    '<schema xmlns="http://www.w3.org/2001/XMLSchema"
             targetNamespace="http://www.oracle.com/emp.xsd"
             xmlns:emp="http://www.oracle.com/emp.xsd"
             xmlns:xdb="http://xmlns.oracle.com/xdb">
       <complexType name="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;
/

During registration of this XML schema, Oracle XML DB generates tables emp_tab and addr_tab and types emp_t and addr_t, just as in Example 9-2. However, this time, type emp_t contains a varray of REF values that point to addresses, instead of a single REF attribute, as shown in Example 9-8.

Example 9-8 Generated Out-Of-Line Collection Type

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

By default, (deprecated) XML schema attribute xdb:storeVarrayAsTableFoot 1  has value true, which means that the varray of REF values is stored out of line, in an intermediate table. That is, in addition to creating the tables and types just mentioned, XML schema registration also creates the intermediate table that stores the list of REF values. This table has a system-generated name, but you can rename it. That can be useful, for example, in order to create an index on it.

Example 9-9 Renaming an Intermediate Table of REF Values

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

Example 9-10 shows a query that selects the names of all San Francisco-based employees and the streets in which they live. The example queries the address table on element City, and joins back with the employee table. The explain-plan fragment shown indicates a join between tables emp_tab_reflist and emp_tab.

Example 9-10 XPath Rewrite for an Out-Of-Line Collection

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 |

To improve performance you can create an index on the REF values in the intermediate table, emp_tab_reflist. This lets Oracle XML DB query the address table, obtain an object reference (REF) to the relevant row, join it with the intermediate table storing the list of REF values, and join that table back with the employee table.

You can create an index on REF values only if the REF is scoped or has a referential constraint. A scoped REF column stores pointers only to objects in a particular table. The REF values in table emp_tab_reflist point only to objects in table addr_tab, so you can create a scope constraint and an index on the REF column, as shown in Example 9-11.

Example 9-11 XPath Rewrite for an Out-Of-Line Collection, with Index on REFs

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

The explain-plan fragment for the same query as in Example 9-10 shows that index reflist_idx is picked up.

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

Partitioning XMLType Tables and Columns Stored Object-Relationally

This section is about XMLType data that is stored object-relationally.

When you partition an XMLType table or a table with an XMLType column using list, range, or hash partitioning, any ordered collection tables (OCTs) or out-of-line tables within the data are automatically partitioned accordingly, by default.

This equipartitioning means that the partitioning of an OCT or an out-of-line table follows the partitioning scheme of its parent (base) table. There is a corresponding child-table partition for each partition of the base table. A child element is stored in the child-table partition that corresponds to the base-table partition of its parent element.

Storage attributes for a base table partition are, by default, also used for the corresponding child-table partitions. You can override these storage attributes for a given child-table partition.

Similarly, by default, the name of an OCT partition is the same as its base (parent) table, but you can override this behavior by specifying the name to use. The name of an out-of-line table partition is always the same as the partition of its parent-table (which could be a base table or an OCT).

Note:

  • Equipartitioning of XMLType data stored object-relationally is not available in releases prior to Oracle Database 11g Release 1 (11.1).

  • Equipartitioning of XMLType data that is stored out of line is not available in releases prior to Oracle Database 11g Release 2 (11.2.0.2). Starting with that release, out-of-line tables are not shared: You cannot create two top-level tables that are based on the same XML schema, if that schema specifies an out-of-line table.

You can prevent partitioning of OCTs by specifying the keyword GLOBAL in a CREATE TABLE statement. (Starting with Oracle Database 11g Release 1 (11.1), the default behavior uses keyword LOCAL). For information about converting a non-partitioned collection table to a partitioned collection table, see Oracle Database VLDB and Partitioning Guide.

You can prevent partitioning of out-of-line tables, and thus allow out-of-line sharing, by turning on event 31178 with level 0x200:

ALTER SESSION SET EVENTS '31178 TRACE NAME CONTEXT FOREVER, LEVEL 0x200'

See Also:

Examples of Partitioning XMLType Data

You can specify partitioning information for an XMLType base table in two ways:

  • During XML schema registration, using XML Schema annotation xdb:tableProps

  • During table creation using CREATE TABLE

Example 9-12 and Example 9-13 illustrate this. These two examples have exactly the same effect. They partition the base purchaseorder table using the Reference element to specify ranges. They equipartition the child table of line items with respect to the base table.

Example 9-12 shows element PurchaseOrder from the purchase-order XML schema, annotated to partition the base table and its child table of line items.

Example 9-12 Specifying Partitioning Information During XML Schema Registration

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

Example 9-13 specifies the same partitioning as in Example 9-12, but it does so during the creation of the base table purchaseorder.

Example 9-13 Specifying Partitioning Information During Table Creation

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

Example 9-12 and Example 9-13 also show how you can specify object storage options for the individual child-table partitions. In this case, the STORAGE clauses specify that extents of size 14M are to be allocated initially for each of the child-table partitions.

Partition Maintenance

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

See Also:

Fully Qualified XML Schema URLs

By default, XML schema URLs are referenced within the scope of the current database user. XML schema URLs are first resolved as the names of local XML schemas owned by the current user.

  • If there are no such XML schemas, then they are resolved as names of global XML schemas.

  • If there are no global XML schemas either, then Oracle XML DB raises an error.

To permit explicit reference to particular XML schemas, Oracle XML DB supports the notion of fully qualified XML schema URLs. The name of the database user owning the XML schema is specified as part of the XML schema URL. Fully qualified XML schema URLs belong to the Oracle XML DB namespace:

http://xmlns.oracle.com/xdb/schemas/<database-user>/<schemaURL-minus-protocol>

For example, suppose there is a registered global XML schema with the URL http://www.example.com/po.xsd, and user QUINE has a local registered XML schema with the same URL. Another user can reference the schema owned by QUINE as follows using this fully qualified XML Schema URL:

http://xmlns.oracle.com/xdb/schemas/QUINE/www.example.com/po.xsd

The fully qualified URL for the global XML schema is:

http://xmlns.oracle.com/xdb/schemas/PUBLIC/www.example.com/po.xsd

Mapping XML Fragments to Large Objects (LOBs)

You can specify the SQL data type to use for a complex element as being CLOB or BLOB. In Figure 9-2, for example, an entire XML fragment is stored in a LOB attribute. This is useful when parts of an XML document are typically retrieved and stored as whole, and are seldom queried. By storing XML fragments as LOBs, you can save on parsing, decomposition, and recomposition overheads.

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

Example 9-14 Oracle XML DB XML Schema: Mapping complexType XML Fragments to LOBs

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;

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

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

Figure 9-2 Mapping complexType XML Fragments to CLOB Instances

Description of Figure 9-2 follows
Description of "Figure 9-2 Mapping complexType XML Fragments to CLOB Instances"

complexType Extensions and Restrictions in Oracle XML DB

In XML Schema, complexType values are declared based on complexContent and simpleContent.

  • simpleContent is declared as an extension of simpleType.

  • complexContent is declared as one of the following:

    • Base type

    • complexType extension

    • complexType restriction

This section describes the Oracle XML DB extensions and restrictions to complexType.

complexType Declarations in XML Schema: Handling Inheritance

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

  • 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 9-15 shows the registration of an XML schema that defines a base complexType Address and two extensions USAddress and IntlAddress.

Example 9-15 XML Schema Inheritance: complexContent as an Extension of complexTypes

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;

Note:

Type intladdr_t is created as a final type because the corresponding complexType specifies the "final" attribute. By default, all complexTypes can be extended and restricted by other types, so all SQL object types are created as types that are not final.
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 9-16 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.

Example 9-16 Inheritance in XML Schema: Restrictions in complexTypes

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;

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 9-16, 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;

Mapping complexType: simpleContent to Object Types

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 corresponding to the body value. The data type of the body attribute is based on simpleType which defines the body type.

Example 9-17 XML Schema complexType: Mapping complexType to simpleContent

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

For the XML schema of Example 9-17, Oracle XML DB generates the following type:

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

Mapping complexType: any and anyAttribute

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 9-18 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.

Example 9-18 XML Schema: Mapping complexType to any/anyAttribute

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;

For the XML schema of Example 9-18, 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));

XML Schema: Working with Circular and Cyclical Dependencies

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

Example 9-19 An XML Schema with Circular Dependency

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

The XML schema in Example 9-19 includes a circular dependency. The complexType personType consists of a personName attribute and a collection of descendant elements. The descendant element is defined as being of type personType.

For Circular XML Schema Dependencies Set Parameter GENTABLES to TRUE

Oracle XML DB supports XML schemas that define this kind of structure. 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.

Consequently, it is important to ensure that parameter GENTABLES is set to TRUE when registering an XML schema that defines this kind of structure. The name of the table used to store the recursive elements can be specified by adding an xdb:defaultTable annotation to the XML schema.

complexType Declarations 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 at the point where the cycle would be completed. Thus, part of the data is stored out of line, but it is still retrieved as part of the parent XML document.

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.

XML schemas permit cycling between definitions of complex types. Figure 9-3 shows this, where the definition of complex type CT1 can reference another complex type CT2, whereas the definition of CT2 references the first type CT1.

XML schemas permit cycles among definitions of complex types. Example 9-20 creates a cycle of length two:

Example 9-20 XML Schema: Cycling Between complexTypes

DECLARE 
  doc VARCHAR2(3000) :=
    '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" 
                xmlns:xdb="http://xmlns.oracle.com/xdb">
       <xs:complexType name="CT1" xdb:SQLType="CT1">
         <xs:sequence>
           <xs:element name="e1" type="xs:string"/>
           <xs:element name="e2" type="CT2"/>
         </xs:sequence>
       </xs:complexType>
       <xs:complexType name="CT2" xdb:SQLType="CT2">
         <xs:sequence>
           <xs:element name="e1" type="xs:string"/>
           <xs:element name="e2" type="CT1"/>
         </xs:sequence>
       </xs:complexType>
     </xs:schema>';
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://www.oracle.com/emp.xsd',
    SCHEMADOC => doc);
END;

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

For the XML schema of Example 9-20, Oracle XML DB generates the following types:

CREATE TYPE ct1 AS OBJECT (SYS_XDBPD$  XDB.XDB$RAW_LIST_T,
                           "e1"        VARCHAR2(4000),
                           "e2"        REF XMLType) NOT FINAL;
CREATE TYPE ct2 AS OBJECT (SYS_XDBPD$  XDB.XDB$RAW_LIST_T,
                           "e1"        VARCHAR2(4000),
                           "e2"        CT1) NOT FINAL;

Figure 9-3 Cross Referencing Between Different complexTypes in the Same XML Schema

Description of Figure 9-3 follows
Description of "Figure 9-3 Cross Referencing Between Different complexTypes in the Same XML Schema"

Another example of a cyclic complex type involves the declaration of the complex type that refers to itself. In Example 9-21, type SectionT does this.

Example 9-21 XML Schema: Cycling Between complexTypes, Self-Reference

DECLARE 
  doc VARCHAR2(3000) :=
    '<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"        
                xmlns:xdb="http://xmlns.oracle.com/xdb">
       <xs:complexType name="SectionT" xdb:SQLType="SECTION_T">
         <xs:sequence>
           <xs:element name="title" type="xs:string"/>
           <xs:choice maxOccurs="unbounded">
             <xs:element name="body" type="xs:string" 
                         xdb:SQLCollType="BODY_COLL"/>
             <xs:element name="section" type="SectionT"/>
           </xs:choice>
         </xs:sequence>
       </xs:complexType>
     </xs:schema>';
BEGIN
  DBMS_XMLSCHEMA.registerSchema(
    SCHEMAURL => 'http://www.oracle.com/section.xsd',
    SCHEMADOC => doc);
END;

For the XML schema of Example 9-21, Oracle XML DB generates the following types:

CREATE TYPE body_coll AS VARRAY(32767Foot 2 ) OF VARCHAR2(32767Footref 2);
CREATE TYPE section_t AS OBJECT (SYS_XDBPD$  XDB.XDB$RAW_LIST_T, 
                                 "title"     VARCHAR2(32767Footref 2),
                                 "body"      BODY_COLL,
                                 "section"   XDB.XDB$REF_LIST_T) NOT FINAL;

Note:

In Example 9-21, object attribute section is declared as a varray of REF references to XMLType instances. Because there can be more than one occurrence of embedded sections, the attribute is a varray. It is a varray of REF references to XMLType instances, to avoid forming a cycle of SQL objects.

How a complexType Can Reference Itself

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

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

Figure 9-4 illustrates schematically how a complexType can reference itself.

Figure 9-4 Self-Referencing Complex Type within an XML Schema

Description of Figure 9-4 follows
Description of "Figure 9-4 Self-Referencing Complex Type within an XML Schema"

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

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. Illustrations of such XML schemas follow in Figure 9-5.

In the top half of the illustration, an example of indirect cyclical references between three XML schemas is shown.

In the bottom half of the illustration, an example of cyclical dependencies between two XML schemas is shown. The details of this simpler example are presented first.

Figure 9-5 Cyclical References Between XML Schemas

Description of Figure 9-5 follows
Description of "Figure 9-5 Cyclical References Between XML Schemas"

An XML schema that includes another XML schema cannot be created if the included XML schema does not exist. The registration of XML schema xm40.xsd in Example 9-22 fails, if xm40a.xsd does not exist.

Example 9-22 An XML Schema that Includes a Non-Existent XML Schema

BEGIN DBMS_XMLSCHEMA.registerSchema(
  SCHEMAURL => 'xm40.xsd',
  SCHEMADOC => '<schema xmlns="http://www.w3.org/2001/XMLSchema"
                        xmlns:my="xm40"  
                        targetNamespace="xm40">
                  <include schemaLocation="xm40a.xsd"/>
                  <!-- Define a global complextype here -->
                  <complexType name="Company">
                    <sequence>
                      <element name="Name" type="string"/>
                      <element name="Address" type="string"/>
                    </sequence>
                  </complexType>
                  <!-- Define a global element depending on included schema -->
                  <element name="Emp" type="my:Employee"/>
                </schema>',
  LOCAL     => TRUE, 
  GENTYPES  => TRUE, 
  GENTABLES => TRUE); 
END;
/

XML schema xm40.xsd can, however, be created if you specify option FORCE => TRUE, as in Example 9-23:

Example 9-23 Using the FORCE Option to Register XML Schema xm40.xsd

BEGIN DBMS_XMLSCHEMA.registerSchema(
  SCHEMAURL => 'xm40.xsd',
  SCHEMADOC => '<schema xmlns="http://www.w3.org/2001/XMLSchema"
                        xmlns:my="xm40"  
                        targetNamespace="xm40">
                  <include schemaLocation="xm40a.xsd"/>
                  <!-- Define a global complextype here -->
                  <complexType name="Company">
                    <sequence>
                      <element name="Name" type="string"/>
                      <element name="Address" type="string"/>
                    </sequence>
                  </complexType>
                  <!-- Define a global element depending on included schema -->
                  <element name="Emp" type="my:Employee"/>
                </schema>',
  LOCAL     => TRUE, 
  GENTYPES  => TRUE, 
  GENTABLES => TRUE, 
  FORCE     => TRUE); 
END;
/

However, an attempt to use XML schema xm40.xsd, as in Example 9-24, fails.

Example 9-24 Trying to Create a Table Using a Cyclic XML Schema

CREATE TABLE foo OF XMLType XMLSCHEMA "xm40.xsd" ELEMENT "Emp";

If you register xm40a.xsd using the FORCE option, as in Example 9-25, then both XML schemas can be used, as shown by the CREATE TABLE statements.

Example 9-25 Using the FORCE Option to Register XML Schema xm40a.xsd

BEGIN DBMS_XMLSCHEMA.registerSchema(
  SCHEMAURL => 'xm40a.xsd',
  SCHEMADOC => '<schema xmlns="http://www.w3.org/2001/XMLSchema"
                        xmlns:my="xm40" 
                        targetNamespace="xm40">
                  <include schemaLocation="xm40.xsd"/>
                  <!-- Define a global complextype here -->
                  <complexType name="Employee">
                    <sequence>
                      <element name="Name" type="string"/>
                      <element name="Age" type="positiveInteger"/>
                      <element name="Phone" type="string"/>
                    </sequence>
                  </complexType>
                  <!-- Define a global element depending on included schema -->
                  <element name="Comp" type="my:Company"/>
                </schema>',
  LOCAL     => TRUE, 
  GENTYPES  => TRUE, 
  GENTABLES => TRUE, 
  FORCE     => TRUE); 
END;
/
CREATE TABLE foo  OF XMLType XMLSCHEMA "xm40.xsd"  ELEMENT "Emp";
CREATE TABLE foo2 OF XMLType XMLSCHEMA "xm40a.xsd" ELEMENT "Comp";
 

Thus, to register these XML schemas, which depend on each other, you must use the FORCE parameter in DBMS_XMLSCHEMA.registerSchema for each schema, as follows:

  1. Register xm40.xsd with FORCE mode set to TRUE:

    DBMS_XMLSCHEMA.registerSchema("xm40.xsd", "<schema ...", ..., FORCE => TRUE)
    

    At this point, xm40.xsd cannot be used.

  2. Register xm40a.xsd in FORCE mode set to TRUE:

    DBMS_XMLSCHEMA.registerSchema("xm40a.xsd", "<schema ...", ..., FORCE => TRUE)
    

    The second operation automatically compiles xm40.xsd and makes both XML schemas usable.

Support for Recursive Schemas

Storing a REF to a recursive structure that is in an out-of-line table has the disadvantage that XPath queries against such documents cannot easily be rewritten, because it is not known at compile time how deep the structure might be. To enable rewrite of such XPath queries, a DOCID column is used to store a pointer back to the root document in any recursive structure, enabling some queries to use the out-of-line tables directly and join back using this column.

Example 9-26 shows a recursive XML schema.

Example 9-26 Recursive XML Schema

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

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.

Sharing defaultTable Among Common Out-Of-Line Elements

The 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 also, and in the same table as the sub-elements (if the root element is stored out of line also).

Both of the elements sharing the default table must be out-of-line elements, that is, the default table for an out-of-line element cannot be the same as the table for a top-level element. To do this, specify xdb:SQLInline = "false" for both elements and specify an explicit xdb:defaultTable attribute having the same value in both elements.

Example 9-27 shows an XML schema with an out-of-line table that is stored in ABCSECTIONTAB.

Example 9-27 Out-of-line Table

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

Both of the out-of-line AbcSection elements in Example 9-27 share the same default table, ABCSECTIONTAB.

However, the Example 9-28 illustrates invalid default table sharing: recursive elements (XyZSection) do not share the same out-of-line table.

Example 9-28 Invalid Default Table Sharing

 <schema targetNamespace="XyzNS" xmlns="http://www.w3.org/2001/XMLSchema"
         xmlns:xyz="XyzNS" xmlns:xdb="http://xmlns.oracle.com/xdb">
   <element name="XyzCode" xdb:defaultTable="XYZCODETAB">
   <complexType>
   <sequence>
      <element name="CodeNumber" type="integer" minOccurs="0"/>
      <element ref="xyz:XyzChapter" xdb:SQLInline="false"/>
      <element ref="xyz:XyzPara" xdb:SQLInline="false" />
   </sequence>
   </complexType>
   </element>
 
    <element name="XyzChapter" xdb:defaultTable="XYZCHAPTAB">
     <complexType>
     <sequence>
         <element name="Title" type="string"/>
         <element ref="xyz:XyzSection" xdb:SQLInline="false" 
                  xdb:defaultTable="XYZSECTIONTAB"/>
      </sequence>
      </complexType>
    </element>
 
    <element name="XyzPara" xdb:defaultTable="XYZPARATAB">
     <complexType>
     <sequence>
         <element name="Title" type="string"/>
         <element ref="xyz:XyzSection" xdb:SQLInline="false" 
                  xdb:defaultTable="Other_XYZSECTIONTAB"/>
      </sequence>
      </complexType>
    </element>
   
    <element name="XyzSection">
    <complexType>
    <sequence>
        <element name="ID" type="integer"/>
        <element name="Contents" type="string"/>
        <element ref="xyz:XyzSection" xdb:defaultTable="XYZSECTIONTAB"/>
     </sequence>
     </complexType>
    </element>
 </schema>

The following query cannot be rewritten.

SELECT XMLQuery('//XyzSection' PASSING OBJECT_VALUE RETURNING CONTENT)
  FROM xyzcode;

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 to go against that table, using the DOCID. If there are other occurrences of the same element under the root sharing that table, but not under //, then the query cannot be rewritten. For example, consider this element structure:

<Book> contains a <Chapter> and a <Part>. <Part> contains a <Chapter>.

Assume that both of the <Chapter> elements are stored out of line and they share the same default table. The query /Book//Chapter can be rewritten to go against the default table for the <Chapter> elements because all of the <Chapter> elements under <Book> share the same default table. Thus, this XPath query is a document-correlated recursive XPath query.

However, a query such as /Book/Part//Chapter cannot be rewritten, even though all the <Chapter> elements under <Part> share the same table, because there is another <Chapter> element under <Book>, which is the document root that also shares that table.

Consider the case where you are extracting //AbcSection with DOCID present, as in the XML schema described in Example 9-27:

SELECT XMLQuery('//AbcSection' PASSING OBJECT_VALUE RETURNING CONTENT)
  FROM abccodetab;

Both of the AbcSection elements are stored in the same table, abcsectiontab. The extraction applies to the underlying table, abcsectiontab.

Consider this query when DOCID is present:

SELECT XMLQuery('/AbcCode/AbcSection//AbcSection'
                PASSING OBJECT_VALUE RETURNING CONTENT)
  FROM abccodetab;

In both this case and the previous case, all reachable AbcSection elements are stored in the same out-of-line table. However, the first AbcSection element at /AbcCode/AbcSection cannot be retrieved by this query. Since the join condition is a DOCID, which cannot distinguish between different positions in the parent document, the correct result cannot be achieved by a direct query on table abcsectiontab. In this case, query rewrite does not occur since it is not a document-correlated recursive XPath. If this top-level AbcSection were not stored out of line with the rest, then the query could be rewritten.

Disabling DOCID Column Creation

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.

The OPTIONS parameter is an input parameter of data type PLS_INTEGER. Its default value is 0, meaning no options are used. To inhibit generation of column DOCID, set parameter OPTIONS to DBMS_XMLSCHEMA.REGISTER_NODOCID (which is 1).

Loading and Retrieving Large Documents with Collections

Configuration file /xdbconfig.xml has parameters that control the amount of memory used by the loading operation. 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, these 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 lesser number of disk accesses but takes up 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 as to 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 Setting xdbcore Parameters

Typically, if you have 1 GB of addressable PG then give about 1/10th of PGA to the document. Set xobcore-xobmem-bound to 1/10 of addressable PGA, which is 100M. During full document retrievals and loads, xdbcore-loadableunit-size should be as close to xobcore-xobmem-bound as possible.

Start by setting xdbcore-loadableunit-size to half the value of xdbcore-xobmem-bound (50 MB). Then try to load the document.

If you run out of memory then reduce the value of xdbcore-xobmem-bound and set xdbcore-loadableunot-size to half of that value. Continue this way until the documents load successfully.

If the load operation succeeds then try to increase xdbcore-loadableunit-size, to obtain better performance. If xdbcore-loadableunit-size equals xdbcore-xobmem-bound, then try to increase both parameter values for further performance improvements.



Footnote Legend

Footnote 1: XML Schema annotation xdb:storeVarrayAsTable is deprecated, starting with Oracle Database 12c Release 1 (12.1.0.1).
Footnote 2: This value of 32767 assumes that the value of initialization parameter MAX_STRING_SIZE is EXTENDED. See Oracle Database SQL Language Reference.