| Oracle® XML DB Developer's Guide 10g Release 1 (10.1) Part Number B10790-01 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 10g Release 1 (10.1) Part Number B10790-01 |
|
|
View PDF |
This chapter describes more advanced techniques for storing structured XML schema-based XMLType objects. It explains simpleType and complexType mapping from XML to SQL storage types and how querying on XMLType tables and columns based on this mapping are optimized using query rewrite techniques. It discusses the mechanism for generating XML schema from existing object types.
This chapter contains these topics:
Setting the SQLInLine Attribute to FALSE for Out-of-Line Storage
Creating Constraints on Repetitive Elements in Schema-Based XML Instance Documents
Guidelines for Loading and Retrieving Large Documents with Collections
An XML schema can be generated from an object-relational type automatically using a default mapping. The generateSchema() and generateSchemas() functions in the DBMS_XMLSCHEMA package take in a string that has the object type name and another that has the Oracle XML DB XML schema.
generateSchema() returns an XMLType containing an XML schema. It can optionally generate XML schema for all types referenced by the given object type or restricted only to the top-level types.
generateSchemas() is similar, except that it returns an XMLSequenceType of XML schemas, each corresponding to a different namespace. It also takes an additional optional argument, specifying 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 6-1 Generating XML Schema: Using generateSchema()
For example, given the object type:
CONNECT t1/t1
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 annotation 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>
How can you, after creating an XMLType table based on an XML schema, add a unique constraint to an attribute's elements? You may, for example, want to create a unique key based on an attribute of an element that repeats itself (therefore creating a collection type). To create constraints on elements that can occur more than once within the instance document, you must store the VARRAY as a table. This is also known as Ordered Collections in Tables (OCT). You can then create constraints on the OCT. Example 6-2 shows how the attribute No of <PhoneNumber> can appear more than once, and how a unique constraint can be added to ensure that the same number cannot be repeated within the same instance document.
|
Note: This constraint 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, simply omit the collection id column. |
Example 6-2 Adding Unique Constraints to an Attribute's Element
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.
CREATE TABLE po_xtab OF XMLType; -- The default is CLOB based storage.
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
By default, a sub-element is mapped to an embedded object attribute. However, there may be scenarios where out-of-line storage offers better performance. In such cases the SQLInline attribute can be set to false, and Oracle XML DB generates an object type with an embedded REF attribute. REF points to another instance of XMLType that corresponds to the XML fragment that gets stored out-of-line. Default XMLType tables are also created to store the out-of-line fragments.
Figure 6-1 illustrates the mapping of a complexType to SQL for out-of-line storage.
Figure 6-1 Mapping complexType to SQL for Out-of-Line Storage
Example 6-3 Oracle XML DB XML Schema: complexType Mapping - Setting SQLInLine Attribute to False for Out-of-Line Storage
In this example, attribute xdb:SQLInLine of element Addr is set to false. The resulting object type OBJ_T2 has a column of type XMLType with an embedded REF attribute. The REF attribute points to another XMLType instance created of object type OBJ_T1 in table Addr_tab. Table Addr_tab has columns Street and City. The latter XMLType instance is stored 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"
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('emp.xsd', doc);
END;
/
On registering this XML schema, Oracle XML DB generates the following types and XMLType tables:
CREATE TYPE ADDR_T AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
Street VARCHAR2(4000),
City VARCHAR2(4000));
CREATE TYPE EMP_T AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
Name VARCHAR2(4000),
Age NUMBER,
Addr REF XMLType) NOT FINAL;
Two XMLType tables are also created: EMP_TAB and ADDR_TAB. Table EMP_TAB holds all the employees and contains an object reference to point to the address values that are stored only in table ADDR_TAB.
The advantage of this model is that it lets you query the out-of-line table (ADDR_TAB in this case) directly, to look up the address information. For example, if you want to get the distinct city information for all the employees, you can query the table ADDR_TAB directly.
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>Jason Miller</Name>
<Age>22</Age>
<Addr>
<Street>Julian 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>Jack Simon</Name>
<Age>23</Age>
<Addr>
<Street>Mable Street</Street>
<City>Redwood City</City>
</Addr>
</x:Employee>'));
REM The ADDR_TAB stores the addresses and can be queried directly
SELECT DISTINCT Extractvalue(object_value,'/Addr/City') AS city FROM ADDR_TAB;
CITY
------------------
Redwood City
San Francisco
The disadvantage of this storage is that to get the whole Employee element you need to look up an additional table for the address.
XPath expressions that involve elements stored out of line get rewritten. In this case, the query involves a join with the out-of-line table. For example, the following EXPLAIN PLAN shows how a query involving Employee and Addr elements is handled.
EXPLAIN PLAN FOR
SELECT Extractvalue(object_value,
'/x:Employee/Name',
'xmlns:x="http://www.oracle.com/emp.xsd"')
FROM emp_tab x
WHERE Existsnode(value(x),
'/x:Employee/Addr[City="San Francisco"]',
'xmlns:x="http://www.oracle.com/emp.xsd"')=1;
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
---------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------
| 0 | SELECT STATEMENT |
|* 1 | FILTER |
| 2 | TABLE ACCESS FULL | EMP_TAB |
|* 3 | TABLE ACCESS BY INDEX ROWID | ADDR_TAB |
|* 4 | INDEX UNIQUE SCAN | SYS_C003111 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------------
1 - filter(EXISTS(SELECT 0
FROM "SCOTT"."ADDR_TAB" "SYS_ALIAS_1"
WHERE "SYS_ALIAS_1"."SYS_NC_OID$"=:B1
AND "SYS_ALIAS_1"."SYS_NC00009$"='San Francisco'))
3 - filter("SYS_ALIAS_1"."SYS_NC00009$"='San Francisco')
4 - access("SYS_ALIAS_1"."SYS_NC_OID$"=:B1)
In this example, the XPath expression was rewritten to an Exists subquery that queries table ADDR_TAB and joins it with table EMP_TAB using the object identifier column in table ADDR_TAB. The optimizer uses a full table scan to scan all the rows in the employee table and uses the unique index on the SYS_NC_OID$ column in the address table to look up the address.
If there are a lot of entries in the ADDR_TAB, then you can make this query more efficient by creating an index on the City column.
CREATE INDEX addr_city_idx ON ADDR_TAB (extractvalue(object_value,'/Addr/City'));
The EXPLAIN PLAN for the previous statement now uses the addr_city_idx index.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
-------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------
| 0 | SELECT STATEMENT |
|* 1 | FILTER |
| 2 | TABLE ACCESS FULL | EMP_TAB |
|* 3 | TABLE ACCESS BY INDEX ROWID| ADDR_TAB |
|* 4 | INDEX RANGE SCAN | ADDR_CITY_IDX |
-------------------------------------------------------
Predicate Information (identified by operation id):
-------------------------------------------------------
1 - filter(EXISTS (SELECT 0
FROM "SCOTT"."ADDR_TAB" "SYS_ALIAS_1"
WHERE "SYS_ALIAS_1"."SYS_NC_OID$"=:B1
AND "SYS_ALIAS_1"."SYS_NC00009$"='San Francisco'))
3 - access("SYS_ALIAS_1"."SYS_NC_OID$"=:B1)
4 - filter("SYS_ALIAS_1"."SYS_NC00009$"='San Francisco')
You can also map list items to be stored out of line. In this case, instead of a single REF column, the parent element will contain a VARRAY of REF values that point to the members of the collection. For example, consider the case where we have a list of addresses for each employee and map that to an 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_T2">
<sequence>
<element name="Name" type="string"/>
<element name="Age" type="decimal"/>
<element name="Addr" xdb:SQLInline="false"
maxOccurs="unbounded" xdb:defaultTable="ADDR_TAB2">
<complexType xdb:SQLType="ADDR_T2">
<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_TAB2"/>
</schema>';
BEGIN
DBMS_XMLSCHEMA.registerSchema('emprefs.xsd', doc);
END;
/
On registering this XML schema, Oracle XML DB now generates the following types and XMLType tables:
CREATE TYPE ADDR_T2 AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
Street VARCHAR2(4000),
City VARCHAR2(4000));
CREATE TYPE EMP_T2 AS OBJECT (SYS_XDBPD$ XDB.XDB$RAW_LIST_T,
Name VARCHAR2(4000),
Age NUMBER,
Addr XDB.XDB$XMLTYPE_REF_LIST_T) NOT FINAL;
The employee type (EMP_T2) now contains a VARRAY of REF values to address instead of a single REF attribute as in the previous XMLSchema. By default this VARRAY of REF values is stored in-line in the employee (EMP_TAB2) table. This storage is ideal for the cases where the more selective predicates in the query are on the employee table. This is because storing the VARRAY in line effectively forces any query involving the two tables to always be driven off of the employee table as there is no way to efficiently join back from the address table. The following example shows the plan for a query that selects the names of all San Francisco-based employees, and the streets in which they live, in an unnested form.
EXPLAIN PLAN FOR
SELECT Extractvalue(value(e), '/x:Employee/Name',
'xmlns:x="http://www.oracle.com/emp.xsd"') AS name,
Extractvalue(value(a), '/Addr/Street') AS street
FROM
EMP_TAB2 e,
TABLE(XMLSequence(Extract(value(e),
'/x:Employee/Addr',
'xmlns:x="http://www.oracle.com/emp.xsd"'))) a
WHERE Extractvalue(value(a),'/Addr/City') = 'San Francisco';
Explained.
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
| Id | Operation | Name |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS FULL | EMP_TAB2 |
| 4 | COLLECTION ITERATOR PICKLER FETCH| |
|* 5 | TABLE ACCESS BY INDEX ROWID | ADDR_TAB2 |
|* 6 | INDEX UNIQUE SCAN | SYS_C003016 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - filter("SYS_ALIAS_2"."SYS_NC00009$"='San Francisco')
6 - access(VALUE(KOKBF$)="SYS_ALIAS_2"."SYS_NC_OID$")
If there are several Addr elements for each employee, then building an index on the City element in table ADDR_TAB2 will help speed up the previous query.
In cases where the number of employees is large, a full table scan of the EMP_TAB2 table is too expensive. The correct plan is to query the address table on the City element and then join back with the employee table.
This can be achieved by storing the VARRAY of REF values as a separate table, and creating an index on the REF values in that table. This would allow Oracle Database to query the address table, get an object reference (REF) to the relevant row, join it with the intermediate table storing the list of REF values and then join that table back with the employee table.
The intermediate table can be created by setting the attribute xdb:storeVarrayAsTable to TRUE in the XMLSchema definition. This forces the schema registration to store all VARRAY values as separate tables.
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"
xdb:storeVarrayAsTable="true">
<complexType name="EmpType" xdb:SQLType="EMP_T3">
<sequence>
<element name="Name" type="string"/>
<element name="Age" type="decimal"/>
<element name="Addr" xdb:SQLInline="false"
maxOccurs="unbounded" xdb:defaultTable="ADDR_TAB3">
<complexType xdb:SQLType="ADDR_T3">
<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_TAB3"/>
</schema>';
BEGIN
DBMS_XMLSCHEMA.registerSchema('empreftab.xsd', doc);
END;
/
In addition to creating the types ADDR_T3 and EMP_T3 and the tables EMP_TAB3 and ADDR_TAB3, the schema registration also creates the intermediate table that stores the list of REF values.
SELECT table_name FROM user_nested_tables WHERE parent_table_name='EMP_TAB3'; TABLE_NAME ------------------------------ SYS_NTyjtiinHKYuTgNAgAIOXPOQ== REM Rename nested table to more meaningful name RENAME "SYS_NTyjtiinHKYuTgNAgAIOXPOQ==" TO EMP_TAB3_REFLIST; DESCRIBE EMP_TAB3_REFLIST Name Null? Type ------------- ---- --------------------------- COLUMN_VALUE REF OF XMLTYPE
We can create an index on the REF value in this table. Indexes on REF values can be only be created if the REF is scoped or has a referential constraint. Creating a scope on a REF column implies that the REF only stores pointers to objects in a particular table. In this example, the REF values in the EMP_TAB3_REFLIST will only point to objects in the ADDR_TAB3 table, so we can create a scope constraint and an index on the REF column, as follows.
ALTER TABLE emp_tab3_reflist ADD SCOPE FOR (column_value) IS addr_tab3; CREATE INDEX reflist_idx ON emp_tab3_reflist (column_value); REM Also create an index on the city element CREATE INDEX city_idx ON ADDR_TAB3 p (extractvalue(value(p),'/Addr/City'));
Now, the EXPLAIN PLAN for the earlier query shows the use of the city_idx index, followed by a join with tables EMP_TAB3_REFLIST and EMP_TAB3.
EXPLAIN PLAN FOR
SELECT Extractvalue(value(e), '/x:Employee/Name',
'xmlns:x="http://www.oracle.com/emp.xsd"') AS name,
Extractvalue(value(a), '/Addr/Street') AS street
FROM EMP_TAB3 e,
TABLE(XMLSequence(Extract(value(e), '/x:Employee/Addr',
'xmlns:x="http://www.oracle.com/emp.xsd"'))) a
WHERE Extractvalue(value(a),'/Addr/City')='San Francisco';
SQL> @?/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------
| Id | Operation | Name |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | NESTED LOOPS | |
| 2 | NESTED LOOPS | |
| 3 | TABLE ACCESS BY INDEX ROWID | ADDR_TAB3 |
|* 4 | INDEX RANGE SCAN | CITY_IDX |
|* 5 | INDEX RANGE SCAN | REFLIST_IDX |
| 6 | TABLE ACCESS BY INDEX ROWID | EMP_TAB3 |
|* 7 | INDEX UNIQUE SCAN | SYS_C003018 |
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("SYS_ALIAS_2"."SYS_NC00009$"='San Francisco')
5 - access("EMP_TAB3_REFLIST"."COLUMN_VALUE"="SYS_ALIAS_2"."SYS_NC_OID$")
7 - access("NESTED_TABLE_ID"="E"."SYS_NC0001100012$")
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, 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 as follows:
http://xmlns.oracle.com/xdb/schemas/<database-user>/<schemaURL-minus-protocol>
Example 6-4 Using 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 SCOTT has a local XML schema with the same URL:
http://www.example.com/po.xsd
User JOE can reference the local XML schema owned by SCOTT as follows:
http://xmlns.oracle.com/xdb/schemas/SCOTT/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) or Binary Large Object (BLOB), as shown in Figure 6-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 6-5 Oracle XML DB XML Schema: complexType Mapping 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_T2">
<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 6-2 Mapping complexType XML Fragments to Character Large Objects (CLOBs)
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
For complexType, Oracle XML DB handles inheritance in the XML schema as follows:
For complexTypes declared to extend other complexTypes, 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 complexTypes declared to restrict other complexTypes, 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 6-6 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, and hence, all SQL object types are created as non-final types. |
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 6-7 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 datatype of the body attribute is based on simpleType which defines the body type.
Example 6-8 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 6-9 Oracle XML DB XML Schema: Mapping complexType to Any/AnyAttributes
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 schema-based XML, wherein elements and attributes in the XML data have XML Schema type information associated with them. However, XPath 1.0 is not aware of type information. Oracle XML DB extends XPath 1.0 with the following functions to support examining of type information:
instanceof() in the namespace http://xmlns.oracle.com/xdb
instanceof-only() in the namespace http://xmlns.oracle.com/xdb
An element is an instance of a specified XML Schema 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 refers to a type that extends or restricts T, or extends or restricts another subtype of T.
XPath queries can use instanceof-only() to restrict the result set to nodes of a certain type, and instanceof() to restrict it to nodes of a certain type and its subtypes for schema-based XML data. For non-schema-based data, elements and attributes do not have type information. Therefore, the functions return FALSE for non-schema-based XML data.
The semantics of the functions are as follows:
ora:instanceof-only(): Function ora:instanceof-only() has the following signature:
boolean instanceof-only(nodeset nodeset-expr,
string typename [, string schema-url])
On schema-based data, the XPath function instanceof-only evaluates the xpath-expr corresponding to nodeset-expr and determines the XML Schema type for each of the resultant node(s). Note that the xpath expression nodeset-expr is typically a relative XPath expression. If the type of any of the nodes exactly matches the name typename, optionally qualified with a namespace prefix, then the XPath function returns TRUE. Otherwise, the function returns FALSE. The XPath function returns FALSE for non-schema-based data.
Example 6-10 Using ora:instanceof-only
The following query selects Name attributes of AE children of the element Person that are of type PersonType (subtypes of PersonType are not matched).
SELECT extract(value(p),
'/p9:Person[ora:instanceof-only(AE,"p9:PersonType")]/AE/Name',
'xmlns:p9="person9.xsd" xmlns:ora="http://xmlns.oracle.com/xdb"')
FROM PO_Table p;
ora:instanceof(): Function ora:instanceof() has the following signature:
boolean instanceof(nodeset nodeset-expr,
string typename [, string schema-url])
On schema-based data, the XPath function instanceof evaluates the xpath-expr corresponding to nodeset-expr and determines the XML Schema type for each of the resultant node(s). Note that the xpath expression nodeset-expr is typically a relative xpath expression. If the type of any of the nodes exactly matches the name typename, optionally qualified with a namespace prefix, then the XPath function returns TRUE. Otherwise, the function returns FALSE. The XPath function returns FALSE for non-schema-based data.
For each node that matches the xpath expression nodeset-expr, the qualified name of the type of the node is determined. For ora:instanceofonly(), if the name and namespace of the type exactly matches the specified typename, the function returns TRUE. For ora:instanceof(), if the name and namespace of the type of the node or one of its supertypes exactly matches the specified typename, the function return TRUE. Otherwise, processing continues with the next node in the node set.
The schema-url parameter can additionally be specified to indicate the schema location URL for the type to be matched. If the schema-url parameter is not specified, then the schema location URl is not checked. If the parameter is specified, then the schema in which the type of the node is declared must match the schema-url parameter.
Example 6-11 Using ora:instanceof
The following query selects Name attributes of AE children of the element Person that are of type PersonType or one of its subtypes.
SELECT extract(value(p),
'/p9:Person[ora:instanceof(AE,"p9:PersonType")]/AE/Name',
'xmlns:p9="person9.xsd" xmlns:ora="http://xmlns.oracle.com/xdb"')
FROM PO_Table p;
Using ora:instanceof in a heterogeneous schema storage: One of the use cases for the schema location parameter is the heterogeneous XML Schema scenario. I f your scenario involves a schema-based table, consider omitting the schema location parameter. Heterogeneous XML Schema-based data can be present in a single table.
Consider a non-schema-based table of XMLType. Each row in the table is an XML document. Suppose that the contents of each XML document is XML data for which schema information has been specified. If the data in the table is converted to schema-based data through a subsequent operation, then the rows in the table could pertain to different schemas. In such a case, you can specify not only the name and the namespace of the type to be matched, but also the schema location URL.
Example 6-12 Using ora:instanceof in a Heterogeneous Schema Storage
In the non-schema-based table non_sch_p_tab, the following query matches elements of type PersonType that pertain to schema person9.xsd.
SELECT extract(
createschemabased(
value(p)),
'/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" p;
The W3C XML Schema Recommendation allows complexTypes and global elements to contain recursive references. For example, a complexType definition may contain an element based on the complexType itself being defined, 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 6-13 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="decendant" type="personType" minOccurs="0"
maxOccurs="unbounded" xdb:SQLName="DESCENDANT"
xdb:defautlTable="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 shown in Example 6-13 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 personType.
Oracle XML DB supports XML schemas that define this kind of structure. To break the cycle implicit in this kind of structure, recursive elements are stored as rows in a separate XMLType table. The table used to manage these elements is an XMLType table, created during the XML schema registration process.
Consequently it is important to ensure that the genTables parameter is always 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.
Cycles in the XML schema are broken while generating the object types, because object types do not allow cycles, by introducing a REF attribute at the point at which the cycle gets completed. Thus part of the data is stored out-of-line yet still belongs to the parent XML document when it is retrieved.
Example 6-14 XML Schema: Cycling Between complexTypes
XML schemas permit cycling between definitions of complexTypes. Figure 6-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 support weak cycles, that is, cycles involving REF (reference) attributes. Therefore, cyclic XML schema definitions are mapped to SQL object types such that any cycles are avoided by forcing SQLInline="false" at the appropriate point. This creates a weak 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 6-3 Cross Referencing Between Different complexTypes in the Same XML Schema
Example 6-15 XML Schema: Cycling Between complexTypes, Self-Referencing
Another example of a cyclic complexType involves the declaration of the complexType having a reference to itself. The following is an example of type <SectionT> that 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. And it is a VARRAY of REF references to XMLType values in order 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, myPOs, can then be created to store instances conforming to element, PurchaseOrder, of this XML schema, in an object-relational format as follows:
CREATE TABLE MyPOs OF XMLType ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder";
Figure 6-4 illustrates schematically how a complexType can reference or cycle itself.
Figure 6-4 complexType Self Referencing Within an XML Schema
Hidden columns are created. These correspond to the object type to which the PurchaseOrder element has been mapped. In addition, an XMLExtra object column is created to store the 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 ", under "Changing the Storage Options on an XMLType Column Using XMLData". |
existsNode() returns a numerical value 0 or 1 indicating if the XPath returns any nodes (text() or element nodes). Based on the mapping discussed in the earlier section, an existsNode() simply checks if a scalar element is not NULL in the case where the XPath targets a text() node or a non-scalar node, and checks for the existence of the element using the SYS_XDBPD$ otherwise. If the SYS_XDBPD$ attribute is absent, then the existence of a scalar node is determined by the NULL information for the scalar column.
Table 6-1 shows the mapping of various XPaths in the case of existsNode() when document ordering is preserved, that is, when SYS_XDBPD$ exists and maintainDOM="true" in the schema document.
Table 6-1 XPath Mapping for existsNode() with Document Ordering Preserved
| XPath Expression | Maps to |
|---|---|
/PurchaseOrder |
CASE WHEN XMLData IS NOT NULL THEN 1 ELSE 0 END |
/PurchaseOrder/@PurchaseDate |
CASE WHEN Check_Node_Exists(XMLData.SYS_XDBPD$, 'PurchaseDate')=1
THEN 1 ELSE 0 END
|
/PurchaseOrder/PONum |
CASE WHEN Check_Node_Exists(XMLData.SYS_XDBPD$, 'PONum')=1
THEN 1 ELSE 0 END
|
/PurchaseOrder[PONum=2100] |
CASE WHEN XMLData."PONum"=2100 THEN 1 ELSE 0 |
/PurchaseOrder[PONum=2100]/@PurchaseDate |
CASE WHEN XML Data."PONum"=2100
AND Check_Node_Exists(XMLData.SYS_XDBPD$,
'PurchaseDate')=1
THEN 1 ELSE 0 END
|
/PurchaseOrder/PONum/text() |
CASE WHEN XMLData."PONum" IS NOT NULL THEN 1 ELSE 0 |
/PurchaseOrder/Item |
CASE WHEN EXISTS (
SELECT NULL FROM TABLE ( XMLData."Item" ) x
WHERE value(x) IS NOT NULL) THEN 1 ELSE 0 END
|
/PurchaseOrder/Item/Part |
CASE WHEN EXISTS (
SELECT NULL FROM TABLE (XMLData."Item" ) x
WHERE Check_Node_Exists(x.SYS_XDBPD$, 'Part')=1)
THEN 1 ELSE 0 END
|
/PurchaseOrder/Item/Part/text() |
CASE WHEN EXISTS (
SELECT NULL FROM TABLE (XMLData."Item" ) x
WHERE x."Part" IS NOT NULL) THEN 1 ELSE 0 END
|
Example 6-16 existsNode Mapping with Document Order Maintained
Using the preceding mapping, a query which checks whether the PurchaseOrder with number 2100 contains a part with price greater than 2000:
SELECT count(*) FROM mypos p WHERE EXISTSNODE(value(p),'/PurchaseOrder[PONum=1001 and Item/Price > 2000]')=1;
would become:
SELECT count(*)
FROM mypos p
WHERE CASE
WHEN p.XMLData."PONum"=1001
AND exists(SELECT NULL FROM TABLE (XMLData."Item") p
WHERE p."Price" > 2000))
THEN 1 ELSE 0 END
=1;
The CASE expression gets further optimized due to the constant relational equality expressions and this query becomes:
SELECT count(*)
FROM mypos p
WHERE p.XMLData."PONum"=1001
AND exists(SELECT NULL FROM TABLE (p.XMLData."Item") x
WHERE x."Price" > 2000);
which would use relational indexes for its evaluation, if present on the Part and PONum columns.
If the SYS_XDBPD$ does not exist (that is, if the XML schema specifies maintainDOM="false") then NULL scalar columns map to non-existent scalar elements. Hence you do not need to check for the node existence using the SYS_XDBPD$ attribute. Table 6-2 shows the mapping of existsNode() in the absence of the SYS_XDBPD$ attribute.
Table 6-2 XPath Mapping for existsNode Without Document Ordering
| XPath Expression | Maps to |
|---|---|
/PurchaseOrder |
CASE WHEN XMLData IS NOT NULL THEN 1 ELSE 0 END |
/PurchaseOrder/@PurchaseDate |
CASE WHEN XMLData.'PurchaseDate' IS NOT NULL THEN 1 ELSE 0 END |
/PurchaseOrder/PONum |
CASE WHEN XMLData."PONum" IS NOT NULL THEN 1 ELSE 0 END |
/PurchaseOrder[PONum=2100] |
CASE WHEN XMLData."PONum"=2100 THEN 1 ELSE 0 END |
/PurchaseOrder[PONum=2100]/@PurchaseOrderDate |
CASE WHEN XMLData."PONum"=2100 AND
XMLData."PurchaseDate" NOT NULL THEN 1 ELSE 0 END
|
/PurchaseOrder/PONum/text() |
CASE WHEN XMLData."PONum" IS NOT NULL THEN 1 ELSE 0 END |
/PurchaseOrder/Item |
CASE WHEN EXISTS (
SELECT NULL FROM TABLE (XMLData."Item" ) x
WHERE value(x) IS NOT NULL) THEN 1 ELSE 0 END
|
/PurchaseOrder/Item/Part |
CASE WHEN EXISTS (
SELECT NULL FROM TABLE (XMLData."Item") x
WHERE x."Part" IS NOT NULL) THEN 1 ELSE 0 END
|
/PurchaseOrder/Item/Part/text() |
CASE WHEN EXISTS (
SELECT NULL FROM TABLE (XMLData."Item") x
WHERE x."Part" IS NOT NULL) THEN 1 ELSE 0 END
|
extractValue() is a shortcut for extracting text nodes and attributes using extract() and then using a getStringVal() or getNumberVal() to get the scalar content. extractValue returns the text nodes for scalar elements or the values of attribute nodes. extractValue() cannot handle returning multiple values or non-scalar elements.
Table 6-3 shows the mapping of various XPath expressions in the case of extractValue(). If an XPath expression targets an element, then extractValue retrieves the text node child of the element. Thus the two XPath expressions, /PurchaseOrder/PONum and /PurchaseOrder/PONum/text() are handled identically by extractValue and both of them retrieve the scalar content of PONum.
Table 6-3 XPath Mapping for extractValue()
| XPath Expression | Maps to |
|---|---|
/PurchaseOrder |
Not supported - extractValue can only retrieve values for scalar elements and attributes. |
/PurchaseOrder/@PurchaseDate |
XMLData."PurchaseDate" |
/PurchaseOrder/PONum |
XMLData."PONum" |
/PurchaseOrder[PONum=2100] |
(SELECT TO_XML(x.XMLData) FROM Dual WHERE x."PONum"=2100) |
/PurchaseOrder[PONum=2100]/@PurchaseDate |
(SELECT x.XMLData."PurchaseDate") FROM Dual WHERE x."PONum"=2100) |
/PurchaseOrder/PONum/text() |
XMLData."PONum" |
/PurchaseOrder/Item |
Not supported - extractValue can only retrieve values for scalar elements and attributes. |
/PurchaseOrder/Item/Part |
Not supported - extractValue cannot retrieve multiple scalar values. |
/PurchaseOrder/Item/Part/text() |
Not supported - extractValue cannot retrieve multiple scalar values. |
Example 6-17 Rewriting extractValue()
For example, a SQL query such as:
SELECT ExtractValue(value(p),'/PurchaseOrder/PONum') FROM mypos p WHERE ExtractValue(value(p),'/PurchaseOrder/PONum')=1001;
would become:
SELECT p.XMLData."PONum" FROM mypos p WHERE p.XMLData."PONum"=1001;
Because it gets rewritten to simple scalar columns, indexes on the PONum attribute, if any, can be used to satisfy the query.
ExtractValue can be used in index expressions. If the expression gets rewritten into scalar columns, then the index is turned into a B*Tree index instead of a function-based index.
Example 6-18 Creating Indexes with extract
For example:
CREATE INDEX my_po_index ON mypos x (Extract(value(x),'/PurchaseOrder/PONum/text()').getnumberval());
would get rewritten into:
CREATE INDEX my_po_index ON mypos x (x.XMLData."PONum");
and thus becomes a regular B*Tree index. This is useful, because unlike a function-based index, the same index can now satisfy queries that target the column, such as:
EXISTSNODE(value(x),'/PurchaseOrder[PONum=1001]')=1;
Function extract() retrieves the results of XPath as XML. The rewrite for extract() is similar to that of extractValue() for those Xpath expressions involving text nodes.
Table 6-4 shows the mapping of various XPath values in the case of extract(), when document order is preserved (that is, when SYS_XDBPD$ exists and maintainDOM="true" in the schema document).
Table 6-4 XPath Mapping for extract() with Document Ordering Preserved
| XPath | Maps to |
|---|---|
/PurchaseOrder |
XMLForest(XMLData as "PurchaseOrder") |
/PurchaseOrder/@PurchaseDate |
CASE WHEN Check_Node_Exists(XMLData.SYS_XDBPD$, 'PurchaseDate')=1
THEN XMLElement("PONum" , XMLData."PurchaseDate") ELSE NULL END
|
/PurchaseOrder/PONum |
CASE WHEN Check_Node_Exists(XMLData.SYS_XDBPD$, 'PONum')=1
THEN XMLElement("PONum" , XMLData."PONum") ELSE NULL END
|
/PurchaseOrder[PONum=2100] |
(SELECT XMLForest(XMLData as "PurchaseOrder") FROM DUAL
WHERE x."PONum"=2100)
|
/PurchaseOrder[PONum=2100]/@PurchaseDate |
(SELECT CASE WHEN
Check_Node_Exists(x.XMLData.SYS_XDBPD$,'PurchaseDate")=1
THEN XMLElement("PONum", XMLData."PurchaseDate")
ELSE NULL END
FROM DUAL WHERE x."PONum"=2100)
|
/PurchaseOrder/PONum/text() |
XMLElement("", XMLData.PONum)
|
/PurchaseOrder/Item |
(SELECT XMLAgg(XMLForest(value(p) as "Item"))
FROM TABLE ( x.XMLData."Item" ) p
WHERE value(p) IS NOT NULL )
|
/PurchaseOrder/Item/Part |
(SELECT XMLAgg(
CASE WHEN Check_Node_Exists(p.SYS_XDBPD$,'Part")=1
THEN XMLForest(p."Part" as "Part") ELSE NULL END)
FROM TABLE ( x.XMLData."Item" ) p)
|
/PurchaseOrder/Item/Part/text() |
(SELECT XMLAgg(XMLElement("PONum", p."Part") )
FROM TABLE ( x.XMLData."Item" ) x )
|
Example 6-19 XPath Mapping for extract() with Document Ordering Preserved
Using the mapping in Table 6-4, a query that extracts the PONum element where the purchase order contains a part with price greater than 2000:
SELECT Extract(value(p),'/PurchaseOrder[Item/Part > 2000]/PONum') FROM po_tab p;
would become:
SELECT (SELECT CASE WHEN Check_Node_Exists(p.XMLData.SYS_XDBPD$, 'PONum') = 1
THEN XMLElement("PONum", p.XMLData."PONum")
ELSE NULL END
FROM DUAL
WHERE exists(SELECT NULL FROM TABLE (XMLData."Item") p
WHERE p."Part" > 2000))
FROM po_tab p;
Check_Node_Exists is an internal function that is for illustration purposes only.
If the SYS_XDBPD$ does not exist, that is, if the XML schema specifies maintainDOM="false", then NULL scalar columns map to non-existent scalar elements. Hence you do not need to check for the node existence using the SYS_XDBPD$ attribute. Table 6-5 shows the mapping of existsNode() in the absence of the SYS_XDBPD$ attribute.
Table 6-5 XPath Mapping for extract() Without Document Ordering Preserved
| XPath | Equivalent to |
|---|---|
/PurchaseOrder |
XMLForest(XMLData AS "PurchaseOrder") |
/PurchaseOrder/@PurchaseDate |
XMLForest(XMLData."PurchaseDate" AS "PurchaseDate") |
/PurchaseOrder/PONum |
XMLForest(XMLData."PONum" AS "PONum") |
/PurchaseOrder[PONum=2100] |
(SELECT XMLForest(XMLData AS "PurchaseOrder") from Dual where x."PONum"=2100) |
/PurchaseOrder[PONum=2100]/@PurchaseDate |
(SELECT XMLForest(XMLData."PurchaseDate" AS "PurchaseDate "") from Dual where x."PONum"=2100) |
/PurchaseOrder/PONum/text() |
XMLForest(XMLData.PONum AS "") |
/PurchaseOrder/Item |
(SELECT XMLAgg(XMLForest(value(p) as "Item")
from TABLE ( x.XMLData."Item" ) p
where value(p) IS NOT NULL )
|
/PurchaseOrder/Item/Part |
(SELECT XMLAgg(XMLForest(p."Part" AS "Part")
from TABLE ( x.XMLData."Item" ) p)
|
/PurchaseOrder/Item/Part/text() |
(SELECT XMLAgg( XMLForest(p. "Part" AS "Part") )
from TABLE ( x.XMLData."Item" ) p )
|
A regular update using updateXML() involves updating a value of the XML document and then replacing the whole document with the newly updated document.
When XMLType is stored in an object-relational manner using XML schema mapping, updates are optimized to directly update pieces of the document. For example, updating the PONum element value can be rewritten to directly update the XMLData.PONum column instead of materializing the whole document in memory and then performing the update.
Function updateXML() must satisfy the following conditions for it to use the optimization:
The XMLType column supplied to updateXML() must be the same column being updated in the SET clause. For example:
UPDATE po_tab p SET value(p)=updatexml(value(p),...);
The XMLType column must have been stored in an object-relational manner using Oracle XML DB XML schema mapping.
The XPath expressions must not involve any predicates or collection traversals.
There must be no duplicate scalar expressions.
All XPath arguments in the updateXML() function must target only scalar content, that is, text nodes or attributes. For example:
UPDATE po_tab p
SET value(p) = updatexml(value(p),
'/PurchaseOrder/@PurchaseDate', '2002-01-02',
'/PurchaseOrder/PONum/text()', 2200);
If all the preceding conditions are satisfied, then the updateXML is rewritten into a simple relational update. For example, the preceding UPDATE operation becomes:
UPDATE po_tab p
SET p.XMLData."PurchaseDate" = TO_DATE('2002-01-02', 'SYYYY-MM-DD'),
p.XMLData."PONum" = 2100;
Date datatypes such as DATE, gMONTH, and gDATE have different formats in XML schema and SQL. In such cases, if the updateXML() has a string value for these columns, then the rewrite automatically puts the XML format string to convert the string value correctly. Thus, string value specified for DATE columns must match the XML date format, not the SQL DATE format.
XML schema documents can have cyclic dependencies that can prevent them from being registered one after the other in the usual manner. Examples of such XML schemas follow:
Example 6-20 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 option:
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 SYS.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;
/
Both XML schemas can be used to create tables:
CREATE TABLE foo OF SYS.XMLType XMLSCHEMA "xm40.xsd" ELEMENT "Emp"; CREATE TABLE foo2 OF SYS.XMLType XMLSCHEMA "xm40a.xsd" ELEMENT "Comp";
To register both these XML schemas that have a cyclic dependency on each other, you must use the FORCE parameter in DBMS_XMLSCHEMA.registerSchema as follows:
Step 1: Register s1.xsd in FORCE mode:
DBMS_XMLSCHEMA.registerSchema("s1.xsd", "<schema ...", ..., FORCE => TRUE)
At this point, s1.xsd is invalid and cannot be used.
Step 2: Register s2.xsd in FORCE mode:
DBMS_XMLSCHEMA.registerSchema("s2.xsd", "<schema ..", ..., FORCE => TRUE)
The second operation automatically compiles s1.xsd and makes both XML schemas valid.
See Figure 6-5. The preceding example is illustrated in the lower half of the figure.
This section describes guidelines for using XML schema and Oracle XML DB:
When you use bind variables, 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.
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 (') or double (") quotes inside the XPath string. The following example illustrates the use of the bind variable with query rewrite.
Example 6-21 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;
/
REM Create table corresponding to the Employee element
CREATE TABLE emp_bind_tab OF XMLType
ELEMENT "bindtest.xsd#Employee";
REM 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 p
WHERE ExistsNode(object_value, '/Employee[EmployeeId="'||:1||'"] ') = 1;
SQL> @?/rdbms/admin/utlxpls
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("P"."SYS_NC00008$"=TO_NUMBER(:1))
The bind variable :1 is used as a string literal value enclosed by double quotes ("). This allows the XPath expression '/Employee[EmployeeId=" ' || :1 || '"]' to be rewritten, and the optimizer can use the EmployeeId_idx index to satisfy the predicate.
With query rewrites, 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 itself is a bind variable, Oracle Database cannot do any rewrites, because each instantiation of the cursor can have totally different XPaths. 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 p WHERE extractvalue( value(p), '/PurchaseOrder/LineItems/LineItem/ItemNumber') := :1; |
When CURSOR_SHARING is set to FORCE, by default each string constant including XPath becomes a bind variable. When Oracle Database then encounters extractvalue(), existsnode(), and so on, it looks at the XPath bind variables to check if they are really constants. If so then it uses them and rewrites the query. Hence there is a large difference depending on where the bind variable is used.
After creating an XMLType table based on an XML schema, you may need to add a unique constraint to one of the elements. That element can occur more than once. To create constraints on elements that occur more than once in the XML instance document, you must store the VARRAY as a table. This is considered an Ordered Collection in the Table, or OCT. In an OCT the elements of the VARRAY are stored in separate tables. You can then create constraints on the OCT.
The following example shows the attribute No of <PhoneNumber> that can appear more than once, and a unique constraint added to ensure that the same number cannot be repeated in the same XML instance document.
Example 6-22 Creating Constraints on Elements in Schema-Based Tables that Occur More than Once Using OCT
In this example, the constraint applies to each collection and not across all XML 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, simply omit the collection id column.
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;
/
This returns the following:
PL/SQL procedure successfully completed. CREATE TABLE emp_tab OF XMLType XMLSCHEMA "emp.xsd" ELEMENT "Employee" VARRAY xmldata."PhoneNumber" STORE AS table phone_tab;
This returns:
Table created. ALTER TABLE phone_tab AD unique(nested_table_id, "No");
This returns:
Table altered.
INSERT INTO emp_tab
VALUES (XMLType('<Employee>
<EmployeeId>1234</EmployeeId>
<PhoneNumber No="1234"/>
<PhoneNumber No="2345"/>
</Employee>').createschemabasedxml('emp.xsd'));
This returns:
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:
INSERT INTO emp_tab values(XMLType( * ERROR at line 1: ORA-00001: unique constraint (SCOTT.SYS_C002136) violated
Two parameters were added to xdbconfig in Oracle Database 10g in order to control the amount of memory used by the loading operation. These tunable parameters provide mechanisms to optimize the loading process provided the following conditions are met:
The document is loaded either through protocols (FTP, HTTP, or DAV) or through the createResource API.
The document is a schema based document containing large collections (that is, it contains elements with maxoccurs set to a large number).
The collections of the document are stored as OCTs. This is achieved by either of the following ways:
Setting xdb:storeVarrayAsTable="true" in the schema definition which turns this storage option on for all collections of the schema.
By setting the table properties appropriately in the element definition.
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 allows the collections to 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 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 1K memory and we want to use loadable units of size 512K, then each loadable unit will contain 512K/1K = 512 line items and there will be approximately 20 such units. Moreover, if we wish that the entire memory representation of the document never exceeds 2M in size, we ensure that at any time no more than 2M/512K = 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 16K. The user can indicate the amount of memory to be given to the document by setting the xdbcore-xobmem-bound parameter which defaults to 1M. 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 Gigabyte 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 M. 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.
