| Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2) Part Number A96620-02 |
|
|
View PDF |
| Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2) Part Number A96620-02 |
|
|
View PDF |
This chapter introduces XML Schema and explains how XML schema are used in Oracle XML DB applications. It describes how to register your XML schema and create storage structures for storing schema-based XML. 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 occurrences, 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.
In addition to supporting XML schema that provide a structured mapping to object- relational storage, Oracle XML DB also supports DTD specifications in XML instance documents. Though DTDs are not used to derive the mapping, XML processors can still access and interpret the DTDs.
When an XML instance document has an inline DTD definition, it is used during document parsing. Any DTD validations and entity declaration handling is done at this point. However, once parsed, the entity references are replaced with actual values and the original entity reference is lost.
Oracle XML DB also supports external DTD definitions if they are stored in the Repository. Applications needing to process an XML document containing an external DTD definition such as "/public/flights.dtd", must first ensure that the DTD document is stored in Oracle XML DB at the path "/public/flights.xsd".
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('<po:PurchaseOrder xmlns:po="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> </po: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 writable 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 and default tables as part of a previous call to register XML 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 XML 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.
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 positional descriptor 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 prefixes, and so on. This is mapped to a Positional Descriptor (PD) column.
|
Note: The PD attribute is mainly intended for Oracle internal use only. You should never directly access or manipulate this 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 schemasimpleTypeIn 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 complexType), 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 existence 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 rewrite also has to check for the existence 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 satisfied 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 comparisons 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 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 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-existent scalar elements. Hence you do not need to check for the node existence 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-existent scalar elements. Hence you do not need to check for the node existence 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.
Consider the following simple XML document that is blown up to 3.6 Megabytes:
<?xml version="1.0"?> <PurchaseOrder xmlns="http://www.vector.com/po.xsd" xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.vector.com/po.xsd http://www.vector.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> <Item> <Part>7i Doc Set</Part> <Price>50</Price> </Item> </PurchaseOrder>
This document is stored in an XMLTYPE table object-relationally. The XML schema was not annotated.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <xsd:complexType name="PurchaseOrderType"> <xsd:sequence> <xsd:element name="PONum" type="xsd:decimal"/> <xsd:element name="Company"> <xsd:simpleType> <xsd:restriction base="xsd:string"> <xsd:maxLength value="100"/> </xsd:restriction> </xsd:simpleType> </xsd:element> <xsd:element name="Item" maxOccurs="2147483647"> <xsd:complexType> <xsd:sequence> ... Name Null? Type ----------------------------------------- -------- ------------------- FILENAME NOT NULL VARCHAR2(20) CONTENT NOT NULL XMLTYPE(XMLSchema "http://www.vector.com/po.xsd" Element "PurchaseOrder") STORAGE Object-relational TYPE "PurchaseOrderType1627_T"
The statement:
SQL> select existsnode(srp.content,'/PurchaseOrder/Item[Part="7i Doc Set"]') into :i from xmltable srp where filename='po6.xml';
takes about 6 seconds on my laptop. When I use a bind variable such as:
SQL> var xpath varchar2(50) SQL> exec :xpath:='/PurchaseOrder/Item[Part="7i Doc Set"]' PL/SQL procedure successfully completed. SQL> select existsnode(srp.content,:xpath) into :i from xmltable srp where filename='po6.xml';
I wait ...The statement hangs; the CPU is busy by 100% and the memory consumption is high:
Answer: If you use bind variables, Oracle does not rewrite the query, hence you are seeing a full function-based XPath versus a relational rewrite.
Question 2: We need bind variables for SQL sharing. What happens if you set the CURSOR_SHARING to FORCE?
Answer 2: Basically, query rewrite means that Oracle is changing the input XPath expression into some underlying columns. This means that for a given XPath, there is a particular set of columns/tables,... that will be referenced underneath. This has to be a compile time operation, since the shared cursor has to know exactly which tables and columns, .... it references. This cannot change with each row or instantiation of the cursor.
So if the XPath expression itself is a bind variable, Oracle cannot do any rewrite, since each instantiation of the cursor can have totally different XPath. This is akin to the user binding the name of the table/column in a SQL query. For example, SELECT * FROM table(:1).
When CURSOR_SHARING is set to FORCE, by default all string constants including XPath become a bind variable. At that time when Oracle encounters extractvalue(),existsnode(),..., Oracle look at the XPath bind variables to check if they are really constants. If so Oracle uses them and rewriteS the query.
Hence there the big difference inwhere the bind variable is used.
I have a question about query rewrite with XML schema-Based object-relational storage. How can I check that query rewrite is working correctly? Should I use an SQL trace, events, and so on?
Answer: You can check query rewrites in two ways:
Event 19027 - turns off query rewrite - no level information needed Event 19021 - XML operations - general event. Use this with different levels to get different behavior.. Level 0x1 - Turn off all functional evaluation.. Level 0x2 - Turn off functional evaluation of EXTRACT Level 0x4 - Turn off functional evaluation of EXISTSNODE Level 0x8 - Turn off functional evaluation of TRANSFORM Level 0x10 - Turn off functional evaluation of EXTRACTVALUE Level 0x20 - Turn off functional evaluation of UPDATEXML
Using the second event 19021, you can selectively, raise errors, if the functional evaluation of these operators are chosen. For example:
ALTER SESSION SET EVENTS '19021 trace name context forever, level 1';
would turn off the functional evaluation of ALL XML operators listed above. Hence when you fire a query such as:
SELECT extract(value(x),'/purchaseorder/reference') FROM purchaseorder_xml_tab
if the query rewrite does not happen, then the extract() raises an ORA-19022 XPath functions are disabled error;
Question 2: According to your suggestions,I used event 19021. Here is the test case I used:
1--set event SQL> alter session set events '19021 trace name context forever, level 2'; Session altered. 2--extract function used
I tried this with both XML schema-based and non-schema-based.
For XML schema-based object-relational storage:
SQL> SELECT value(x).extract('/a:PO/Company', 2 'xmlns:a="http://www.oracle.com/PO"') 3 FROM po_tab x;
But I get the following error:
ERROR: ORA-19022: XML XPath functions are disabled ORA-06512: at "SYS.XMLTYPE", line 0 ORA-06512: at line 1
For non-schema-based CLOB storage:
SQL> SELECT extract(value(p),'/PO/PODATE') 2 FROM po_tab p;
results in the following error:
ERROR: ORA-19022: XML XPath functions are disabled
This result indicates that query rewrite does not occur. Are there other ways to check query rewrite?
Answer 2: You are doing the right thing. Setting event 19021 turns off functional evaluation, so that all XMLType functions will be turned off, and if query rewrite does not happen, you will get error ORA-19022 (XML XPath functions are disabled).
The reason the second example (non-schema based) is not working is that query rewrite can happen only for non-schema-based (NSB) XMLType views (XVs), defined over objects. It does not happen for non-schema-based XMLType tables, since the storage is CLOB-based.
The reason the first example (schema-based) is not working is probably the namespace parameter.
Query rewrite does not currently function for extract() or existsNode() XMLType methods. You can however use the operator equivalents instead of the XMLType methods. For example, use extract() operator instead of xmltype.extract() method.
I ran the demo script that orchestrates running all the other scripts. It creates an index like this:
create index director_name on movies( extractValue(movieDoc,'/Movie/Director/Last') );
But when I try an Explain Plan on this query:
SELECT extractValue(movieDoc,'/Movie/@Title') FROM movies WHERE extractValue(movieDoc,'/Movie/Director/Last') = 'Minghella'
It does not appear to use my index. Here is the EXPLAIN plan from the JDeveloper9i Explain Plan GUI:
SELECT STATEMENT - Filter - Table Access (FULL) SCOTT.MOVIES - Collection Iterator (PICKLER FETCH)
Is this because I do not have enough movies in my table, so the Optimizer decides that a full table scan is fastest? I also tried:
SELECT /*+ INDEX(movies director_name) */ extractValue(movieDoc,'/Movie/@Title') FROMmovies WHERE extractValue(movieDoc,'/Movie/Director/Last') = 'Minghella'
but it still does a full table scan of MOVIES.
Answer: When you create a non-schema-based index on XMLType, the indexes end up as function-based indexes. You can check user_functional_indexes. For a function-based index, the string must match exactly and you need to use ALTER SESSION as follows:
ALTER SESSION SET query_rewrite_enabled=true ALTER SESSION SET query_Rewrite_integrity=trusted
for it to detect the indexes.
Answer: If you have an element based on a global complexType, the SQLType (and SQLSchema) attributes should be specified for the complexType declaration. In addition you can (optionally) include the same SQLType and SQLSchema attributes within the element declaration.
The reason is that if you do not specify the SQLType for the global complexType, XML DB creates an SQLType with an internally generated name. The elements that reference this global type cannot then have a different value for SQLType. In other words, this is fine:
<xsd:complexType name="PURCHASEORDERLINEITEM_TYPEType"> <xsd:sequence> <xsd:element name="@LineNo" type="xsd:double" xdb:SQLName="@LineNo" xdb:SQLType="NUMBER"/> <xsd:element name="Decription" type="xsd:string"xdb:SQLName="Decription" xdb:SQLType="VARCHAR2"/> <xsd:element name="Part" type="PURCHASEORDERPART_TYPEType"xdb:SQLName="Part" /> </xsd:sequence> </xsd:complexType> <xsd:complexType name="PURCHASEORDERPART_TYPEType" xdb:SQLSchema="XMLUSER" xdb:SQLType="PURCHASEORDERPART_TYPE"> <xsd:sequence> <xsd:element name="@Id" type="xsd:string" xdb:SQLName="@Id"xdb:SQLType="VARCHAR2"/> <xsd:element name="@Quantity" type="xsd:double"xdb:SQLName="@Quantity" xdb:SQLType="NUMBER"/> <xsd:element name="@cost" type="xsd:double" xdb:SQLName="@cost"xdb:SQLType="NUMBER"/> </xsd:sequence> </xsd:complexType>
The following is also okay:
<xsd:complexType name="PURCHASEORDERLINEITEM_TYPEType"> <xsd:sequence> <xsd:element name="@LineNo" type="xsd:double" xdb:SQLName="@LineNo" xdb:SQLType="NUMBER"/> <xsd:element name="Decription" type="xsd:string"xdb:SQLName="Decription" xdb:SQLType="VARCHAR2"/> <xsd:element name="Part" type="PURCHASEORDERPART_TYPEType"xdb:SQLName="Part" xdb:SQLSchema="XMLUSER" xdb:SQLType="PURCHASEORDERPART_TYPE" /> </xsd:sequence> </xsd:complexType>
Given this table definition
SQL> describe "rechnung"; Name Null? Type ------------------------ ----- --------------- ID NOT NULL NUMBER(10)
rechnung
SYS.XMLTYPE(XMLSchema "http://cczarski.de.oracle.com/Rec hnung/Test001.xsd" Element "rechnung") STORAGE Object-relational TYPE "RECHNUNG_T" DATUM DATE
And this schema
<?xml version="1.0" encoding="iso-8859-1"?> <xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" targetNamespace="http://cczarski.de.oracle.com/Rechnung/Test001.xsd" xmlns:xdb="http://xmlns.oracle.com/xdb" xmlns:rechn="http://cczarski.de.oracle.com/Rechnung/Test001.xsd" elementFormDefault="qualified" version="1.0"> <!-- Zundchst wird der Kunde definiert --
Why does inserting this document:
<rechnung xmlns="http://.../Test001.xsd" xmlns:xsi=" http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://cczarski.d e.oracle.com/Rechnung/Test001.xsd"> <kunde> ...
Report
ERROR at line 2: ORA-19007: Schema and element do not match
Answer: xsi:schemaLocation takes two parameters: "NS SchemaURL"
try:
xsi:schemaLocation="http://cczarski.de.oracle.com/Rechnung/Test001.xsd http://cczarski.de.oracle.com/Rechnung/Test001.xsd">
I am having trouble pulling out the saved stylesheet from RESOURCE_VIEW with the following statement:
SELECT EXTRACT (rtab.res, 'r:Resource/r:Contents/node()/xsl:stylesheet', 'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd" '|| 'xmlns:xdb="http://xmlns.oracle.com/xdb" ' || 'xmlns:xsl="http://www.w3.org/1999/XSL/Transform"' ).getclobval() FROM resource_view rtab WHERE rtab.any_path = '/public/spec_proto/XDB_Stylesheet_Render_XML.xsl' /
Did I err in terms of the namespace?
Answer: Do you have the XSL schema registered? At this time, extracting from resource contents will not work unless the contents are from a registered XML schema.
Our code parses an XML file that is an instance of our own XML schema ApplicationStructure. It works fine, until we add xmlns="http://www.oracle.com/JHeadstart/ApplicationStructure" in the top-level tag. Then the call to selectSingleNode suddenly returns NULL. If we remove the xmlns attribute, selectSingleNode again returns the node we want. Our code:
import oracle.xml.parser.v2.*; ... private XMLDocument mXmldoc; mXmldoc = XMLLoader.getXMLDocument(mSource); // Select Service node XMLNode serviceNode = (XMLNode)mXmldoc.selectSingleNode("Service");
What are we doing wrong?
It seems that there is another constructor of selectSingleNode, which accepts a second parameter NSResolver. How must we use this and can we make it work with XML files with the xmlns attribute and without it?
We would like to be able to use the xmlns attribute, so that we can benefit from the JDeveloper9i Code Insight for our own registered XML Schema.
Answer: XPath has no syntax for searching on elements that are qualified with the default namespace. The XPath pattern "foo" always searches for the element <foo> with a null namespace. So that even though the default namespace *SYNTACTICALLY* allows you the convenience of writing:
<foo xmlns="urn:mynamespace"/>
To the XML Parser internally, this is an Element named: <{urn:mynamespace}:foo> and not just:
<foo>
So, moral of the story, is that to search for anything with a namespace URI, including default namespace, use the following:
Here's an example:
package test; import oracle.xml.parser.v2.*; import org.w3c.dom.*; import java.io.*; public class Demo { private static final String URI = "http://www.oracle.com/JHeadstart/ApplicationStructure"; private static final String TESTDOC = "<foo xmlns='"+URI+"'/>"; private static final NSResolver nsr = new MyNSResolver(); public static void main(String[] args) throws Throwable { System.out.println("Document to parse is"); System.out.println(TESTDOC); DOMParser dp = new DOMParser(); dp.parse( new StringReader(TESTDOC)); XMLDocument doc = dp.getDocument(); Node n = doc.selectSingleNode("xxx:foo", nsr); // Provide NSResolver! System.out.println( "Found " + ((n!=null) ? " it! " : " nothing")); } static class MyNSResolver implements NSResolver { public String resolveNamespacePrefix(String pref) { if (pref.equals("xxx")) return URI; else return null; } } }
This script runs successfully until I insert sample data into the table. It then fails with ORA-19007: Schema and element do not match. It should work:
---- testPo.sql set serverout on drop table po_tab1; declare urlvar varchar2(100); xsdfile varchar2(2000); begin urlvar := 'http://www.oracle.com/PO.xsd'; -- xmlns:po="http://www.oracle.com/PO.xsd"> xsdfile := '<schema xmlns="http://www.w3.org/2001/XMLSchema" targetNamespace="http://www.oracle.com/PO.xsd" xmlns:po="http://www.oracle.com/PO.xsd"> <complexType name="PurchaseOrderType"> <sequence> <element name="PONum" type="decimal"/> <element name="Company" type="string"/> <element name="Item" maxOccurs="1000"> <complexType> <sequence> <element name="Part" type="string"/> <element name="Price" type="decimal"/> </sequence> </complexType> </element> </sequence> <attribute name = "PurchaseDate" type = "date"/> </complexType> <element name="PurchaseOrder" type = "po:PurchaseOrderType"/> </schema>'; begin dbms_xmlschema.deleteschema(urlvar,dbms_xmlschema.delete_cascade_force); exception when others then null; end; dbms_xmlschema.registerschema(urlvar,xsdfile); end; / set heading off set pagesize 0 set long 10000 set maxdata 12000 set arraysize 1 select a.schema.getstringval() from user_xml_schemas a where a.schema_url = 'http://www.oracle.com/PO.xsd'; CREATE TABLE po_tab1 OF XMLTYPE ELEMENT "http://www.oracle.com/PO.xsd#PurchaseOrder"; insert into po_tab1 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" PurchaseDate="1967-08-13"> <PONum>1</PONum> <Company>The Business</Company> <Item> <Part>Part 1</Part> <Price>1000</Price> </Item> </PurchaseOrder>')); select * from po_tab1;
Answer: The schemaLocation attribute should be a pair of <namespace> and <schemaloc> values as follows:
xsi:schemaLocation="http://www.oracle.com/PO.xsd http://www.oracle.com/PO.xsd"
I am trying to register the XML schema for schemas with something like. I have downloaded XMLSchema.xsd, XMLSchema.dtd, datatypes.dtd and made them available at my local webserver after checking that XMLSchema.xsd can be validated:
begin dbms_xmlschema.registeruri( schemaURL => 'http://www.denmark.dk/MD/XMLSchema' , schemaDocUri => 'http://144.21.226.78/XMLSchema.xsd' , local => false ); end; / declare * ERROR at line 1: ORA-31011: XML parsing failed ORA-19202: Error occurred in XML processing LPX-00233: namespace prefixes starting with "xml" are reserved Error at line 70 ORA-06512: at "XDB.DBMS_XMLSCHEMA_INT", line 0 ORA-06512: at "XDB.DBMS_XMLSCHEMA", line 160 ORA-06512: at line 34
I need a table for storing all the XML schemas. The content of this table I want to be validated against schema for schemas as well as the built-in capability of searching through the object-relational structures afterwards in order to do analyses that are more complex. Is it possible to Register XML schema for schemas?
In the original XMLSchema.xsd no XML namespace is declared; hence I could not validate it in XMLSpy. The I added the xmlns:xml namespace declaration to validate success in XMLSpy, but I get an error.
Answer: It's illegal to have namespaces that start with "xml". Pick another namespace prefix such as, "foo" or "xsd" or "xs" or "x".
|
 Copyright © 2002 Oracle Corporation. All Rights Reserved. |
|