Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2) Part Number A96620-01 |
|
This chapter introduces XML Schema and explains how XML schema is used in Oracle XML DB applications. It describes how to register your XML schema, create storage structures for storing schema-based XML, and generate Java Beans to access and manipulate data in Java applications.
It explains in detail the mapping from XML to SQL storage types, including techniques for maintaining the DOM fidelity of XML data.This chapter also describes how queries over XMLType
tables and columns based on this mapping are optimized using query rewrite techniques. It discusses the mechanism for generating XML schemas from existing object types.
This chapter contains the following sections:
The XML Schema Recommendation was created by the World Wide Web Consortium (W3C) to describe the content and structure of XML documents in XML. It includes the full capabilities of Document Type Definitions (DTDs) so that existing DTDs can be converted to XML schema. XML schemas have additional capabilities compared to DTDs.
XML Schema is a schema definition language written in XML. It can be used to describe the structure and various other semantics of conforming instance documents. For example, the following XML schema definition, po.xsd, describes the structure and other properties of purchase order XML documents.
This manual refers to an XML schema definition as an XML schema.
The following is an example of an XML schema definition, po.xsd
:
<schema targetNamespace="http://www.oracle.com/PO.xsd" xmlns:po="http://www.oracle.com/PO.xsd" xmlns="http://www.w3.org/2001/XMLSchema"> <complexType name="PurchaseOrderType"> <sequence> <element name="PONum" type="decimal"/> <element name="Company"> <simpleType> <restriction base="string"> <maxLength value="100"/> </restriction> </simpleType> </element> <element name="Item" maxOccurs="1000"> <complexType> <sequence> <element name="Part"> <simpleType> <restriction base="string"> <maxLength value="1000"/> </restriction> </simpleType> </element> <element name="Price" type="float"/> </sequence> </complexType> </element> </sequence> </complexType> <element name="PurchaseOrder" type="po:PurchaseOrderType"/> </schema>
The following is an example of an XML document that conforms to XML schema po.xsd
:
<PurchaseOrder xmlns="http://www.oracle.com/PO.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.oracle.com/PO.xsd http://www.oracle.com/PO.xsd"> <PONum>1001</PONum> <Company>Oracle Corp</Company> <Item> <Part>9i Doc Set</Part> <Price>2550</Price> </Item> </PurchaseOrder>
Oracle XML DB uses annotated XML schema as metadata, that is, the standard XML Schema definitions along with several Oracle XML DB-defined attributes. These attributes are in a different namespace and control how instance documents get mapped into the database. Since these attributes are in a different namespace from the XML schema namespace, such annotated XML schemas are still legal XML schema documents:
See Also:
Namespace of XML Schema constructs: |
When using Oracle XML DB, you must first register your XML schema. You can then use the XML schema URLs while creating XMLType
tables, columns, and views.
Oracle XML DB provides XML Schema support for the following tasks:
XMLType
tables, views and columns based on registered XML schemas.XMLType
tables.As described in Chapter 4, "Using XMLType", XMLType
is a datatype that facilitates storing XML in columns and tables in the database. XML schemas further facilitate storing XML columns and tables in the database, and they offer you more storage and access options for XML data along with space- performance-saving options.
For example, you can use XML schema to declare which elements and attributes can be used and what kinds of element nesting, and datatypes are allowed in the XML documents being stored or processed.
Using XML schema with Oracle XML DB provides a flexible setup for XML storage mapping. For example:
Which storage method you choose depends on how your data will be used and depends on the queriability and your requirements for querying and updating your data. In other words. Using XML schema gives you more flexibility for storing highly structured or unstructured data.
Another advantage of using XML schema with Oracle XML DB is that you can perform XML instance validation according to the XML schema and with respect to Oracle XML Repository requirements for optimal performance. For example,an XML schema can check that all incoming XML documents comply with definitions declared in the XML schema, such as allowed structure, type, number of allowed item occurences, or allowed length of items.
Also, by registering XML schema in Oracle XML DB, when inserting and storing XML instances using Protocols, such as FTP or HTTP, the XML schema information can influence how efficiently XML instances are inserted.
When XML instances must be handled without any prior information about them, XML schema can be useful in predicting optimum storage, fidelity, and access.
Oracle XML DB's XML schema functionality is available through the PL/SQL supplied package, DBMS_XMLSCHEMA
, a server-side component that handles the registration of XML schema definitions for use by Oracle XML DB applications.
Two of the main DBMS_XMLSCHEMA
functions are:
.
This deletes a previously registered XML schema, identified by its URL or XMLSchema name.An XML schema must be registered before it can be used or referenced in any context by Oracle XML DB. XML schema are registered by using DBMS_XMLSCHEMA.registerSchema()
and specifying the following:
XMLType
, or URIType.After registration has completed:
Use DBMS_XMLSCHEMA
to register your XML schema. This involves specifying the XML schema document and its URL, also known as the XML schema location.
Consider the following XML schema. It declares a complexType
called PurchaseOrderType
and an element PurchaseOrder
of this type. The schema is stored in the PL/SQL variable doc
. The following registers the XML schema at URL: http://www.oracle.com/PO.xsd
:
declare doc varchar2(1000) := '<schema targetNamespace="http://www.oracle.com/PO.xsd" xmlns:po="http://www.oracle.com/PO.xsd" xmlns="http://www.w3.org/2001/XMLSchema"> <complexType name="PurchaseOrderType"> <sequence> <element name="PONum" type="decimal"/> <element name="Company"> <simpleType> <restriction base="string"> <maxLength value="100"/> </restriction> </simpleType> </element> <element name="Item" maxOccurs="1000"> <complexType> <sequence> <element name="Part"> <simpleType> <restriction base="string"> <maxLength value="1000"/> </restriction> </simpleType> </element> <element name="Price" type="float"/> </sequence> </complexType> </element> </sequence> </complexType> <element name="PurchaseOrder" type="po:PurchaseOrderType"/> </schema>'; begin dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc); end;
The registered schema can be used to created XML schema-Based tables, or XML schema-based columns. For example, the following statement creates an a table with an XML schema-based column.
create table po_tab( id number, po sys.XMLType ) xmltype column po XMLSCHEMA "http://www.oracle.com/PO.xsd" element "PurchaseOrder";
The following shows an XMLType
instance that conforms to the preceding XML schema being inserted into the preceding table. The schemaLocation
attribute specifies the schema URL:
insert into po_tab values (1, xmltype('<PurchaseOrder xmlns="http://www.oracle.com/PO.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.oracle.com/PO.xsd http://www.oracle.com/PO.xsd"> <PONum>1001</PONum> <Company>Oracle Corp</Company> <Item> <Part>9i Doc Set</Part> <Price>2550</Price> </Item> <Item> <Part>8i Doc Set</Part> <Price>350</Price> </Item> </PurchaseOrder>'));
XML schemas can be registered as local or global:
When you register an XML schema, DBMS_XMLSCHEMA
adds an Oracle XML DB resource corresponding to the XML schema into the Oracle XML DB Repository. The XML schema URL determines the path name of the resource in Oracle XML DB Repository according to the following rules:
In Oracle XML DB, local XML schema resources are created under the /sys/schemas/<username>
directory. The rest of the path name is derived from the schema URL.
For example, a local XML schema with schema URL:
http://www.myco.com/PO.xsd
registered by SCOTT, is given the path name:
/sys/schemas/SCOTT/www.myco.com/PO.xsd.
Database users need appropriate permissions (ACLs) to create a resource with this path name in order to register the XML schema as a local XML schema.
By default, an XML schema belongs to you after registering the XML schema with Oracle XML DB. A reference to the XML schema document is stored in Oracle XML DB Repository, in directory:
/sys/schemas/<username>/....
For example, if you, SCOTT, registered the preceding XML schema, it is mapped to the file:
/sys/schemas/SCOTT/www.oracle.com/PO.xsd
Such XML schemas are referred to as local. In general, they are usable only by you to whom they belong.
In contrast to local schema, privileged users can register an XML schema as a global XML schema by specifying an argument in the DBMS_XMLSCHEMA
registration function.
Global schemas are visible to all users and stored under the /sys/schemas/PUBLIC/
directory in Oracle XML DB Repository.
Note: Access to this directory is controlled by Access Control Lists (ACLs) and, by default, is writeable only by a DBA. You need WRITE privileges on this directory to register global schemas.
See also Chapter 18, "Oracle XML DB Resource Security" for further information on privileges and for details on XDBAdmin role. |
You can register a local schema with the same URL as an existing global schema. A local schema always hides any global schema with the same name (URL).
For example, a global schema registered by SCOTT with the URL:
www.myco.com/PO.xsd
is mapped to Oracle XML DB Repository at:
/sys/schemas/PUBLIC/www.myco.com/PO.xsd
Database users need appropriate permissions (ACLs) to create this resource in order to register the XML schema as global.
As part of registering an XML schema, Oracle XML DB also performs several other steps to facilitate storing, accessing, and manipulating XML instances that conform to the XML schema. These steps include:
XMLType
tables for all root elements. You can also specify any column and table level constraints for use during table creation.
You can delete your registered XML schema by using the DBMS_XMLSCHEMA.deleteSchema
procedure. When you attempt to delete an XML schema, DBMS_XMLSCHEMA
checks:
A FORCE mode option is provided while deleting XML schemas. If you specify the FORCE mode option, the XML schema deletion proceeds even if it fails the dependency check. In this mode, XML schema deletion marks all its dependents as invalid.
The CASCADE mode option drops all generated types, default tables, and Java beans as part of a previous call to register schema.
See Also:
Oracle9i XML API Reference - XDK and Oracle XML DB the chapter on |
The following example deletes XML schema PO.xsd
. First, the dependent table po_tab
is dropped. Then, the schema is deleted using the FORCE
and CASCADE
modes with DBMS_XMLSCHEMA
.DELETESCHEMA
:
drop table po_tab; EXEC dbms_xmlschema.deleteSchema('http://www.oracle.com/PO.xsd', dbms_xmlschema.DELETE_CASCADE_FORCE);
The following sections describe guidelines for registering XML schema with Oracle XML DB.
The following objects depend on a registered XML schemas:
XMLType
column that conforms to some element in the XML schema.After an XML schema has been registered, it can be used to create XML schema-based XMLType
tables, views, and columns by referencing the following:
For example you can create an XML schema-based XMLType
table as follows:
CREATE TABLE po_tab OF XMLTYPE XMLSCHEMA "http://www.oracle.com/PO.xsd" ELEMENT "PurchaseOrder";
The following statement inserts schema-conformant data:
insert into po_tab values ( xmltype('<PurchaseOrder xmlns="http://www.oracle.com/PO.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.oracle.com/PO.xsd http://www.oracle.com/PO.xsd"> <PONum>1001</PONum> <Company>Oracle Corp</Company> <Item> <Part>9i Doc Set</Part> <Price>2550</Price> </Item> <Item> <Part>8i Doc Set</Part> <Price>350</Price> </Item> </PurchaseOrder>'));
You can validate an XMLType
instance against a registered XML schema by using one of the validation methods.
The following PL/SQL example validates an XML instance against XML schema PO.xsd
:
declare xmldoc xmltype; begin -- populate xmldoc (by fetching from table) select value(p) into xmldoc from po_tab p; -- validate against XML schema xmldoc.schemavalidate(); if xmldoc.isschemavalidated() = 1 then dbms_output.put_line('Data is valid'); else dbms_output.put_line('Data is invalid'); end if; end;
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.
These rules imply that, by default, users cannot reference the following kinds of XML schemas:
To permit explicit reference to XML schemas in these cases, Oracle XML DB supports a 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-name>/<schemaURL-minus-protocol>
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
Registration of an XML schema is non transactional and auto committed as with other SQL DDL operations, as follows:
Since XML schema registration potentially involves creating object types and tables, error recovery involves dropping any such created types and tables. Thus, the entire XML schema registration is guaranteed to be atomic. That is, either it succeeds or the database is restored to the state before the start of registration.
Java beans can be optionally generated during XML schema registration and provide accessor and mutator methods for elements and attributes declared in the XML schema. Access to XML data stored in the database, using Java beans, offers better performance for manipulating the XML data when the XML schema is well known and mostly fixed by avoiding run-time name translation.
For example, the Java bean class corresponding to the XML schema PO.xsd
has the following accessor and mutator methods:
public class PurchaseOrder extends XMLTypeBean { public BigDecimal getPONum() { .... } public void setPONum(BigDecimal val) { .... } public String getCompany() { .... } public void setCompany(String val) { .... } .... }
Note: Java bean support in Oracle XML DB is only for XML schema-based XML documents. Non-schema-based XML documents can be manipulated using Oracle XML DB DOM API. See Chapter 9, "Java and Java Bean APIs for XMLType". |
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.
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.oracl e.com/xdb/T1" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xdb="http://xml ns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:sch emaLocation="http://xmlns.oracle.com/xdb http://xmlns.oracle.com/xdb/XDBSchema.x sd"> <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:SQLTyp e="NUMBER"/> <xsd:element name="ENAME" type="xsd:string" xdb:SQLName="ENAME" xdb:SQLTyp e="VARCHAR2"/> <xsd:element name="SALARY" type="xsd:double" xdb:SQLName="SALARY" xdb:SQLT ype="NUMBER"/> </xsd:sequence> </xsd:complexType> </xsd:schema>
Table 5-1 lists the XMLType
API's XML schema-related methods.
XMLType API Method | Description |
---|---|
isSchemaBased() |
Returns TRUE if the |
getNamespace() |
Returns the XML schema URL, name of root element, and the namespace for an XML schema-based |
setSchemaValidated() |
An |
XML schema documents are themselves stored in Oracle XML DB as XMLType
instances. XML schema-related XMLType
types and tables are created as part of the Oracle XML DB installation script, catxdbs.sql
.
The XML schema for XML schemas is called the root XML schema, XDBSchema.xsd
. XDBSchema.xsd
describes any valid XML schema document that can be registered by Oracle XML DB. You can access XDBSchema.xsd
through Oracle XML DB Repository at:
/sys/schemas/PUBLIC/xmlns.oracle.com/xdb/XDBSchema.xsd
XML Schema-based XMLType
structures are stored in one of the following ways:
STORE AS
clause of the CREATE TABLE
statement:
CREATE TABLE po_tab OF xmltype STORE AS CLOB ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder";
Design criteria for storing XML data are discussed inChapter 2, "Getting Started with Oracle XML DB" and Chapter 3, "Using Oracle XML DB".
Instead of using the STORE AS
clause, you can specify that the table and column be stored according to a mapping based on a particular XML schema. You can specify the URL for the XML schema used for the mapping.
Non-schema-based XML data can be stored in tables using CLOBs. However you do not gain benefits such as indexing, query-rewrite, and so on.
Document Object Model (DOM) fidelity is the concept of retaining the structure of a retrieved XML document, compared to the original XML document, for DOM traversals. DOM fidelity is needed to ensure the accuracy and integrity of XML documents stored in Oracle XML DB.
All elements and attributes declared in the XML schema are mapped to separate attributes in the corresponding SQL object type. However, some pieces of information in XML instance documents are not represented directly by these element or attributes, such as:
To ensure the integrity and accuracy of this data, for example, when regenerating XML documents stored in the database, Oracle XML DB uses a data integrity mechanism called DOM fidelity.
DOM fidelity refers to how identical the returned XML documents are compared to the original XML documents, particularly for purposes of DOM traversals.
To guarantee that DOM fidelity is maintained and that the returned XML documents are identical to the original XML document for DOM traversals, Oracle XML DB adds a system binary attribute, SYS_XDBPD$
, to each created object type.
This attribute stores all pieces of information that cannot be stored in any of the other attributes, thereby ensuring the DOM fidelity of all XML documents stored in Oracle XML DB. Examples of such pieces of information include: ordering information, comments, processing instructions, namespace prefices, and so on.
This is mapped to a Positional Descriptor (PD) column.
Note: In general,it is not a good idea to set this information because the extra pieces of information, such as, comments, processing instructions, and so on, could be lost if there is no PD column. |
If DOM fidelity is not required, you can suppress SYS_XDBPD$
in the XML schema definition by setting the attribute, maintainDOM=FALSE
.
Oracle XML DB creates XML schema-based XMLType
tables and columns by referencing:
Figure 5-1 shows the syntax for creating an XMLType
table:
CREATE TABLE [schema.] table OF XMLTYPE [XMLTYPE XMLType_storage] [XMLSchema_spec];
A subset of the XPointer notation, shown in the following example, can also be used to provide a single URL containing the XML schema location and element name.
This example creates the XMLType
table po_tab
using the XML schema at the given URL:
CREATE TABLE po_tab OF XMLTYPE XMLSCHEMA "http://www.oracle.com/PO.xsd" ELEMENT "PurchaseOrder";
An equivalent definition is:
CREATE TABLE po_tab OF XMLTYPE ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder";
When an XML schema is registered, Oracle XML DB creates the appropriate SQL object types that enable structured storage of XML documents that conform to this XML schema. All SQL object types are created based on the current registered XML schema, by default.
or example, when PO.xsd is registered with Oracle XML DB, the following SQL types are created.
Note: The names of the types are generated names, and will not necessarily match |
CREATE TYPE "Itemxxx_T" as object ( part varchar2(1000), price number ); CREATE TYPE "Itemxxx_COLL" AS varray(1000) OF "Item_T"; CREATE TYPE "PurchaseOrderTypexxx_T" AS OBJECT ( ponum number, company varchar2(100), item Item_varray_COLL );
Note: The names of the object types and attributes in the preceding example can be system-generated.
If the |
To specify specific names of SQL objects generated include the attributes SQLName
and SQLType
in the XML schema definition prior to registering the XML schema.
SQLName
and SQLType
values, Oracle XML DB creates the SQL object types using these names.All annotations are in the form of attributes that can be specified within attribute and element declarations. These attributes belong to the Oracle XML DB namespace: http://xmlns.oracle.com/xdb
Table 5-2 lists Oracle XML DB attributes that you can specify in element and attribute declarations.
Information regarding the SQL mapping is stored in the XML schema document. The registration process generates the SQL types, as described in "Mapping of Types Using DBMS_XMLSCHEMA" and adds annotations to the XML schema document to store the mapping information. Annotations are in the form of new attributes.
The following XML schema definition shows how SQL mapping information is captured using SQLType
and SQLName
attributes:
declare doc varchar2(3000) := '<schema targetNamespace="http://www.oracle.com/PO.xsd" xmlns:po="http://www.oracle.com/PO.xsd" xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns="http://www.w3.org/2001/XMLSchema"> <complexType name="PurchaseOrderType"> <sequence> <element name="PONum" type="decimal" xdb:SQLName="PONUM" xdb:SQLType="NUMBER"/> <element name="Company" xdb:SQLName="COMPANY" xdb:SQLType="VARCHAR2"> <simpleType> <restriction base="string"> <maxLength value="100"/> </restriction> </simpleType> </element> <element name="Item" xdb:SQLName="ITEM" xdb:SQLType="ITEM_T" maxOccurs="1000"> <complexType> <sequence> <element name="Part" xdb:SQLName="PART" xdb:SQLType="VARCHAR2"> <simpleType> <restriction base="string"> <maxLength value="1000"/> </restriction> </simpleType> </element> <element name="Price" type="float" xdb:SQLName="PRICE" xdb:SQLType="NUMBER"/> </sequence> </complexType> </element> </sequence> </complexType> <element name="PurchaseOrder" type="po:PurchaseOrderType"/> </schema>'; begin dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc); end;
Figure 5-2 shows how Oracle XML DB creates XML schema-based XMLType
tables using an XML document and mapping specified in an XML schema. An XMLType
table is first created and depending on how the storage is specified in the XML schema, the XML document is mapped and stored either as a CLOB in one XMLType
column, or stored object-relationally and spread out across several columns in the table.
An XMLType
table is first created and depending on how the storage is specified in the XML schema, the XML document is mapped and stored either as a CLOB in one XMLType
column, or stored object-relationally and spread out across several columns in the table.
Use DBMS_XMLSCHEMA
to set the mapping of type information for attributes and elements.
An attribute declaration can have its type specified in terms of one of the following:
simpleType
, declared within this XML schema or in an external XML schemaref=".."
), declared within this XML schema or in an external XML schemasimpleType
In all cases, the SQL type and associated information (length and precision) as well as the memory mapping information, are derived from the simpleType
on which the attribute is based.
You can explicitly specify an SQLType
value in the input XML schema document. In this case, your specified type is validated. This allows for the following specific forms of overrides:
An element declaration can specify its type in terms of one of the following:
complexType
, specified within this XML schema document or in an external XML schema.ref="..."
), which could itself be within this XML schema document or in an external XML schema.complexType
.An element based on a complexType
is, by default, mapped to an object type containing attributes corresponding to each of the sub-elements and attributes. However, you can override this mapping by explicitly specifying a value for SQLType
attribute in the input XML schema. The following values for SQLType
are permitted in this case:
These represent storage of the XML in a text or unexploded form in the database. The following special cases are handled:
complexTypes
used to declare elements and elements declared within the omplexType
), the SQLInline
attribute is forced to be "false" and the correct SQL mapping is set to REF XMLTYPE
.maxOccurs > 1
, a VARRAY type may need to be created.
SQLInline="false"
, a default table needs to be created. It is added to the table creation context. The name of the default table has either been specified by the user, or derived by mangling the element name.This section describes how XML schema definitions map XML schema simpleType
to SQL object types. Figure 5-3 shows an example of this.
Table 5-5 through Table 5-8 list the default mapping of XML schema simpleType
to SQL, as specified in the XML schema definition. For example:
If the XML schema specifies the datatype to be string with a maxLength
value of less than 4000, it is mapped to a VARCHAR2 attribute of the specified length. However, if maxLength
is not specified in the XML schema, it can only be mapped to a LOB. This is sub-optimal when most of the string values are small and only a small fraction of them are large enough to need a LOB.
Using XML schema, a complexType
is mapped to an SQL object type as follows:
complexType
are mapped to object attributes. The simpleType
defining the XML attribute determines the SQL datatype of the corresponding attribute.complexType
are also mapped to object attributes. The datatype of the object attribute is determined by the simpleType
or complexType
defining the XML element.If the XML element is declared with attribute maxOccurs
> 1, it is mapped to a collection
attribute in SQL. The collection
could be a VARRAY (default) or nested table if the maintainOrder
attribute is set to false. Further, the default storage of the VARRAY is in Ordered Collections in Tables (OCTs) instead of LOBs. You can choose LOB storage by setting the storeAsLob
attribute to true.
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 5-4 illustrates the mapping of a complexType
to SQL for out-of-line storage.
In this example element Addr
's attribute, xdb:SQLInLine
, 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
. 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 = "Employee" xdb:SQLType="OBJ_T2"> <sequence> <element name = "Name" type = "string"/> <element name = "Age" type = "decimal"/> <element name = "Addr" xdb:SQLInline = "false"> <complexType xdb:SQLType="OBJ_T1"> <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_T1 AS OBJECT ( SYS_XDBPD$ XDB.XDB$RAW_LIST_T, Street VARCHAR2(4000), City VARCHAR2(4000) ); CREATE TYPE OBJ_T2 AS OBJECT ( SYS_XDBPD$ XDB.XDB$RAW_LIST_T, Name VARCHAR2(4000), Age NUMBER, Addr REF XMLType );
You can specify the SQLType
for a complex element as a Character Large Object (CLOB) or Binary Large Object (BLOB) as shown in Figure 5-5. 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/recomposition overheads.
In the following example, the XML schema specifies that the XML fragment's element Addr
is using 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 );
In XML schema, complexTypes
are declared based on complexContent
and simpleContent
.
simpleContent
is declared as an extension of simpleType
.complexContent
is declared as one of the following:
For complexType
, Oracle XML DB handles inheritance in the XML schema as follows:
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;
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;
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;
Since 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's type.
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.
namespace
attribute can be used to restrict the contents so that they belong to a specified namespace.processContents
attribute within the any
element declaration, indicates the level of validation required for the contents matching the any
declaration.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;
It 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) );
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.
XML schemas permit cycling between definitions of complexTypes
. Figure 5-6 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 2:
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
(references) 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;
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;
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 5-7 illustrates schematically how a complexTypes
can reference or cycle itself.
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:
|
To specify storage, the underlying columns can be referenced in the XMLType
storage clauses using either Object or XML notation:
XMLDATA.<attr1>.<attr2>....
For example:
CREATE TABLE MyPOs OF XMLTYPE ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder" lob (xmldata.lobattr) STORE AS (tablespace ...);
extractValue(xmltypecol, '/attr1/attr2')
For example:
CREATE TABLE MyPOs OF XMLTYPE ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder" lob (ExtractValue(MyPOs, '/lobattr')) STORE AS (tablespace ...);
As shown in the preceding examples, columns underlying an XMLType
column can be referenced using either an object or XML notation in the CREATE TABLE statements. The same is true in CREATE INDEX statements:
CREATE INDEX ponum_idx ON MyPOs (xmldata.ponum); CREATE INDEX ponum_idx ON MyPOs p (ExtractValue(p, '/ponum');
Constraints can also be specified for underlying XMLType
columns, using either the object or XML notation:
CREATE TABLE MyPOs OF XMLTYPE ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder" (unique(xmldata.ponum));
CREATE TABLE MyPOs P OF XMLTYPE ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder"(unique(ExtractValue(p,'/ponum') );
New instances can be inserted into an XMLType
columns as follows:
INSERT INTO MyPOs VALUES (xmltype.createxml('<PurchaseOrder>.....</PurchaseOrder>'));
When the XMLType
is stored in structured storage (object-relationally) using an XML schema and queries using XPath are used, they are rewritten to go directly to the underlying object-relational columns. This enables the use of B*Tree or other indexes, if present on the column, to be used in query evaluation by the Optimizer. This query rewrite mechanism is used for XPaths in SQL functions such as existsNode()
, extract()
, extractValue()
, and updateXML()
. This enables the XPath to be evaluated against the XML document without having to ever construct the XML document in memory.
For example a query such as:
SELECT VALUE(p) FROM MyPOs p WHERE extractValue(value(p),'/PurchaseOrder/Company') = 'Oracle';
is trying to get the value of the Company
element and compare it with the literal 'Oracle
'. Since the MyPOs
table has been created with XML schema-based structured storage, the extractValue
operator gets rewritten to the underlying relational column that stores the company information for the purchaseorder
.
Thus the preceding query is rewritten to the following:
SELECT VALUE(p) FROM MyPOs p WHERE p.xmldata.company = 'Oracle';
If there was a regular index created on the Company
column, such as:
CREATE INDEX company_index ON MyPos e (extractvalue(value(e),'/PurchaseOrder/Company'));
then the preceding query would use the index for its evaluation.
Query rewrite happens for the following SQL functions:
The rewrite happens when these SQL functions are present in any expression in a query, DML, or DDL statement. For example, you can use extractValue()
to create indexes on the underlying relational columns.
This example gets the existing purchase orders:
SELECT EXTRACTVALUE(value(x),'/PurchaseOrder/Company') FROM MYPOs x WHERE EXISTSNODE(value(x),'/PurchaseOrder/Item[1]/Part') = 1;
Here are some examples of statements that get rewritten to use underlying columns:
This example deletes all purchaseorders
where the Company
is not Oracle
:
DELETE FROM MYPOs x WHERE EXTRACTVALUE(value(x),'/PurchaseOrder/Company') = 'Oracle Corp';
This example creates an index on the Company
column, since this is stored object relationally and the query rewrite happens, a regular index on the underlying relational column will be created:
CREATE INDEX company_index ON MyPos e (extractvalue(value(e),'/PurchaseOrder/Company'));
In this case, if the rewrite of the SQL functions results in a simple relational column, then the index is turned into a B*Tree or a domain index on the column, rather than a function-based index.
XPath involving simple expressions with no wild cards or descendant axes get rewritten. The XPath may select an element or an attribute node. Predicates are supported and get rewritten into SQL predicates.
Table 5-10 lists the kinds of XPath expressions that can be translated into underlying SQL queries in this release.
The following XPath constructs do not get rewritten:
The following XML schema constructs are not supported. This means that if the XPath expression includes nodes with the following XML schema construct then the entire expression will not get rewritten:
any
content. When nodes contain any
content, then the expression cannot be rewritten, except when the any
targets a namespace other than the namespace specified in the XPath. any
attributes are handled in a similar way.complexTypes
where the child is not a member of the declared complexType
.
For example, consider the case where we have a address
complexType
which has a street
element. We can have a derived type called shipAddr
which contains shipmentNumber
element. If the PurchaseOrder
had an address element of type address, then an XPath like "/PurchaseOrder/address/street"
would get rewritten whereas "/PurchaseOrder/address/shipmentNumber"
would not.
The following sections use the same purchaseorder
XML schema explained earlier in the chapter to explain how the functions get rewritten.
Consider the following purchaseorder
XML schema:
declare doc varchar2(1000) := '<schema targetNamespace="http://www.oracle.com/PO.xsd" xmlns:po="http://www.oracle.com/PO.xsd" xmlns="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified"> <complexType name="PurchaseOrderType"> <sequence> <element name="PONum" type="decimal"/> <element name="Company"> <simpleType> <restriction base="string"> <maxLength value="100"/> </restriction> </simpleType> </element> <element name="Item" maxOccurs="1000"> <complexType> <sequence> <element name="Part"> <simpleType> <restriction base="string"> <maxLength value="1000"/> </restriction> </simpleType> </element> <element name="Price" type="float"/> </sequence> </complexType> </element> </sequence> </complexType> <element name="PurchaseOrder" type="po:PurchaseOrderType"/> </schema>'; begin dbms_xmlschema.registerSchema('http://www.oracle.com/PO.xsd', doc); end; -- A table is created conforming to this schema CREATE TABLE MyPOs OF XMLTYPE ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder"; -- The inserted XML document is partially validated against the schema before -- it is inserted. insert into MyPos values (xmltype('<PurchaseOrder xmlns="http://www.oracle.com/PO.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.oracle.com/PO.xsd http://www.oracle.com/PO.xsd"> <PONum>1001</PONum> <Company>Oracle Corp</Company> <Item> <Part>9i Doc Set</Part> <Price>2550</Price> </Item> <Item> <Part>8i Doc Set</Part> <Price>350</Price> </Item> </PurchaseOrder>'));
Since the XML schema did not specify anything about maintaining the ordering, the default is to maintain the ordering and DOM fidelity. Hence the types have SYS_XDBPD$
attribute to store the extra information needed to maintain the ordering of nodes and to capture extra items such as comments, processing instructions and so on.
The SYS_XDBPD$
attribute also maintains the existential information for the elements (that is, whether the element was present or not in the input document). This is needed for elements with scalar content, since they map to simple relational columns. In this case, both empty and missing scalar elements map to NULL values in the column and only the SYS_XDBPD$
attribute can help distinguish the two cases. The query rewrite mechanism takes into account the presence or absence of the SYS_XDBPD$
attribute and rewrites queries appropriately.
Assuming that this XML schema is registered with the schema URL: http://www.oracle.com/PO.xsd
you can create the po_tab
table with this schema as follows:
CREATE TABLE po_tab OF XMLTYPE
XMLSCHEMA "http://www.oracle.com/PO.xsd" ELEMENT "PurchaseOrder";
Now this table has a hidden XMLData
column of type "PurchaseOrder_T
" that stores the actual data.
XPath expression mapping of types and topics are described in the following sections:
A rewrite for a simple XPath involves accessing the attribute corresponding to the XPath expression. Table 5-11 lists the XPath map:
An XPath expression can contain a text()
operator which
maps to the scalar content in the XML document. When rewriting, this maps
directly to the underlying relational columns.
For example, the XPath expression "/PurchaseOrder/PONum/text()
" maps to the SQL column XMLData
."PONum
" directly.
A NULL value in the PONum
column implies that the text value is not available, either because the text
node was not present in the input document or the element itself was missing. This is more efficient than accessing the scalar element, since we do not need to check for the existance of the element in the SYS_XBDPD$
attribute.
For example, the XPath "/PurchaseOrder/PONum
" also maps to the SQL attribute XMLData."PONum"
,
However,in this case, query reqrite also has to check for the existance of the element itself, using the SYS_XDBPD$
in the XMLData
column.
Predicates are mapped to SQL predicate expressions.
For example the predicate in the XPath expression:
/PurchaseOrder[PONum=1001 and Company = "Oracle Corp"]
maps to the SQL predicate:
( XMLData."PONum" = 20 and XMLData."Company" = "Oracle Corp")
For example, the following query is rewritten to the structured (object-relational) equivalent, and will not require Functional evaluation of the XPath.
select extract(value(p),'/PurchaseOrder/Item').getClobval() from mypos p where existsNode(value(p),'/PurchaseOrder[PONum=1001 and Company = "Oracle Corp"]') =1;
XPath expressions may involve relational operators with collection expressions. In Xpath 1.0, conditions involving collections are existential checks. In other words, even if one member of the collection satisfies the condition, the expression is true.
For example the collection predicate in the XPath:
/PurchaseOrder[Items/Price > 200] -- maps to a SQL collection expression: EXISTS ( SELECT null FROM TABLE (XMLDATA."Item") x WHERE x."Price" > 200 )
For example, the following query is rewritten to the structured equivalent.
select extract(value(p),'/PurchaseOrder/Item').getClobval() from mypos p where existsNode(value(p),'/PurchaseOrder[Item/Price > 400]') = 1;
More complicated rewrites occur when you have a collection <condition>
collection. In this case, if at least one combination of nodes from these two collection arguments satisfy the condition, then the predicate is deemed to be satisfied.
For example, consider a fictitious XPath which checks to see if a Purchaseorder
has Items such that the price of an item is the same as some part number:
/PurchaseOrder[Items/Price = Items/Part] -- maps to a SQL collection expression: EXISTS ( SELECT null FROM TABLE (XMLDATA."Item") x WHERE EXISTS ( SELECT null FROM TABLE(XMLDATA."Item") y WHERE y."Part" = x."Price"))
For example, the following query is rewritten to the structured equivalent:
select extract(value(p),'/PurchaseOrder/Item').getClobval() from mypos p where existsNode(value(p),'/PurchaseOrder[Item/Price = Item/Part]') = 1;
Most of the rewrite preserves the original document ordering. However, since the SQL system does not guarantee ordering on the results of subqueries, when selecting elements from a collection using the extract()
function, the resultant nodes may not be in document order.
For example:
SELECT extract(value(p),'/PurchaseOrder/Item[Price>2100]/Part') FROM mypos p;
is rewritten to use subqueries as shown in the following:
SELECT (SELECT XMLAgg( XMLForest(x."Part" AS "Part")) FROM TABLE (XMLData."Item") x WHERE x."Price" > 2100 ) FROM po_tab p;
Though in most cases, the result of the aggregation would be in the same order as the collection elements, this is not guaranteed and hence the results may not be in document order. This is a limitation that may be fixed in future releases.
An XPath expression can also access a particular index of a collection For example, "/PurchaseOrder/Item[1]/Part"
is rewritten to extract out the first Item of the collection and then access the Part attribute within that.
If the collection has been stored as a VARRAY, then this operation retrieves the nodes in the same order as present in the original document. If the mapping of the collection is to a nested table, then the order is undetermined. If the VARRAY is stored as an Ordered Collection Table (OCT) , (the default for the tables created by the schema compiler, if storeVarrayAsTable="true"
is set), then this collection index access is optimized to use the IOT index present on the VARRAY.
An XPath expression can contain references to nodes that cannot be present in the input document. Such parts of the expression map to SQL NULLs during rewrite. For example the XPath expression: "/PurchaseOrder/ShipAddress"
cannot be satisified by any instance document conforming to the PO.xsd
XML schema, since the XML schema does not allow for ShipAddress
elements under PurchaseOrder
. Hence this expression would map to a SQL NULL literal.
Namespaces are handled in the same way as the function-based evaluation. For schema based documents, if the function (like existsNode()
or extract()
) does not specify any namespace parameter, then the target namespace of the schema is used as the default namespace for the XPath expression.
For example, the XPath expression /PurchaseOrder/PONum
is treated as /a:PurchaseOrder/a:PONum
with xmlns:a="http://www.oracle.com/PO.xsd
" if the SQL function does not explicitly specify the namespace prefix and mapping. In other words:
SELECT * FROM po_tab p WHERE EXISTSNODE(value(p), '/PurchaseOrder/PONum') = 1;
is equivalent to the query:
SELECT * FROM po_tab p WHERE EXISTSNODE(value(p),'/PurchaseOrder/PONum', 'xmlns="http://www.oracle.com/PO.xsd') = 1;
When performing query rewrite, the namespace for a particular element is matched with that of the XML schema definition. If the XML schema contains elementFormDefault="qualified"
then each node in the XPath expression must target a namespace (this can be done using a default namespace specification or by prefixing each node with a namespace prefix).
If the elementFormDefault
is unqualified (which is the default), then only the node that defines the namespace should contain a prefix. For instance if the PO.xsd
had the element form to be unqualified, then the existsNode()
function should be rewritten as:
EXISTSNODE(value(p),'/a:PurchaseOrder/PONum', 'xmlns:a="http://www.oracle.com/PO.xsd") = 1;
The default date formats are different for XML schema and SQL. Consequently, when rewriting XPath expressions involving comparsions with dates, you need to use XML formats.
For example, the expression:
[@PurchaseDate="2002-02-01"]
cannot be simply rewritten as:
XMLData."PurchaseDate" = "2002-02-01"
since the default date format for SQL is not YYYY-MM-DD
. Hence during query rewrite, the XML format string is added to convert text values into date datatypes correctly. Thus the preceding predicate would be rewritten as:
XMLData."PurchaseDate" = TO_DATE("2002-02-01","SYYYY-MM-DD");
Similarly when converting these columns to text values (needed for extract()
, and so on), XML format strings are added to convert them to the same date format as XML.
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 non-NULL in the case where the XPath targets a text()
node or a non-scalar
node and checks for the existance of the element using the SYS_XDBPD$
otherwise. If the SYS_XDBPD$
attribute is absent, then the existance of a scalar
node is determined by the NULL information for the scalar column.
Table 5-12 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.
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-existant scalar elements. Hence you do not need to check for the node existance using the SYS_XDBPD$
attribute. Table 5-13 shows the mapping of existsNode()
in the absence of the SYS_XDBPD$ attribute.
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 5-14 shows the mapping of various XPath expressions in the case of extractValue(). If an XPath expression targets an element, 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
.
For example, an 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;
Since it gets rewritten to simple scalar columns, indexes if any, on the PONum
attribute may 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.
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, since unlike a function-based index, the same index can now satisfy queries which target the column such as:
EXISTSNODE(value(x),'/PurchaseOrder[PONum=1001]') = 1;
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 5-15 shows the mapping of various XPath in the case of extract()
when document order is preserved (that is, when SYS_XDBPD$
exists and maintainDOM="true"
in the schema document).
Note: The examples show |
Using the mapping in Table 5-15, a query that extracts the PONum element where the purchaseorder 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-existant scalar elements. Hence you do not need to check for the node existance using the SYS_XDBPD$
attribute. Table 5-16 shows the mapping of existsNode()
in the absence of the SYS_XDBPD$
attribute.
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 object relationally, 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.
updateXML()
must satisfy the following conditions for it to use the optimization:
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),...);
XMLType
column must have been stored object relationally using Oracle XML DB's XML schema mapping.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:
UPDATE po_tab p SET value(p) = updatexml(value(p),'/PurchaseOrder/@PurchaseDate','2002-01-02', '/PurchaseOrder/PONum/text()', 2200);
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, gDATE, and so on, have different format in XML schema and SQL. In such cases, if the updateXML()
has a string value for these columns, 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 and not the SQL DATE format.
As part of XML schema registration, you can also create default tables. Default tables are most useful when XML instance documents conforming to this XML schema are inserted through APIs that do not have any table specification, such as with FTP or HTTP. In such cases, the XML instance is inserted into the default table.
If you have given a value for attribute defaultTable
, the XMLType
table is created with that name. Otherwise it gets created with an internally generated name.
Further, any text specified using the tableProps
and columnProps
attribute are appended to the generated CREATE TABLE
statement.
Arrays in XML schemas (elements with maxOccurs > 1
) are usually stored in VARRAYs, which can be stored either in a Large Object (LOB) or in a separate store table, similar to a nested table.
This allows the elements of a VARRAY to reside in a separate table based on an IOT. The primary key of the table is (NESTED_TABLE_ID
, ARRAY_INDEX
). NESTED_TABLE_ID
is used to link the element with their containing parents while the ARRAY_INDEX
column keeps track of the position of the element within the collection.
There are two ways to specify an OCT storage:
"storeVarrayAsTable"
. By default this is "false" and VARRAYs are stored in a LOB. If this is set to "true"
, all VARRAYs, all elements that have maxOccurs > 1
, will be stored as OCTs.tableProps
" attribute. The exact SQL needed to create an OCT can be used as part of the tableProps
attribute:
"VARRAY xmldata.<array> STORE AS TABLE <myTable> ((PRIMARY KEY (NESTED_ TABLE_ID, ARRAY_INDEX)) ORGANIZATION INDEX)"
The advantages of using OCTs for VARRAY storage include faster access to elements and better queryability. Indexes can be created on attributes of the element and these can aid in better execution for query rewrite.
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:
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 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, and so on:
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 which have a cyclic dependency on each other, you must use the FORCE
parameter in DBMS_XMLSCHEMA.registerSchema
as follows:
dbms_xmlschema.registerSchema("s1.xsd", "<schema ...", ..., force => true)
At this point, s1.xsd
is invalid and cannot be used.
dbms_xmlschema.registerSchema("s2.xsd", "<schema ..", ..., force => true)
The second operation automatically compiles s1.xsd
and makes both XML schemas valid.
See Figure 5-8. The preceding example is illustrated in the lower half of the figure.
|
![]() Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|