| Oracle® XML DB Developer's Guide 11g Release 1 (11.1) Part Number B28369-01 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 11g Release 1 (11.1) Part Number B28369-01 |
|
|
View PDF |
This chapter describes advanced techniques for storing structured XML schema-based XMLType objects.
See Also:
Chapter 6, "XML Schema Storage and Query: Basic" for basic information about using XML Schema with Oracle XML DB
Chapter 7, "XPath Rewrite" for information about the optimization of XPath expressions in Oracle XML DB
Chapter 9, "XML Schema Evolution" for information about updating an XML schema after you have registered it with Oracle XML DB
http://www.w3.org/TR/xmlschema-0/ for an introduction to XML Schema
This chapter contains these topics:
Adding Unique Constraints to the Parent Element of an Attribute
Setting Attribute SQLInline to false for Out-Of-Line Storage
Oracle XPath Extension Functions to Examine Type Information
An XML schema can be generated from an object-relational type automatically using a default mapping. PL/SQL functions generateSchema and generateSchemas in package DBMS_XMLSCHEMA take in a string that has the object type name and another that has the Oracle XML DB XML schema.
Function generateSchema returns an XMLType containing an XML schema. It can optionally generate an XML schema for all types referenced by the given object type or restricted only to the top-level types.
Function generateSchemas is similar, except that it returns an XMLSequenceType value. This is a varray of XMLType instances, each of which is an XML schema that corresponds to a different namespace. It also takes an additional optional argument that specifies the root URL of the preferred XML schema location:
http://xmlns.oracle.com/xdb/schemas/<schema>.xsd
They can also optionally generate annotated XML schemas that can be used to register the XML schema with Oracle XML DB.
Example 8-1 Generating an XML Schema with Function GENERATESCHEMA
For example, given the object type:
CREATE TYPE employee_t AS OBJECT(empno NUMBER(10),
ename VARCHAR2(200),
salary NUMBER(10,2)):
You can generate the schema for this type as follows:
SELECT DBMS_XMLSCHEMA.generateschema('T1', 'EMPLOYEE_T') FROM DUAL;
This returns a schema corresponding to the type employee_t. The schema declares an element named EMPLOYEE_T and a complexType called EMPLOYEE_TType. The schema includes other annotations from http://xmlns.oracle.com/xdb.
DBMS_XMLSCHEMA.GENERATESCHEMA('T1', 'EMPLOYEE_T')
------------------------------------------------------------------------
<xsd:schema targetNamespace="http://ns.oracle.com/xdb/T1"
xmlns="http://ns.oracle.com/xdb/T1"
xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/xdb
http://xmlns.oracle.com/xdb/XDBSchema.xsd">
<xsd:element name="EMPLOYEE_T" type="EMPLOYEE_TType"
xdb:SQLType="EMPLOYEE_T" xdb:SQLSchema="T1"/>
<xsd:complexType name="EMPLOYEE_TType">
<xsd:sequence>
<xsd:element name="EMPNO" type="xsd:double" xdb:SQLName="empno"
xdb:SQLType="NUMBER"/>
<xsd:element name="ENAME" type="xsd:string" xdb:SQLName="ename"
xdb:SQLType="VARCHAR2"/>
<xsd:element name="SALARY" type="xsd:double" xdb:SQLName="salary"
xdb:SQLType="NUMBER"/>
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
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 8-2 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 8-2 Adding a Unique Constraint to the Parent Element of an Attribute
BEGIN DBMS_XMLSCHEMA.registerschema('emp.xsd',
'<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>',
TRUE,
TRUE,
FALSE,
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 onXMLType data stored as binary XML.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 SQLInline to false, and Oracle XML DB will generate 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 8-1 illustrates the mapping of complexType to SQL for out-of-line storage.
Figure 8-1 Mapping complexType to SQL for Out-Of-Line Storage
Example 8-3 Setting SQLInline to False for Out-Of-Line Storage
In this example, attribute xdb:SQLInline of element Addr is set to false. The resulting SQL object type, obj_t2, has a column of type XMLType with an embedded REF attribute. The REF attribute points to another 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.
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;
/
When registering this XML schema, Oracle XML DB generates the following 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.
The advantage of this model is that it lets you query the out-of-line table (addr_tab) directly, to look up address information. Example 8-4 illustrates querying table addr_tab directly to obtain the distinct city information for all employees.
Example 8-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 extractValue(OBJECT_VALUE, '/Addr/City') AS city 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 expressions that involve elements stored out of line can be rewritten. The query involves a join with the out-of-line table. Example 8-5 shows such a query. A fragment of the explain plan for this query is shown, for comparison with Example 8-6.
Example 8-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.
|* 3 | INDEX RANGE SCAN | ADDR_CITY_IDX | 1 | | 1 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| ADDR_TAB | 1 | 2012 | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP_TAB | 16 | 32464 | 2 (0)| 00:00:01 |
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 8-6. The corresponding explain-plan fragment for the same query as in Example 8-5 shows that the city index is picked up.
Example 8-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 anXMLType 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.See Also:
Chapter 7, "XPath Rewrite"You can also map list 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.
Example 8-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;
/
When you register this XML schema, Oracle XML DB generates tables emp_tab and addr_tab and types emp_t and addr_t, as in Example 8-3. However, this time, type emp_t contains a varray of REF values to addresses, instead of a single REF attribute.
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, XML schema attribute storeVarrayAsTable has value true, which means that this varray of REF values is stored out of line, in an intermediate table. This means that, 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, in order to, for example, create an index on it.
Example 8-8 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 8-9 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 8-9 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 |
We can improve performance by creating 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 we can create a scope constraint and an index on the REF column, as shown in Example 8-10.
Example 8-10 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 8-9 shows that index reflist_idx is picked up — compare with Example 8-9.
| 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 |
In some cases, where the more selective predicates in the query are on the employee table, you might want to set XML schema attribute storeVarrayAsTable to false, in order to store the varray of REF values in line in table emp_tab. Storing the varray in line effectively forces any query involving the two tables emp_tab and addr_tab to always be driven from emp_tab. There is then no way to efficiently join back from the address table. This means that this approach is not appropriate when the number of employees is large, because it involves a full table scan of table emp_tab, which can be expensive.
By default, XML schema URL names are always referenced within the scope of the current user. In other words, when database users specify XML schema URLs, they are first resolved as the names of local XML schemas owned by the current user.
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 XML schemas in these cases, Oracle XML DB supports the notion of fully qualified XML schema URLs. In this form, the name of the database user owning the XML schema is also specified as part of the XML schema URL, except that such XML schema URLs belong to the Oracle XML DB namespace:
http://xmlns.oracle.com/xdb/schemas/<database-user>/<schemaURL-minus-protocol>
Example 8-11 Using a Fully Qualified XML Schema URL
For example, consider the global XML schema with the following URL:
http://www.example.com/po.xsd
Assume that database user QUINE has a local XML schema with the same URL:
http://www.example.com/po.xsd
Another user can reference the local XML schema owned by QUINE as follows:
http://xmlns.oracle.com/xdb/schemas/QUINE/www.example.com/po.xsd
Similarly, the fully qualified URL for the global XML schema is:
http://xmlns.oracle.com/xdb/schemas/PUBLIC/www.example.com/po.xsd
You can specify the SQLType for a complex element as a Character Large Object (CLOB) value or a Binary Large Object (BLOB) value, as shown in Figure 8-2. Here the entire XML fragment is stored in a LOB attribute. This is useful when parts of the XML document are seldom queried but are mostly retrieved and stored as single pieces. By storing XML fragments as LOBs, you can save on parsing, decomposition, and recomposition overheads.
Example 8-12 Oracle XML DB XML Schema: Mapping complexType XML Fragments to LOBs
In the following example, the XML schema specifies that the XML fragment element Addr uses the attribute SQLType = "CLOB":
DECLARE
doc VARCHAR2(3000) :=
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.oracle.com/emp.xsd"
xmlns:emp="http://www.oracle.com/emp.xsd"
xmlns:xdb="http://xmlns.oracle.com/xdb">
<complexType name="Employee" xdb:SQLType="OBJ_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('http://www.oracle.com/PO.xsd', doc);
END;
On registering this XML schema, Oracle XML DB generates the following types and XMLType tables:
CREATE TYPE obj_t AS OBJECT(SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
Name VARCHAR2(4000),
Age NUMBER,
Addr CLOB);
Figure 8-2 Mapping complexType XML Fragments to Character Large Objects (CLOB)
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.
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 8-13 Inheritance in XML Schema: complexContent as an Extension of complexTypes
Consider an XML schema that defines a base complexType Address and two extensions USAddress and IntlAddress.
DECLARE
doc VARCHAR2(3000) :=
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb">
<xs:complexType name="Address" xdb:SQLType="ADDR_T">
<xs:sequence>
<xs:element name="street" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="USAddress" xdb:SQLType="USADDR_T">
<xs:complexContent>
<xs:extension base="Address">
<xs:sequence>
<xs:element name="zip" type="xs:string"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
<xs:complexType name="IntlAddress" final="#all" xdb:SQLType="INTLADDR_T">
<xs:complexContent>
<xs:extension base="Address">
<xs:sequence>
<xs:element name="country" type="xs:string"/>
</xs:sequence>
</xs:extension>
</xs:complexContent>
</xs:complexType>
</xs:schema>';
BEGIN
DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/PO.xsd', doc);
END;
Note:
Typeintladdr_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 8-14 Inheritance in XML Schema: Restrictions in complexTypes
Consider an XML schema that defines a base complexType Address and a restricted type LocalAddress that prohibits the specification of country attribute.
DECLARE
doc varchar2(3000) :=
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb">
<xs:complexType name="Address" xdb:SQLType="ADDR_T">
<xs:sequence>
<xs:element name="street" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="zip" type="xs:string"/>
<xs:element name="country" type="xs:string" minOccurs="0"
maxOccurs="1"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LocalAddress" xdb:SQLType="USADDR_T">
<xs:complexContent>
<xs:restriction base="Address">
<xs:sequence>
<xs:element name="street" type="xs:string"/>
<xs:element name="city" type="xs:string"/>
<xs:element name="zip" type="xs:string"/>
<xs:element name="country" type="xs:string"
minOccurs="0" maxOccurs="0"/>
</xs:sequence>
</xs:restriction>
</xs:complexContent>
</xs:complexType>
</xs:schema>';
BEGIN
DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/PO.xsd', doc);
END;
Because inheritance support in SQL does not support a notion of restriction, the SQL type corresponding to the restricted complexType is a empty subtype of the parent object type. For the preceding XML schema, the following SQL types are generated:
CREATE TYPE addr_t AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
"street" VARCHAR2(4000),
"city" VARCHAR2(4000),
"zip" VARCHAR2(4000),
"country" VARCHAR2(4000)) NOT FINAL;
CREATE TYPE usaddr_t UNDER addr_t;
A complexType based on a simpleContent declaration is mapped to an object type with attributes corresponding to the XML attributes and an extra SYS_XDBBODY$ attribute corresponding to the body value. The data type of the body attribute is based on simpleType which defines the body type.
Example 8-15 XML Schema complexType: Mapping complexType to simpleContent
DECLARE
doc VARCHAR2(3000) :=
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.oracle.com/emp.xsd"
xmlns:emp="http://www.oracle.com/emp.xsd"
xmlns:xdb="http://xmlns.oracle.com/xdb">
<complexType name="name" xdb:SQLType="OBJ_T">
<simpleContent>
<restriction base="string">
</restriction>
</simpleContent>
</complexType>
</schema>';
BEGIN
DBMS_XMLSCHEMA.registerschema('http://www.oracle.com/emp.xsd', doc);
END;
On registering this XML schema, Oracle XML DB generates the following types and XMLType tables:
CREATE TYPE obj_t AS OBJECT(SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
SYS_XDBBODY$ VARCHAR2(4000));
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.
Example 8-16 Oracle XML DB XML Schema: Mapping complexType to any/anyAttribute
This XML schema example declares an any element and maps it to the column SYS_XDBANY$, in object type obj_t. This element also declares that the attribute processContents skips validating contents that match the any declaration.
DECLARE
doc VARCHAR2(3000) :=
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
targetNamespace="http://www.oracle.com/any.xsd"
xmlns:emp="http://www.oracle.com/any.xsd"
xmlns:xdb="http://xmlns.oracle.com/xdb">
<complexType name="Employee" xdb:SQLType="OBJ_T">
<sequence>
<element name="Name" type="string"/>
<element name="Age" type="decimal"/>
<any namespace="http://www/w3.org/2001/xhtml"
processContents="skip"/>
</sequence>
</complexType>
</schema>';
BEGIN
DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/emp.xsd', doc);
END;
This results in the following statement:
CREATE TYPE obj_t AS OBJECT(SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
Name VARCHAR2(4000),
Age NUMBER,
SYS_XDBANY$ VARCHAR2(4000));
Oracle XML DB supports XML schema-based data, where elements and attributes have XML Schema data-type information associated with them. However, XPath 1.0 is not aware of data-type information. Oracle XML DB extends XPath 1.0 with the following Oracle extension functions to support examining data-type information:
instanceof
instanceof-only
These XPath functions are in namespace http://xmlns.oracle.com/xdb, which has the predefined prefix ora.
An element is an instance of a specified XML Schema data type if its type is the same as the specified type or is a subtype of the specified type. A subtype of type T in the context of XML Schema is a type that extends or restricts T, or extends or restricts another subtype of T.
For XPath expressions involving XML schema-based data, you can use Oracle XPath function ora:instanceof-only to restrict the result set to nodes of a certain data type, and ora:instanceof to restrict the result set to nodes of a certain data type or its subtypes. For non-schema-based XML data, elements and attributes do not have data-type information, so these functions return false for non-schema-based data.
Syntax
ora:instanceof-only(nodeset-expr, typename [, schema-url])
On XML schema-based data, ora:instanceof-only evaluates XPath expression nodeset-expr and determines the XML Schema data type for each of the resultant nodes. Expression nodeset-expr is typically a relative XPath expression. If the data type of any of the nodes exactly matches data type typename (a string), which can be qualified with a namespace prefix, then instanceof-only returns true; otherwise, it returns false. It returns false for non-schema-based data.
Optional parameter schema-url (a string) indicates the schema location URL for the data type to be matched. If specified, then the schema-url parameter must specify the location of the XML schema that defines the node data type. If schema-url is not specified, the schema location of the node is not checked.
Example 8-17 Using ora:instanceof-only
The following query selects the Name attributes of AE children of element Person that are of data type PersonType (subtypes of PersonType are not matched).
SELECT extract(OBJECT_VALUE,
'/p9:Person[ora:instanceof-only(AE,"p9:PersonType")]/AE/Name',
'xmlns:p9="person9.xsd" xmlns:ora="http://xmlns.oracle.com/xdb"')
FROM po_table;
Syntax
ora:instanceof(nodeset-expr, typename [, schema-url])
Oracle XPath function ora:instanceof is similar to ora:instanceof-only, but it also returns true if the data type of any of the matching nodes exactly matches a subtype of data type typename.
Example 8-18 Using ora:instanceof
The following query selects the Name attributes of AE children of element Person that are of data type PersonType or of one of its subtypes.
SELECT extract(OBJECT_VALUE,
'/p9:Person[ora:instanceof(AE, "p9:PersonType")]/AE/Name',
'xmlns:p9="person9.xsd" xmlns:ora="http://xmlns.oracle.com/xdb"')
FROM po_table;
The schema-location parameter is typically used in a heterogeneous XML Schema scenario. Heterogeneous XML schema-based data can be present in a single table. If your scenario involves a schema-based table, consider omitting the schema location parameter.
Consider a non-schema-based table of XMLType. Each row in the table is an XML document. Suppose that each row contains data for which XML schema information has been specified. If the data in the table is converted to XML schema-based data through a subsequent operation, then the rows in the table could pertain to different XML schemas. In such a case, you can specify not only the name and the namespace of the data type to be matched, but also the schema-location URL.
Example 8-19 Using ora:instanceof with Heterogeneous XML Schema-Based Data
In the non-schema-based table non_sch_p_tab, the following query matches elements of type PersonType that pertain to XML schema person9.xsd.
SELECT extract(
createSchemaBased(
OBJECT_VALUE),
'/p9:Person/AE[ora:instanceof(.,"p9:PersonType", "person9.xsd")]',
'xmlns:p9="person9.xsd" xmlns:ora="http://xmlns.oracle.com/xdb"')
FROM non_sch_p_tab;
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 8-20 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 8-20 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.
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.
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.
Example 8-21 XML Schema: Cycling Between complexTypes
XML schemas permit cycling between definitions of complexTypes. Figure 8-3 shows this example, where the definition of complexType CT1 can reference another complexType CT2, whereas the definition of CT2 references the first type CT1.
XML schemas permit cycling between definitions of complexTypes. This is an example of cycle of length two:
DECLARE
doc VARCHAR2(3000) :=
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb">
<xs:complexType name="CT1" xdb:SQLType="CT1">
<xs:sequence>
<xs:element name="e1" type="xs:string"/>
<xs:element name="e2" type="CT2"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="CT2" xdb:SQLType="CT2">
<xs:sequence>
<xs:element name="e1" type="xs:string"/>
<xs:element name="e2" type="CT1"/>
</xs:sequence>
</xs:complexType>
</xs:schema>';
BEGIN
DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/emp.xsd', doc);
END;
SQL types do not allow cycles in type definitions. However, they do support weak cycles, that is, cycles involving REF (reference) 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 preceding XML schema, the following SQL types are generated:
CREATE TYPE ct1 AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
"e1" VARCHAR2(4000),
"e2" REF XMLType) NOT FINAL;
CREATE TYPE ct2 AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
"e1" VARCHAR2(4000),
"e2" CT1) NOT FINAL;
Figure 8-3 Cross Referencing Between Different complexTypes in the Same XML Schema
Example 8-22 XML Schema: Cycling Between complexTypes, Self-Reference
Another example of a cyclic complexType involves the declaration of the complexType having a reference to itself. In this example, type <SectionT> references itself:
DECLARE
doc VARCHAR2(3000) :=
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb">
<xs:complexType name="SectionT" xdb:SQLType="SECTION_T">
<xs:sequence>
<xs:element name="title" type="xs:string"/>
<xs:choice maxOccurs="unbounded">
<xs:element name="body" type="xs:string"
xdb:SQLCollType="BODY_COLL"/>
<xs:element name="section" type="SectionT"/>
</xs:choice>
</xs:sequence>
</xs:complexType>
</xs:schema>';
BEGIN
DBMS_XMLSCHEMA.registerSchema('http://www.oracle.com/section.xsd', doc);
END;
The following SQL types are generated.
CREATE TYPE body_coll AS VARRAY(32767) OF VARCHAR2(4000);
CREATE TYPE section_t AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
"title" VARCHAR2(4000),
"body" BODY_COLL,
"section" XDB.XDB$REF_LIST_T) NOT FINAL;
Note:
Thesection attribute 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.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 8-4 illustrates schematically how a complexType can reference itself.
Figure 8-4 complexType Self Referencing 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.
Note:
XMLDATA is a pseudo-attribute of XMLType that enables direct access to the underlying object column. See Chapter 4, "XMLType Operations".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 8-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. This simpler example is next presented with details.
Figure 8-5 Cyclical References Between XML Schemas
Example 8-23 Cyclic Dependencies
An XML schema that includes another XML schema cannot be created if the included XML schema does not exist.
BEGIN DBMS_XMLSCHEMA.registerSchema(
'xm40.xsd',
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:my="xm40"
targetNamespace="xm40">
<include schemaLocation="xm40a.xsd"/>
<!-- Define a global complextype here -->
<complexType name="Company">
<sequence>
<element name="Name" type="string"/>
<element name="Address" type="string"/>
</sequence>
</complexType>
<!-- Define a global element depending on included schema -->
<element name="Emp" type="my:Employee"/>
</schema>',
TRUE,
TRUE,
FALSE,
TRUE);
END;
/
It can, however, be created with the FORCE => TRUE option (the last argument):
BEGIN DBMS_XMLSCHEMA.registerSchema(
'xm40.xsd',
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:my="xm40"
targetNamespace="xm40">
<include schemaLocation="xm40a.xsd"/>
<!-- Define a global complextype here -->
<complexType name="Company">
<sequence>
<element name="Name" type="string"/>
<element name="Address" type="string"/>
</sequence>
</complexType>
<!-- Define a global element depending on included schema -->
<element name="Emp" type="my:Employee"/>
</schema>',
TRUE,
TRUE,
FALSE,
TRUE,
TRUE);
END;
/
Attempts to use this schema and recompile will fail:
CREATE TABLE foo OF XMLType XMLSCHEMA "xm40.xsd" ELEMENT "Emp";
Now, create the second XML schema with the FORCE option. This should also make the first XML schema valid:
BEGIN DBMS_XMLSCHEMA.registerSchema(
'xm40a.xsd',
'<schema xmlns="http://www.w3.org/2001/XMLSchema"
xmlns:my="xm40"
targetNamespace="xm40">
<include schemaLocation="xm40.xsd"/>
<!-- Define a global complextype here -->
<complexType name="Employee">
<sequence>
<element name="Name" type="string"/>
<element name="Age" type="positiveInteger"/>
<element name="Phone" type="string"/>
</sequence>
</complexType>
<!-- Define a global element depending on included schema -->
<element name="Comp" type="my:Company"/>
</schema>',
TRUE,
TRUE,
FALSE,
TRUE,
TRUE);
END;
/
The XML schemas can each be used to create a table:
CREATE TABLE foo OF XMLType XMLSCHEMA "xm40.xsd" ELEMENT "Emp"; CREATE TABLE foo2 OF XMLType XMLSCHEMA "xm40a.xsd" ELEMENT "Comp";
To register both of these XML schemas, which depend on each other, you must use the FORCE parameter in DBMS_XMLSCHEMA.registerSchema as follows:
Register xm40.xsd with FORCE mode set to TRUE:
DBMS_XMLSCHEMA.registerSchema("xm40.xsd", "<schema ...", ..., FORCE => TRUE)
At this point, xm40.xsd is invalid and cannot be used.
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 valid.
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 how deep the structure can be at compile time. 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. Consider this schema:
Example 8-24 Recursive 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.
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, users 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 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.
Consider the example where an out-of-line table is stored in ABCSECTIONTAB:
Example 8-25 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 the preceding example share the same default table, ABCSECTIONTAB.
However, the following example illustrates invalid default table sharing recursive elements (XyZSection) do not share the same out-of-line table.:
Example 8-26 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 extract(value(p), '//XyzSection') FROM xyzcode p;
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 8-25:
SELECT extract(value(x), '//AbcSection') FROM abccodetab;
Both of the AbcSection elements are stored in the same table, abcsectiontab. The extract goes to the underlying abcsectiontab table.
Consider this query when DOCID is present:
SELECT extract(value(x), '/AbcCode/AbcSection//AbcSection') 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.
You can disable the creation of the DOCID column by specifying an optional last parameter of DBMS_XMLSCHEMA.registerSchema when calling this procedure. This disables DOCID creation in all XMLType tables generated during schema registration. The parameters of the procedure registerSchema are:
PROCEDURE registerSchema(
SCHEMAURL IN VARCHAR2,
SCHEMADOC IN VARCHAR2,
LOCAL IN BOOLEAN := TRUE,
GENTYPES IN BOOLEAN := TRUE,
GENBEAN IN BOOLEAN := FALSE,
GENTABLES IN BOOLEAN := TRUE,
FORCE IN BOOLEAN := FALSE,
OWNER IN VARCHAR2 := '',
OPTIONS IN pls_integer := 0);
For DOCID columns not to be generated, set the parameter options to:
DBMS_XMLSCHEMA.REGISTER_NODOCID
This section describes guidelines for using XML schema with Oracle XML DB.
When you use a bind variable, Oracle Database rewrites the queries for the cases where the bind variable is used in place of a string literal value. You can also use the CURSOR_SHARING set to force Oracle Database to always use bind variables for all string expressions.
XPath Rewrite with Bind Variables
When bind variables are used as string literals in XPath, the expression can be rewritten to use the bind variables. The bind variable must used in place of the string literal using the concatenation operator (||), and it must be surrounded by single quotation marks (') or double-quotes (") inside the XPath string. The following example illustrates the use of the bind variable with XPath rewrite.
Example 8-27 Using Bind Variables in XPath
BEGIN
DBMS_XMLSCHEMA.registerschema(
'bindtest.xsd',
'<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb">
<xs:element name="Employee" xdb:SQLType="EMP_BIND_TYPE">
<xs:complexType>
<xs:sequence>
<xs:element name="EmployeeId" type="xs:positiveInteger"/>
<xs:element name="PhoneNumber" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>',
TRUE,
TRUE,
FALSE,
FALSE);
END;
/
-- Create table corresponding to the Employee element
CREATE TABLE emp_bind_tab OF XMLType
ELEMENT "bindtest.xsd#Employee";
-- Create an index to illustrate the use of bind variables
CREATE INDEX employeeId_idx ON emp_bind_tab
(extractValue(OBJECT_VALUE, '/Employee/EmployeeId'));
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/Employee/PhoneNumber')
FROM emp_bind_tab
WHERE existsNode(OBJECT_VALUE, '/Employee[EmployeeId="'||:1||'"] ') = 1;
SELECT PLAN_TABLE_OUTPUT
FROM table(DBMS_XPLAN.display('plan_table', NULL, 'serial'))/
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------
| Id | Operation | Name |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
| 1 | TABLE ACCESS BY INDEX ROWID| EMP_BIND_TAB |
|* 2 | INDEX RANGE SCAN | EMPLOYEEID_IDX |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("SYS_ALIAS_1"."SYS_NC00008$"=TO_NUMBER(:1))
The bind variable :1 is used as a string literal value enclosed by double-quotes ("). This lets Oracle XML DB rewrite the XPath expression '/Employee[EmployeeId=" ' || :1 || '"]', and the optimizer can use the EmployeeId_idx index to satisfy the predicate.
Setting CURSOR_SHARING to FORCE
With XPath rewrite, Oracle Database changes the input XPath expression to use the underlying columns. This means that for a given XPath there is a particular set of columns or tables that is referenced underneath. This is a compile-time operation, because the shared cursor must know exactly which tables and columns it references. This cannot change with each row or instantiation of the cursor.
Hence if the XPath expression is itself a bind variable, Oracle Database cannot do any rewrites, because each instantiation of the cursor can have totally different XPath expressions. This is similar to binding the name of the column or table in a SQL query. For example, SELECT * FROM table(:1).
Note:
You can specify bind variables on the right side of the query. For example, this query uses the usual bind variable sharing:SELECT * FROM purchaseorder
WHERE extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference')
= :1;
When CURSOR_SHARING is set to FORCE, by default each string constant including XPath becomes a bind variable. When Oracle Database then encounters SQL functions extractValue, existsNode, and so on, it looks at the XPath bind variables to check if they are really constants. If so, it uses them and rewrites the query. Hence there is a large difference depending on where the bind variable is used.
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 either protocols (FTP, HTTP(S), or DAV) or PL/SQL function DBMS_XDB.createResource.
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.
These optimizations are most useful when there are no triggers on the base table. For situations where triggers appear, the performance may be suboptimal.
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. Instead of creating the entire document in memory and then pushing it out to disk (a process that leads to excessive memory usage and in some instances a load failure due to inadequate system memory), we create the documents in finite chunks of memory called loadable units. In the example case, if we assume that each line item needs 1 KB of memory and we want to use loadable units of size 512 KB each, then each loadable unit will contain 512 line items, and there will be approximately 20 such units. Moreover, if we want the entire memory representation of the document never to exceed 2 MB in size, then we ensure that at any time no more than 4 loadable units are maintained in the memory. We 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 the parameter xdbcore-loadableunit-size whose default value is 16 KB. The user can indicate the amount of memory to be given to the document by setting the xdbcore-xobmem-bound parameter which defaults to 1 MB. The values to 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.
In the preceding example, when we do the FTP load of the document, 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
Typically if you have 1 GB of addressable PGA, give about 1/10th of PGA to the document. So, xobcore-xobmem-bound should be set to 1/10 of addressable PGA which equals 100M. During full document retrievals and loads, the xdbcore-loadableunit-size should be as close to the xobcore-xobmem-bound size as possible, within some error. However, in practice, we set it to half the value of xdbcore-xobmem-bound; in this case this is 50 MB. Starting with these values, try to load the document. In case you run out of memory, lower the xdbcore-xobmem-bound and set the xdbcore-loadableunot-size to half of its value, and continue until the documents load. In case the load succeeds, try to see if you can increase the xdbcore-loadableunit-size to squeeze out better performance. If xdbcore-loadableunit-size equals xdbcore-xobmem-bound, then try to increase both parameters for further performance improvements.
