| Oracle® XML DB Developer's Guide 11g Release 1 (11.1) Part Number B28369-01 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 11g Release 1 (11.1) Part Number B28369-01 |
|
|
View PDF |
This chapter provides an overview of how to use Oracle XML DB. The examples here illustrate techniques for accessing and managing XML content in purchase orders. The format and data of XML purchase orders are well suited for Oracle XML DB storage and processing techniques because purchase orders are highly structured XML documents. However, the majority of techniques introduced here can also be used to manage other types of XML documents, such as those containing unstructured or semi-structured data. This chapter also further explains Oracle XML DB concepts introduced in Chapter 1, "Introduction to Oracle XML DB".
This chapter contains these topics:
Before the introduction of Oracle XML DB, there were two ways to store XML content in Oracle Database:
Use Oracle XML Developer's Kit (XDK) to parse the XML document outside Oracle Database, and store the extracted XML data as rows in one or more tables in the database.
Store the XML document in Oracle Database using a Character Large Object (CLOB), Binary Large Object (BLOB), Binary File (BFILE), or VARCHAR column.
In both cases, Oracle Database is unaware that it is managing XML content.
The introduction of Oracle XML DB and the XMLType data type provides new techniques that facilitate the persistence of XML content in the database. These techniques include the ability to store XML documents in an XMLType column or table, or in Oracle XML DB Repository. Storing XML as an XMLType column or table makes Oracle Database aware that the content is XML. This lets the database:
Perform XML-specific validations, operations, and optimizations on the XML content
Facilitate highly efficient processing of XML content by Oracle XML DB
Oracle9i release 1 (9.0.1) introduced a new data type, XMLType, to facilitate native handling of XML data in the database:
XMLType can represent an XML document in the database, so it is accessible in SQL.
XMLType has built-in methods that operate on XML content. For example, you can use XMLType methods to create, extract, and index XML data stored in Oracle Database.
XMLType functionality is also available through a set of Application Program Interfaces (APIs) provided in PL/SQL and Java.
XMLType can be used in PL/SQL stored procedures for parameters, return values, and variables.
With XMLType, SQL developers can leverage the power of the relational database while working in the context of XML. XML developers can leverage the power of XML standards while working in the context of a relational database.
XMLType can be used as the data type of columns in tables and views. XMLType variables can be used in PL/SQL stored procedures as parameters and return values. You can also use XMLType in SQL, PL/SQL, C, Java (through JDBC), and Oracle Data Provider for .NET (ODP.NET).
The XMLType API provides a number of useful methods that operate on XML content. For example, method extract() extracts one or more nodes from an XMLType instance. Many of these XMLType methods are also provided as SQL functions. For example, SQL function extract corresponds to XMLType method extract().
Oracle XML DB functionality is based on the Oracle XML Developer's Kit C implementations of the relevant XML standards such as XML Parser, XML DOM, and XML Schema Validator.
See Also:
"XMLType Storage Models" for the available XMLType storage options and their relative advantages
The XMLType data type and application programming interface (API) enable SQL operations on XML content and XML operations on SQL content:
Versatile API – XMLType has a versatile API for application development that includes built-in functions, indexing, and navigation support.
XMLType and SQL – You can use XMLType in SQL statements, combined with other data types. For example, you can query XMLType columns and join the result of the extraction with a relational column. Oracle Database determines an optimal way to run such queries.
Indexing – You can created several kinds of indexes to improve the performance of queries on XML data.
For structured storage of XMLType data, you can create B-tree indexes on the object-relational tables that underlie XMLType tables and columns.
For unstructured and binary XML storage of XMLType data, you can create an XMLIndex index, which specifically targets the XML structure of a document.
You can create function-based indexes on explicit XPath expressions. This applies to all XMLType storage models.
You can index the textual content of XML data with an Oracle Text CONTEXT index, for use in full-text search. This applies to all XMLType storage models.
Use XMLType whenever you want to use the database as a persistent storage of XML data. XMLType features include the following:
SQL queries on part of or the whole XML document – SQL functions existsNode and extract provide the necessary SQL query functions over XML documents.
XPath access using SQL functions existsNode and extract – XMLType uses the built-in C XML parser and processor and hence provides better performance and scalability when used inside the server.
Strong typing inside SQL statements and PL/SQL functions – The strong typing offered by XMLType ensures that the values passed in are XML values and not any arbitrary text string.
Indexing on XPath document queries – XMLType has methods that you can use to create function-based indexes that optimize searches.
Separation of applications from storage models – Using XMLType instead of directly using CLOB, object-relational, or binary XML storage lets applications gracefully move to various storage alternatives later without affecting any of the query or DML statements in the application.
Support for future optimizations – New XML functionality will support XMLType. Because Oracle Database is natively aware that XMLType can store XML data, better optimizations and indexing techniques can be done. By writing applications to use XMLType, these optimizations and enhancements can be easily achieved and preserved in future releases without your needing to rewrite applications.
The following examples create XMLType columns and tables for managing XML content in Oracle Database.
XML data has its own structure, which, except for object-relational storage of XMLType, is not reflected directly in database structure. That is, individual XML elements and attributes are not mapped to individual database columns or tables.
This means that, to constrain XML data according to the values of individual elements or attributes, the standard approach for relational data does not apply. Instead, you must create virtual columns that represent the XML data of interest, and then use those virtual columns to define the constraints that you need.
This approach applies only to XML data that is stored as binary XML. For XML data that uses unstructured storage, the database has no knowledge of the XML structure —the data is treated as flat text, but for binary XML storage that structure is known. You exploit this structural knowledge to create virtual columns, which the database can then use with constraints.
The technique is as follows:
Define virtual columns that correspond to the XML data that you are interested in.
Use those columns to constrain the XMLType data as a whole.
You create virtual columns on XMLType data as you would create virtual columns using any other type of data, but using a slightly different syntax. In particular, you cannot specify any constraints in association with the column definition.
Because XMLType is an abstract data type, if you create virtual columns on an XMLType table, those columns are hidden; they do not show up in DESCRIBE statements and so on. This enables tools that use operations such as DESCRIBE to function normally and not be misled by the virtual columns. If you create virtual columns on a table that has an XMLType column, the virtual columns will be listed by a DESCRIBE operation, along with all of the non-virtual columns.
You create a virtual column based on an XML element or attribute by defining it in terms of a SQL expression that involves that element or attribute; that is, you create a function-based column. You can use SQL function extractValue as the function.
See Also:
Oracle Database SQL Language Reference for information about creating tables with virtual columns
You can load XML content into Oracle XML DB using these techniques:
Table-based loading:
Path-based repository loading techniques:
You can use a simple INSERT operation in SQL or PL/SQL to load an XML document into the database. Before the document can be stored as an XMLType column or table, it must be converted into an XMLType instance using one of the XMLType constructors.
See Also:
Oracle Database PL/SQL Packages and Types Reference for a description of the XMLType constructors
XMLType constructors allow an XMLType instance to be created from different sources, including VARCHAR, CLOB, and BFILE values. The constructors accept additional arguments that reduce the amount of processing associated with XMLType creation. For example, if you are sure that a given source XML document is valid, you can provide an argument to the constructor that disables the type-checking that is otherwise performed.
In addition, if the source data is not encoded in the database character set, an XMLType instance can be constructed using a BFILE or BLOB value. The encoding of the source data is specified through the character set id (csid) argument of the constructor.
Create a SQL Directory That Points to the Needed Directory
Example 3-3 shows how to insert XML content into an XMLType table. Before making this insertion, you must create a SQL directory object that points to the directory containing the file to be processed. To do this, you must have the CREATE ANY DIRECTORY privilege.
CREATE DIRECTORY xmldir AS path_to_folder_containing_XML_file;
Example 3-4 Inserting XML Content into an XML Type Table Using Java
This example shows how to load XML content into Oracle XML DB by first creating an XMLType instance in Java, given a Document Object Model (DOM).
public void doInsert(Connection conn, Document doc)
throws Exception
{
String SQLTEXT = "INSERT INTO purchaseorder VALUES (?)";
XMLType xml = null;
xml = XMLType.createXML(conn,doc);
OraclePreparedStatement sqlStatement = null;
sqlStatement = (OraclePreparedStatement) conn.prepareStatement(SQLTEXT);
sqlStatement.setObject(1,xml);
sqlStatement.execute();
}
1 row selected.
The "Simple Bulk Loader Application" available on the Oracle Technology Network (OTN) site at http://www.oracle.com/technology/sample_code/tech/xml/xmldb/content.html demonstrates how to load a directory of XML files into Oracle XML DB using Java Database Connectivity (JDBC). JDBC is a set of Java interfaces to Oracle Database.
Example 3-5 shows, in C, how to insert XML content into an XMLType table by creating an XMLType instance given a DOM.
Example 3-5 Inserting XML Content into an XMLType Table Using C
#include "stdio.h" #include <xml.h> #include <stdlib.h> #include <string.h> #include <ocixmldb.h> OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; OCIServer *srvhp; OCIDuration dur; OCISession *sesshp; oratext *username = "QUINE"; oratext *password = "CURRY"; oratext *filename = "AMCEWEN-20021009123336171PDT.xml"; oratext *schemaloc = "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"; /*--------------------------------------------------------*/ /* Execute a SQL statement that binds XML data */ /*--------------------------------------------------------*/ sword exec_bind_xml(OCISvcCtx *svchp, OCIError *errhp, OCIStmt *stmthp, void *xml, OCIType *xmltdo, OraText *sqlstmt) { OCIBind *bndhp1 = (OCIBind *) 0; sword status = 0; OCIInd ind = OCI_IND_NOTNULL; OCIInd *indp = &ind; if(status = OCIStmtPrepare(stmthp, errhp, (OraText *)sqlstmt, (ub4)strlen((const char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) return OCI_ERROR; if(status = OCIBindByPos(stmthp, &bndhp1, errhp, (ub4) 1, (dvoid *) 0, (sb4) 0, SQLT_NTY, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) return OCI_ERROR; if(status = OCIBindObject(bndhp1, errhp, (CONST OCIType *) xmltdo, (dvoid **) &xml, (ub4 *) 0, (dvoid **) &indp, (ub4 *) 0)) return OCI_ERROR; if(status = OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) return OCI_ERROR; return OCI_SUCCESS; } /*--------------------------------------------------------*/ /* Initialize OCI handles, and connect */ /*--------------------------------------------------------*/ sword init_oci_connect() { . . . } /*--------------------------------------------------------*/ /* Free OCI handles, and disconnect */ /*--------------------------------------------------------*/ void free_oci() { . . . } void main() { OCIType *xmltdo; xmldocnode *doc; ocixmldbparam params[1]; xmlerr err; xmlctx *xctx; oratext *ins_stmt; sword status; xmlnode *root; oratext buf[10000]; /* Initialize envhp, svchp, errhp, dur, stmthp */ init_oci_connect(); /* Get an XML context */ params[0].name_ocixmldbparam = XCTXINIT_OCIDUR; params[0].value_ocixmldbparam = &dur; xctx = OCIXmlDbInitXmlCtx(envhp, svchp, errhp, params, 1); if (!(doc = XmlLoadDom(xctx, &err, "file", filename, "schema_location", schemaloc, NULL))) { printf("Parse failed.\n"); return; } else printf("Parse succeeded.\n"); root = XmlDomGetDocElem(xctx, doc); printf("The xml document is :\n"); XmlSaveDom(xctx, &err, (xmlnode *)doc, "buffer", buf, "buffer_length", 10000, NULL); printf("%s\n", buf); /* Insert the document into my_table */ ins_stmt = (oratext *)"insert into purchaseorder values (:1)"; status = OCITypeByName(envhp, errhp, svchp, (const text *) "SYS", (ub4) strlen((const char *)"SYS"), (const text *) "XMLTYPE", (ub4) strlen((const char *)"XMLTYPE"), (CONST text *) 0, (ub4) 0, OCI_DURATION_SESSION, OCI_TYPEGET_HEADER, (OCIType **) &xmltdo); if (status == OCI_SUCCESS) { status = exec_bind_xml(svchp, errhp, stmthp, (void *)doc, xmltdo, ins_stmt); } if (status == OCI_SUCCESS) printf ("Insert successful\n"); else printf ("Insert failed\n"); /* Free XML instances */ if (doc) XmlFreeDocument((xmlctx *)xctx, (xmldocnode *)doc); /* Free XML CTX */ OCIXmlDbFreeXmlCtx(xctx); free_oci(); }
|
See Also: Appendix A, "Oracle-Supplied XML Schemas and Examples" for a complete listing of this example |
When loading large XML files consisting of a collection of smaller XML documents, it is often more efficient to use Simple API for XML (SAX) parsing to break the file into a set of smaller documents, and then insert those documents. SAX is an XML standard interface provided by XML parsers for event-based applications.
You can use SAX to load a database table from very large XML files in the order of 30 MB or larger, by creating individual documents from a collection of nodes. You can also bulk load XML files.
See Also:
http://www.oracle.com/technology/sample_code/tech/xml/xmldb/content.html, "SAX Loader Application" for an example of how to do thisUse SQL*Loader to load large amounts of XML data into Oracle Database. SQL*Loader loads in one of two modes, conventional or direct path. Table 3-1 compares these modes.
Table 3-1 SQL*Loader – Conventional and Direct-Path Load Modes
| Conventional Load Mode | Direct-Path Load Mode |
|---|---|
|
Uses SQL to load data into Oracle Database. This is the default mode. |
Bypasses SQL and streams the data directly into Oracle Database. |
|
Advantage: Follows SQL semantics. For example triggers are fired and constraints are checked. |
Advantage: This loads data much faster than the conventional load mode. |
|
Disadvantage: This loads data slower than with the direct load mode. |
Disadvantage: SQL semantics are not obeyed. For example triggers are not fired and constraints are not checked. |
When loading LOBs with SQL*Loader direct-path load, much memory can be used. If the message SQL*Loader 700 (out of memory) appears, then it is likely that more rows are being batched in each load call than can be handled by your operating system and process memory. Workaround: use the ROWS option to read a smaller number of rows in each data save.
See Also:
Example 35-1, "Loading Very Large XML Documents Into Oracle Database Using SQL*Loader" for an example of direct loading of XML data.
You can also store XML documents in Oracle XML DB Repository, and access these documents using path-based rather than table-based techniques. To load an XML document into the repository under a given path, use PL/SQL package DBMS_XDB. This is illustrated by the following example.
Example 3-6 Inserting XML Content into the Repository Using PL/SQL DBMS_XDB
DECLARE
res BOOLEAN;
BEGIN
res := DBMS_XDB.createResource('/home/QUINE/purchaseOrder.xml',
bfilename('XMLDIR', 'purchaseOrder.xml'),
nls_charset_id('AL32UTF8'));
END;/
Many operations for configuring and using Oracle XML DB are based on processing one or more XML documents. Examples include registering an XML schema and performing an XSL transformation. The easiest way to make these XML documents available to Oracle Database is to load them into Oracle XML DB Repository.
You can load XML documents from a local file system into Oracle XML DB Repository using protocols such as WebDAV, from Windows Explorer or other tools that support WebDAV. Figure 3-1 shows a simple drag and drop operation for copying the contents of the SCOTT folder from the local hard drive to the poSource folder in the Oracle XML DB Repository.
Figure 3-1 Using Windows Explorer to Load Content into the Repository
The copied folder might contain, for example, an XML schema document, an HTML page, and some XSLT style sheets.
|
Note: Oracle XML DB Repository can also store content that is not XML data, such as HTML files, JPEG images, word documents, as well as XML documents (schema-based and non-schema-based). |
This section describes how character sets of XML documents are determined.
Caution:
AL32UTF8 is the Oracle Database character set that is appropriate forXMLType data. It is equivalent to the IANA registered standard UTF-8 encoding, which supports all valid XML characters.
Do not confuse Oracle Database database character set UTF8 (no hyphen) with database character set AL32UTF8 or with character encoding UTF-8. Database character set UTF8 has been superseded by AL32UTF8. Do not use UTF8 for XML data. UTF8 supports only Unicode version 3.1 and earlier; it does not support all valid XML characters. AL32UTF8 has no such limitation.
Using database character set UTF8 for XML data could potentially stop a system or affect security negatively. If a character that is not supported by the database character set appears in an input-document element name, a replacement character (usually "?") will be substituted for it. This will terminate parsing and raise an exception. It could cause a fatal error.
Each XML document is composed of units called entities. Each entity in an XML document may use a different encoding for its characters. Entities that are stored in an encoding other than UTF-8 or UTF-16 must begin with a declaration containing an encoding specification indicating the character encoding in use. For example:
<?xml version='1.0' encoding='EUC-JP' ?>
Entities encoded in UTF-16 must begin with the Byte Order Mark (BOM), as described in Appendix F of the XML 1.0 Reference. For example, on big-endian platforms, the BOM required of a UTF-16 data stream is #xFEFF.
In the absence of both the encoding declaration and the BOM, the XML entity is assumed to be encoded in UTF-8. Because ASCII is a subset of UTF-8, ASCII entities do not require an encoding declaration.
In many cases, external sources of information are available, besides the XML data, to provide the character encoding in use. For example, the encoding of the data can be obtained from the charset parameter of the Content-Type field in an HTTP(S) request as follows:
Content-Type: text/xml; charset=ISO-8859-4
In releases prior to Oracle Database 10g release 1, all XML documents were assumed to be in the database character set, regardless of the document encoding declaration. With Oracle Database 10g release 1, the document encoding is detected from the encoding declaration when the document is loaded into the database.
However, if the XML data is obtained from a CLOB or VARCHAR value, then the encoding declaration is ignored, because these two data types are always encoded in the database character set.
In addition, when loading data into Oracle XML DB, either through programmatic APIs or transfer protocols, you can provide external encoding to override the document encoding declaration. An error is raised if you try to load a schema-based XML document that contains characters that are not legal in the determined encoding.
The following examples show different ways to specify external encoding:
Using PL/SQL function DBMS_XDB.createResource to create a file resource from a BFILE, you can specify the file encoding with the CSID argument. If a zero CSID is specified then the file encoding is auto-detected from the document encoding declaration.
CREATE DIRECTORY xmldir AS '/private/xmldir';
CREATE OR REPLACE PROCEDURE loadXML(filename VARCHAR2, file_csid NUMBER) IS
xbfile BFILE;
RET BOOLEAN;
BEGIN
xbfile := bfilename('XMLDIR', filename);
ret := DBMS_XDB.createResource('/public/mypurchaseorder.xml',
xbfile,
file_csid);
END;/
Use the FTP protocol to load documents into Oracle XML DB. Use the quote set_charset FTP command to indicate the encoding of the files to be loaded.
FTP> quote set_charset Shift_JIS FTP> put mypurchaseorder.xml
Use the HTTP(S) protocol to load documents into Oracle XML DB. Specify the encoding of the data to be transmitted to Oracle XML DB in the request header.
Content-Type: text/xml; charset= EUC-JP
XML documents stored in Oracle XML DB can be retrieved using a SQL client, programmatic APIs, or transfer protocols. You can specify the encoding of the retrieved data (except in Oracle Database releases prior to 10g, where XML data is retrieved only in the database character set).
When XML data is stored as a CLOB or VARCHAR2 value, the encoding declaration, if present, is always ignored for retrieval, just as for storage. This means that the encoding of a retrieved document can be different from the encoding explicitly declared in that document.
The character set for an XML document retrieved from the database is determined in the following ways:
SQL client – If a SQL client (such as SQL*Plus) is used to retrieve XML data, then the character set is determined by the client-side environment variable NLS_LANG. In particular, this setting overrides any explicit character-set declarations in the XML data itself.
For example, if you set the client side NLS_LANG variable to AMERICAN_AMERICA.AL32UTF8 and then retrieve an XML document with encoding EUC_JP provided by declaration <?xml version="1.0" encoding="EUC-JP"?>, the character set of the retrieved document is AL32UTF8, not EUC_JP.
PL/SQL and APIs – Using PL/SQL or programmatic APIs, you can retrieve XML data into VARCHAR, CLOB, or XMLType data types. As for SQL clients, you can control the encoding of the retrieved data by setting NLS_LANG.
You can also retrieve XML data into a BLOB value using XMLType and URIType methods. These methods let you specify the character set of the returned BLOB value. Here is an example:
CREATE OR REPLACE FUNCTION getXML(pathname VARCHAR2, charset VARCHAR2)
RETURN BLOB IS
xblob BLOB;
BEGIN
SELECT e.RES.getBLOBVal(nls_charset_id(charset)) INTO xblob
FROM RESOURCE_VIEW e WHERE equals_path(e.RES, pathname) = 1;
RETURN xblob;
END;/
FTP – You can use the FTP quote set_nls_locale command to set the character set:
FTP> quote set_nls_locale EUC-JP FTP> get mypurchaseorder.xml
See Also:
FTP Quote MethodsHTTP(S) – You can use the Accept-Charset parameter in an HTTP(S) request:
/httptest/mypurchaseorder.xml 1.1 HTTP/Host: localhost:2345 Accept: text/* Accept-Charset: iso-8859-1, utf-8
See Also:
Controlling Character Sets for HTTP(S)The W3C XML Schema Recommendation defines a standardized language for specifying the structure, content, and certain semantics of a set of XML documents. An XML schema can be considered the metadata that describes a class of XML documents. The XML Schema Recommendation is described at: http://www.w3.org/TR/xmlschema-0/
Documents conforming to a given XML schema can be considered as members or instances of the class defined by that XML schema. Consequently the term instance document is often used to describe an XML document that conforms to a given XML schema. The most common use of an XML schema is to validate that a given instance document conforms to the rules defined by the XML schema.
The W3C Schema working group publishes an XML schema, often referred to as the "Schema for Schemas". This XML schema provides the definition, or vocabulary, of the XML Schema language. All valid XML schemas can be considered as members of the class defined by this XML schema. This means that an XML schema is an XML document that conforms to the class defined by the XML schema published at http://www.w3.org/2001/XMLSchema.
XML schemas can be authored and edited using any of the following:
A simple text editor, such as emacs or vi
An XML schema-aware editor, such as the XML editor included with Oracle JDeveloper
An explicit XML schema-authoring tool, such as XMLSpy from Altova Corporation
The XML Schema language defines 47 scalar data types. This provides for strong typing of elements and attributes. The W3C XML Schema Recommendation also supports object-oriented techniques such as inheritance and extension, hence you can design XML schema with complex objects from base data types defined by the XML Schema language. The vocabulary includes constructs for defining and ordering, default values, mandatory content, nesting, repeated sets, and redefines. Oracle XML DB supports all the constructs, except for redefines.
The following example purchaseOrder.xsd, is an XML schema example fragment, in its native form, as an XML Document:
Example 3-7 Purchase-Order XML Schema, purchaseOrder.xsd
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" version="1.0">
<xs:element name="PurchaseOrder" type="PurchaseOrderType"/>
<xs:complexType name="PurchaseOrderType">
<xs:sequence>
<xs:element name="Reference" type="ReferenceType"/>
<xs:element name="Actions" type="ActionsType"/>
<xs:element name="Reject" type="RejectionType" minOccurs="0"/>
<xs:element name="Requestor" type="RequestorType"/>
<xs:element name="User" type="UserType"/>
<xs:element name="CostCenter" type="CostCenterType"/>
<xs:element name="ShippingInstructions" type="ShippingInstructionsType"/>
<xs:element name="SpecialInstructions" type="SpecialInstructionsType"/>
<xs:element name="LineItems" type="LineItemsType"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LineItemsType">
<xs:sequence>
<xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LineItemType">
<xs:sequence>
<xs:element name="Description" type="DescriptionType"/>
<xs:element name="Part" type="PartType"/>
</xs:sequence>
<xs:attribute name="ItemNumber" type="xs:integer"/>
</xs:complexType>
<xs:complexType name="PartType">
<xs:attribute name="Id">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:minLength value="10"/>
<xs:maxLength value="14"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="Quantity" type="moneyType"/>
<xs:attribute name="UnitPrice" type="quantityType"/>
</xs:complexType>
<xs:simpleType name="ReferenceType">
<xs:restriction base="xs:string">
<xs:minLength value="18"/>
<xs:maxLength value="30"/>
</xs:restriction>
</xs:simpleType>
<xs:complexType name="ActionsType">
<xs:sequence>
<xs:element name="Action" maxOccurs="4">
<xs:complexType>
<xs:sequence>
<xs:element name="User" type="UserType"/>
<xs:element name="Date" type="DateType" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="RejectionType">
<xs:all>
<xs:element name="User" type="UserType" minOccurs="0"/>
<xs:element name="Date" type="DateType" minOccurs="0"/>
<xs:element name="Comments" type="CommentsType" minOccurs="0"/>
</xs:all>
</xs:complexType>
<xs:complexType name="ShippingInstructionsType">
<xs:sequence>
<xs:element name="name" type="NameType" minOccurs="0"/>
<xs:element name="address" type="AddressType" minOccurs="0"/>
<xs:element name="telephone" type="TelephoneType" minOccurs="0"/>
</xs:sequence>
</xs:complexType>
<xs:simpleType name="moneyType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="2"/>
<xs:totalDigits value="12"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="quantityType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="4"/>
<xs:totalDigits value="8"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="UserType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="10"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="RequestorType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="128"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="CostCenterType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="4"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="VendorType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="PurchaseOrderNumberType">
<xs:restriction base="xs:integer"/>
</xs:simpleType>
<xs:simpleType name="SpecialInstructionsType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="2048"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="NameType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="AddressType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="256"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="TelephoneType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="24"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="DateType">
<xs:restriction base="xs:date"/>
</xs:simpleType>
<xs:simpleType name="CommentsType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="2048"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="DescriptionType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="256"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>
Figure 3-2 shows the purchase-order XML schema displayed using XMLSpy. XMLSpy is a graphical and user-friendly tool from Altova Corporation for creating and editing XML schema and XML documents. See http://www.altova.com for details. XMLSpy also supports WebDAV and FTP protocols hence can directly access and edit content stored in Oracle XML DB Repository.
Figure 3-2 XMLSpy Graphical Representation of the PurchaseOrder XML Schema
The PurchaseOrder XML schema is a simple XML schema that demonstrates key features of a typical XML document:
Global element PurchaseOrder is an instance of the complexType PurchaseOrderType
PurchaseOrderType defines the set of nodes that make up a PurchaseOrder element
LineItems element consists of a collection of LineItem elements
Each LineItem element consists of two elements: Description and Part
Part element has attributes Id, Quantity, and UnitPrice
This section describes the use of XML Schema with Oracle XML DB.
The following paragraphs describe the main reasons for using XML schema with Oracle XML DB.
The most common usage of XML Schema is as a mechanism for validating that instance documents conform to a given XML schema. The XMLType data type methods isSchemaValid() and schemaValidate() allow Oracle XML DB to validate the contents of an instance document stored in an XMLType, against an XML schema.
An XML schema can also be used as a constraint when creating tables or columns of XMLType. For example, the XMLType is constrained to storing XML documents compliant with one of the global elements defined by the XML schema.
Oracle XML DB also uses XML Schema as a mechanism for defining how the contents of an XMLType instance should be stored inside the database. All storage models support the use of XML Schema: binary XML, structured, unstructured, and hybrid (a combination of structured and unstructured). See "XMLType Storage Models" for information on the available storage models for XMLType.
Structured storage of XML documents is based on decomposing the content of the document into a set of SQL objects. These SQL objects are based on the SQL 1999 Type framework. When an XML schema is registered with Oracle XML DB, the required SQL type definitions are automatically generated from the XML schema.
A SQL type definition is generated from each complexType defined by the XML schema. Each element or attribute defined by the complexType becomes a SQL attribute in the corresponding SQL type. Oracle XML DB automatically maps the 47 scalar data types defined by the XML Schema Recommendation to the 19 scalar data types supported by SQL. A varray type is generated for each element and this can occur multiple times.
The generated SQL types allow XML content, compliant with the XML schema, to be decomposed and stored in the database as a set of objects without any loss of information. When the document is ingested the constructs defined by the XML schema are mapped directly to the equivalent SQL types. This lets Oracle XML DB leverage the full power of Oracle Database when managing XML and can lead to significant reductions in the amount of space required to store the document. It can also reduce the amount of memory required to query and update XML content.
The W3C XML Schema Recommendation defines an annotation mechanism that lets vendor-specific information be added to an XML schema. Oracle XML DB uses this mechanism to control the mapping between the XML schema and database features.
You can use XML schema annotations to do the following:
Specify which database tables are used to store the XML data.
Override the default mapping between XML Schema data types and either binary XML encoding types or, for structured storage, SQL data types.
Name the database objects and attributes that are created to store XML data (for structured storage).
When you register an XML schema for data that is stored object-relationally and you set registration parameter GENTABLES to TRUE, default tables are created automatically to store the associated XML instance documents.
Order is preserved among XML collection elements when they are stored. The result is an ordered collection.Foot 1 You can store data in an ordered collection in these ways:
Varray in a table. Each element in the collection is mapped to a SQL object. The collection of SQL objects is stored as a set of rows in a table, called an ordered collection table (OCT). By default, all collections are stored in OCTs; this corresponds to the XML schema annotation xdb:storeVarrayAsTable = "true" (default value).
Varray in a LOB. Each element in the collection is mapped to a SQL object. The entire collection of SQL objects is serialized as a varray and stored in a LOB column. To store a given collection as a varray in a LOB, use XML schema annotation xdb:storeVarrayAsTable = "false".
You can also use out-of-line storage for an ordered collection. This corresponds to XML schema annotation SQLInline = "false", and it means that a varray of REFs in the collection table or LOB tracks the collection content, which is stored out of line.
There is no requirement to annotate an XML schema before using it. Oracle XML DB uses a set of default assumptions when processing an XML schema that contains no annotations.
If you do not supply any of the annotations mentioned in this section, then Oracle XML DB stores a collection as a heap-based OCT. You can force OCTs to be stored as index-organized tables (IOTs) instead, by passing REGISTER_NT_AS_IOT in the OPTIONS parameter of DBMS_XMLSCHEMA.registerschema.
Note:
Oracle recommends that you use heap-based OCTs, not IOTs. Oracle Text does not support IOTs.See also: Chapter 11, "Full-Text Search Over XML Data" for information about using Oracle Text with XML data.
Note:
In releases prior to Oracle Database 11g Release 1:The default value for xdb:storeVarrayAsTable was false.
OCTs were stored as IOTs by default.
Before annotating an XML schema you must first declare the Oracle XML DB namespace. The Oracle XML DB namespace is defined as:
http://xmlns.oracle.com/xdb
The namespace is declared in the XML schema by adding a namespace declaration such as the following to the root element of the XML schema:
xmlns:xdb="http://xmlns.oracle.com/xdb"
Note the use of a namespace prefix (xdb). This makes it possible to abbreviate the namespace to xdb when adding annotations.
Example 3-8 shows the beginning of the PurchaseOrder XML schema with annotations. See Example A-1 for the complete schema listing.
Example 3-8 Annotated Purchase-Order XML Schema, purchaseOrder.xsd
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"
version="1.0"
xdb:storeVarrayAsTable="true">
<xs:element name="PurchaseOrder" type="PurchaseOrderType" xdb:defaultTable="PURCHASEORDER"/>
<xs:complexType name="PurchaseOrderType" xdb:SQLType="PURCHASEORDER_T">
<xs:sequence>
<xs:element name="Reference" type="ReferenceType" minOccurs="1" xdb:SQLName="REFERENCE"/>
<xs:element name="Actions" type="ActionsType" xdb:SQLName="ACTIONS"/>
<xs:element name="Reject" type="RejectionType" minOccurs="0" xdb:SQLName="REJECTION"/>
<xs:element name="Requestor" type="RequestorType" xdb:SQLName="REQUESTOR"/>
<xs:element name="User" type="UserType" minOccurs="1" xdb:SQLName="USERID"/>
<xs:element name="CostCenter" type="CostCenterType" xdb:SQLName="COST_CENTER"/>
<xs:element name="ShippingInstructions" type="ShippingInstructionsType"
xdb:SQLName="SHIPPING_INSTRUCTIONS"/>
<xs:element name="SpecialInstructions" type="SpecialInstructionsType"
xdb:SQLName="SPECIAL_INSTRUCTIONS"/>
<xs:element name="LineItems" type="LineItemsType" xdb:SQLName="LINEITEMS"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LineItemsType" xdb:SQLType="LINEITEMS_T">
<xs:sequence>
<xs:element name="LineItem" type="LineItemType" maxOccurs="unbounded"
xdb:SQLName="LINEITEM" xdb:SQLCollType="LINEITEM_V"/>
</xs:sequence>
</xs:complexType>
<xs:complexType name="LineItemType" xdb:SQLType="LINEITEM_T">
<xs:sequence>
<xs:element name="Description" type="DescriptionType"
xdb:SQLName="DESCRIPTION"/>
<xs:element name="Part" type="PartType" xdb:SQLName="PART"/>
</xs:sequence>
<xs:attribute name="ItemNumber" type="xs:integer" xdb:SQLName="ITEMNUMBER"
xdb:SQLType="NUMBER"/>
</xs:complexType>
<xs:complexType name="PartType" xdb:SQLType="PART_T">
<xs:attribute name="Id" xdb:SQLName="PART_NUMBER" xdb:SQLType="VARCHAR2">
<xs:simpleType>
<xs:restriction base="xs:string">
<xs:minLength value="10"/>
<xs:maxLength value="14"/>
</xs:restriction>
</xs:simpleType>
</xs:attribute>
<xs:attribute name="Quantity" type="moneyType" xdb:SQLName="QUANTITY"/>
<xs:attribute name="UnitPrice" type="quantityType" xdb:SQLName="UNITPRICE"/>
</xs:complexType>
<xs:simpleType name="ReferenceType">
<xs:restriction base="xs:string">
<xs:minLength value="18"/>
<xs:maxLength value="30"/>
</xs:restriction>
</xs:simpleType>
<xs:complexType name="ActionsType" xdb:SQLType="ACTIONS_T">
<xs:sequence>
<xs:element name="Action" maxOccurs="4" xdb:SQLName="ACTION" xdb:SQLCollType="ACTION_V">
<xs:complexType xdb:SQLType="ACTION_T">
<xs:sequence>
<xs:element name="User" type="UserType" xdb:SQLName="ACTIONED_BY"/>
<xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_ACTIONED"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
<xs:complexType name="RejectionType" xdb:SQLType="REJECTION_T">
<xs:all>
<xs:element name="User" type="UserType" minOccurs="0" xdb:SQLName="REJECTED_BY"/>
<xs:element name="Date" type="DateType" minOccurs="0" xdb:SQLName="DATE_REJECTED"/>
<xs:element name="Comments" type="CommentsType" minOccurs="0" xdb:SQLName="REASON_REJECTED"/>
</xs:all>
</xs:complexType>
<xs:complexType name="ShippingInstructionsType" xdb:SQLType="SHIPPING_INSTRUCTIONS_T">
<xs:sequence>
<xs:element name="name" type="NameType" minOccurs="0" xdb:SQLName="SHIP_TO_NAME"/>
<xs:element name="address" type="AddressType" minOccurs="0" xdb:SQLName="SHIP_TO_ADDRESS"/>
<xs:element name="telephone" type="TelephoneType" minOccurs="0" xdb:SQLName="SHIP_TO_PHONE"/>
</xs:sequence>
</xs:complexType>
<xs:simpleType name="moneyType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="2"/>
<xs:totalDigits value="12"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="quantityType">
<xs:restriction base="xs:decimal">
<xs:fractionDigits value="4"/>
<xs:totalDigits value="8"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="UserType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="10"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="RequestorType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="128"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="CostCenterType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="4"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="VendorType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="PurchaseOrderNumberType">
<xs:restriction base="xs:integer"/>
</xs:simpleType>
<xs:simpleType name="SpecialInstructionsType">
<xs:restriction base="xs:string">
<xs:minLength value="0"/>
<xs:maxLength value="2048"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="NameType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="20"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="AddressType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="256"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="TelephoneType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="24"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="DateType">
<xs:restriction base="xs:date"/>
</xs:simpleType>
<xs:simpleType name="CommentsType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="2048"/>
</xs:restriction>
</xs:simpleType>
<xs:simpleType name="DescriptionType">
<xs:restriction base="xs:string">
<xs:minLength value="1"/>
<xs:maxLength value="256"/>
</xs:restriction>
</xs:simpleType>
</xs:schema>
The PurchaseOrder XML schema defines the following two namespaces:
http://www.w3c.org/2001/XMLSchema. This is reserved by W3C for the Schema for Schemas.
http://xmlns.oracle.com/xdb. This is reserved by Oracle for the Oracle XML DB schema annotations.
The PurchaseOrder schema uses several annotations, including the following:
defaultTable annotation in the PurchaseOrder element. This specifies that XML documents, compliant with this XML schema are stored in a database table called purchaseorder.
SQLType annotation.
The first occurrence of SQLType specifies that the name of the SQL type generated from complexType element PurchaseOrderType is purchaseorder_t.
The second occurrence of SQLType specifies that the name of the SQL type generated from the complexType element LineItemType is lineitem_t and the SQL type that manages the collection of LineItem elements is lineitem_v.
SQLName annotation. This provides an explicit name for each SQL attribute of purchaseorder_t.
Figure 3-3 shows the XMLSpy Oracle tab, which facilitates adding Oracle XML DB schema annotations to an XML schema while working in the graphical editor.
Figure 3-3 XMLSpy Showing Support for Oracle XML DB Schema Annotations
For an XML schema to be useful to Oracle XML DB you must first register it with Oracle XML DB. After it has been registered, it can be used for validating XML documents and for creating XMLType tables and columns bound to the XML schema.
Two items are required to register an XML schema with Oracle XML DB:
The XML schema document
A string that can be used as a unique identifier for the XML schema, after it is registered with Oracle Database. Instance documents use this unique identifier to identify themselves as members of the class defined by the XML schema. The identifier is typically in the form of a URL, and is often referred to as the schema location hint or document location hint.
You register an XML schema with PL/SQL procedure DBMS_XMLSCHEMA.registerschema. See Example 3-9. By default, when an XML schema is registered, Oracle XML DB automatically generates all of the SQL object types and XMLType tables required to manage the instance documents.
XML schemas can be registered as global or local.
See Also:
"Delete and Reload Documents, Before Registering an XML Schema They Reference" for considerations to keep in mind when you register an XML schema
Chapter 6, "XML Schema Storage and Query: Basic" for a discussion of the differences between global and local schemas
Oracle Database PL/SQL Packages and Types Reference for information about DBMS_XMLSCHEMA.registerschema
Example 3-9 Registering an XML Schema with DBMS_XMLSCHEMA.registerSchema
BEGIN
DBMS_XMLSCHEMA.registerSchema(
'http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd',
XDBURIType('/source/schemas/poSource/xsd/purchaseOrder.xsd').getCLOB(),
TRUE,
TRUE,
FALSE,
TRUE);
END;
/
In this example, the unique identifier for the XML schema is:
http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd
The XML schema document was previously loaded into Oracle XML DB Repository at this path: /source/schemas/poSource/xsd/purchaseOrder.xsd.
During XML schema registration, an XDBURIType accesses the content of the XML schema document, based on its location in the repository. Flags passed to procedure registerSchema specify that the XML schema must be registered as a local schema, and that SQL objects and tables must be generated by the registration process.
Procedure DBMS_XMLSCHEMA.registerSchema performs the following operations:
Parses and validates the XML schema.
Creates a set of entries in Oracle Data Dictionary that describe the XML schema.
Creates a set of SQL object definitions, based on complexType elements defined in the XML schema.
Creates an XMLType table for each global element defined by the XML schema.
Example 3-10 illustrates the creation of object types during XML schema registration with Oracle XML DB.
Example 3-10 Objects Created During XML Schema Registration
DESCRIBE purchaseorder_t purchaseorder_t is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T REFERENCE VARCHAR2(30 CHAR) ACTIONS ACTIONS_T REJECTION REJECTION_T REQUESTOR VARCHAR2(128 CHAR) USERID VARCHAR2(10 CHAR) COST_CENTER VARCHAR2(4 CHAR) SHIPPING_INSTRUCTIONS SHIPPING_INSTRUCTIONS_T SPECIAL_INSTRUCTIONS VARCHAR2(2048 CHAR) LINEITEMS LINEITEMS_T DESCRIBE lineitems_t lineitems_t is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T LINEITEM LINEITEM_V DESCRIBE lineitem_v lineitem_v VARRAY(2147483647) OF LINEITEM_T LINEITEM_T is NOT FINAL Name Null? Type ----------------------------------------- -------- ---------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T ITEMNUMBER NUMBER(38) DESCRIPTION VARCHAR2(256 CHAR) PART PART_T
This example shows that SQL type definitions were created when the XML schema was registered with Oracle XML DB. These SQL type definitions include:
purchaseorder_t. This type is used to persist the SQL objects generated from a PurchaseOrder element. When an XML document containing a PurchaseOrder element is stored in Oracle XML DB the document is broken up, and the contents of the document are stored as an instance of purchaseorder_t.
lineitems_t, lineitem_v, and lineitem_t. These types manage the collection of LineItem elements that may be present in a PurchaseOrder document. Type lineitems_t consists of a single attribute lineitem, defined as an instance of type lineitem_v. Type lineitem_v is defined as a varray of linteitem_t objects. There is one instance of the lineitem_t object for each LineItem element in the document.
A number of issues can arise when working with large, complex XML schemas. Sometimes, you will encounter one of these errors when you register an XML schema or you create a table that is based on a global element defined by an XML schema:
ORA-01792: maximum number of columns in a table or view is 1000
ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")
These errors are raised when an attempt is made to create an XMLType table or column based on a global element and the global element is defined as a complexType that contains a very large number of element and attribute definitions.The errors are raised only when creating an XMLType table or column that uses object-relational storage. In this case, the table or column is persisted using a SQL type, and each object attribute defined by the SQL type counts as one column in the underlying table. If the SQL type contains object attributes that are based on other SQL types, then the attributes defined by those types also count as columns in the underlying table.
If the total number of object attributes in all of the SQL types exceeds the Oracle Database limit of 1000 columns in a table, then the storage table cannot be created. When the total number of elements and attributes defined by a complexType reaches 1000, it is not possible to create a single table that can manage the SQL objects that are generated when an instance of that type is stored in the database.
Error ORA-01792 reports that the 1000-column limit has been exceeded. Error ORA-04031 reports that memory is insufficient during the processing of a large number of element and attribute definitions.To resolve this problem of having too many element and attribute definitions, you must reduce the total number of object attributes in the SQL types that are used to create the storage tables.
As a quick resolution of the problem, you can register the XML schema using REGISTER_AUTO_OOL in the OPTIONS parameter of procedure DBMS_XMLSCHEMA.registerSchema. When you do that, Oracle XML DB automatically moves large types out of line, reducing the likelihood of raising these errors. If you use this option, then you must also set registration parameter GENTABLES to TRUE.
Keep in mind that this option is only a stopgap; it is not a panacea. It will usually enable you to register the XML schema. You can then examine the automatically generated tables and try to achieve a result suitable to your application, reducing the number of object attributes used to create storage tables.
There are two ways to achieve this reduction:
Use a top-down technique, with multiple XMLType tables that manage the XML documents. This reduces the number of SQL attributes in the SQL type hierarchy for a given storage table. As long as none of the tables need to manage more than 1000 object attributes, the problem is resolved.
Use a bottom-up technique, which reduces the number of SQL attributes in the SQL type hierarchy, collapsing some elements and attributes defined by the XML schema so that they are stored as a single CLOB value.
Both techniques rely on annotating the XML schema to define how a particular complexType will be stored in the database.
For the top-down technique, annotations SQLInline = "false" and defaultTable force some subelements in the XML document to be stored as rows in a separate XMLType table. Oracle XML DB maintains the relationship between the two tables using a REF of XMLType. Good candidates for this approach are XML schemas that do either of the following:
Define a choice, where each element within the choice is defined as a complexType
Define an element based on a complexType that contains a large number of element and attribute definitions
The bottom-up technique involves reducing the total number of attributes in the SQL object types by choosing to store some of the lower-level complexType elements as CLOB values, rather than as objects. This is achieved by annotating the complexType or the usage of the complexType with SQLType = "CLOB".
Which technique you use depends on the application and the type of queries and updates to be performed against the data.
By default, when an XML schema is registered with the database, Oracle XML DB generates a default table for each global element defined by the XML schema.
You can use attribute xdb:defaultTable to specify the name of the default table for a given global element. Each xdb:defaultTable attribute value you provide must be unique among all schemas registered by a given database user. If you do not supply a nonempty default table name for some element, then a unique name is provided automatically.
In practice, however, you do not want to create a default table for most global elements. Elements that never serve as the root element for an XML instance document do not need default tables — such tables are never used. Creating default tables for all global elements can lead to significant overhead in processor time and space used, especially if an XML schema contains a large number of global element definitions.
As a general rule, then, you want to prevent the creation of a default table for any global element (or any local element stored out of line) that you are sure will not be used as a root element in any document. You can do this in one of the following ways:
Add the annotation xdb:defaultTable = "" (empty string) to the definition of each global element that will not appear as the root element of an XML instance document. Using this approach, you allow automatic default-table creation, in general, and you prohibit it explicitly where needed, using xdb:defaultTable = "".
Set parameter GENTABLES to false when registering the XML schema, and then manually create the default table for each global element that can legally appear as the root element of an instance document. Using this approach, you inhibit automatic default-table creation, and you create only the tables that are needed, by hand.
After an XML schema has been registered with Oracle XML DB, it can be referenced when defining tables that contain XMLType columns or creating XMLType tables.
Example 3-11 shows how to manually create table purchaseorder, the default table for PurchaseOrder elements.
Example 3-11 Creating an XMLType Table that Conforms to an XML Schema
CREATE TABLE purchaseorder OF XMLType
XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"
ELEMENT "PurchaseOrder"
VARRAY "XMLDATA"."ACTIONS"."ACTION"
STORE AS TABLE action_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)))
VARRAY "XMLDATA"."LINEITEMS"."LINEITEM"
STORE AS TABLE lineitem_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)));
Each member of the varray that manages the collection of Action elements is stored in the ordered collection table action_table. Each member of the varray that manages the collection of LineItem elements is stored as a row in ordered collection table lineitem_table. The ordered collection tables are heap-based. Because of the PRIMARY KEY specification, they automatically contain pseudocolumn NESTED_TABLE_ID and column SYS_NC_ARRAY_INDEX$, which are required to link them back to the parent column.
This CREATE TABLE statement is equivalent to the CREATE TABLE statement that is generated automatically by Oracle XML DB when you set parameter GENTABLES to TRUE during XML schema registration. By default, the value of XML schema annotation storeVarrayAsTable is true, which automatically generates ordered collection tables (OCTs) for collections during XML schema registration. These OCTs are given system-generated names, which can be difficult to work with. You can give them more meaningful names using the SQL statement RENAME TABLE.
The CREATE TABLE statement in Example 3-11 corresponds to a purchase-order document with a single level of nesting: The varray that manages the collection of LineItem elements is ordered collection table lineitem_table. What if you had a different XML schema that had, say a collection of Shipment elements inside a Shipments element that was, in turn, inside a LineItem element? In that case, you could create the table manually as shown in Example 3-12.
Example 3-12 Creating an XMLType Table for Nested Collections
CREATE TABLE purchaseorder OF XMLType
XMLSCHEMA "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd"
ELEMENT "PurchaseOrder"
VARRAY "XMLDATA"."ACTIONS"."ACTION"
STORE AS TABLE action_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$)))
VARRAY "XMLDATA"."LINEITEMS"."LINEITEM"
STORE AS TABLE lineitem_table
((PRIMARY KEY (NESTED_TABLE_ID, SYS_NC_ARRAY_INDEX$))
VARRAY "SHIPMENTS"."SHIPMENT"
STORE AS TABLE shipments_table
((PRIMARY KEY (NESTED_TABLE_ID,
SYS_NC_ARRAY_INDEX$))));
Example 3-13 Using DESCRIBE for an XML Schema-Based XMLType Table
A SQL*Plus DESCRIBE statement (it can be abbreviated to DESC), can be used to view information about an XMLType table.
DESCRIBE purchaseorder Name Null? Type ----------------------------------------- -------- ---------------------------- TABLE of SYS.XMLTYPE(XMLSchema "http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd" Element "PurchaseOrder") STORAGE Object-relational TYPE "PURCHASEORDER_T"
The output of the DESCRIBE statement shows the following information about the purchaseorder table:
The table is an XMLType table
The table is constrained to storing PurchaseOrder documents as defined by the PurchaseOrder XML schema
Rows in this table are stored as a set of objects in the database
SQL type purchaseorder_t is the base object for this table
The XML schema in Example 3-11 specifies that the PurchaseOrder table is the default table for PurchaseOrder elements. When an XML document compliant with the XML schema is inserted into Oracle XML DB Repository using protocols or PL/SQL, the content of the XML document is stored as a row in the purchaseorder table.
When an XML schema is registered as a global schema, you must grant the appropriate access rights on the default table to all other users of the database, before they can work with instance documents that conform to the globally registered XML schema.
Before an XML document can be inserted into an XML schema-based XMLType table or column the document must identify the associated XML schema. There are two ways to do this:
Explicitly identify the XML schema when creating the XMLType. This can be done by passing the name of the XML schema to the XMLType constructor, or by invoking XMLType method createSchemaBasedXML().
Use the XMLSchema-instance mechanism to explicitly provide the required information in the XML document. This option can be used when working with Oracle XML DB.
The advantage of the XMLSchema-instance mechanism is that it lets the Oracle XML DB protocol servers recognize that an XML document inserted into Oracle XML DB Repository is an instance of a registered XML schema. The content of the instance document is automatically stored in the default table specified by that XML schema.
The XMLSchema-instance mechanism is defined by the W3C XML Schema working group. It is based on adding attributes that identify the target XML schema to the root element of the instance document. These attributes are defined by the XMLSchema-instance namespace.
To identify an instance document as a member of the class defined by a particular XML schema you must declare the XMLSchema-instance namespace by adding a namespace declaration to the root element of the instance document. For example:
xmlns:xsi = http://www.w3.org/2001/XMLSchema-instance
Once the XMLSchema-instance namespace has been declared and given a namespace prefix, attributes that identify the XML schema can be added to the root element of the instance document. In the preceding example, the namespace prefix for the XMLSchema-instance namespace was defined as xsi. This prefix can then be used when adding the XMLSchema-instance attributes to the root element of the instance document.
Which attributes must be added depends on a number of factors. There are two possibilities, noNamespaceSchemaLocation and schemaLocation. Depending on the XML schema, one or both of these attributes is required to identify the XML schemas that the instance document is associated with.
If the target XML schema does not declare a target namespace, the noNamespaceSchemaLocation attribute is used to identify the XML schema. The value of the attribute is the schema location hint. This is the unique identifier passed to PL/SQL procedure DBMS_XMLSCHEMA.registerSchema when the schema is registered with the database.
For the purchaseOrder.xsd XML schema, the correct definition of the root element of the instance document would read as follows:
<PurchaseOrder
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
xsi:noNamespaceSchemaLocation=
"http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
If the target XML schema declares a target namespace, then the schemaLocation attribute is used to identify the XML schema. The value of this attribute is a pair of values separated by a space:
the value of the target namespace declared in the XML schema
the schema location hint, the unique identifier passed to procedure DBMS_XMLSCHEMA.registerSchema when the schema is registered with the database
For example, assume that the PurchaseOrder XML schema includes a target namespace declaration. The root element of the schema would look like this:
<xs:schema targetNamespace="http://demo.oracle.com/xdb/purchaseOrder"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:xdb="http://xmlns.oracle.com/xdb"
version="1.0" xdb:storeVarrayAsTable="true">
<xs:element name="PurchaseOrder" type="PurchaseOrderType"
xdb:defaultTable="PURCHASEORDER"/>
In this case, the correct form of the root element of the instance document would read as follows:
<PurchaseOrder
xnlns="http://demo.oracle.com/xdb/purchaseOrder"
xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance
xsi:schemaLocation=
"http://demo.oracle.com/xdb/purchaseOrder
http://mdrake-lap:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
When the XML schema includes elements defined in multiple namespaces, an entry must occur in the schemaLocation attribute for each of the XML schemas. Each entry consists of the namespace declaration and the schema location hint. The entries are separated from each other by one or more whitespace characters. If the primary XML schema does not declare a target namespace, then the instance document also needs to include a noNamespaceSchemaLocation attribute that provides the schema location hint for the primary XML schema.
One advantage of using Oracle XML DB to manage XML content is that SQL can be used to supplement the functionality provided by XML schema. Combining the power of SQL and XML with the ability of the database to enforce rules makes the database a powerful framework for managing XML content.
Only well-formed XML documents can be stored in XMLType tables or columns. A well-formed XML document is one that conforms to the syntax of the XML version declared in its XML declaration. This includes having a single root element, properly nested tags, and so forth. Additionally, if the XMLType table or column is constrained to an XML schema, only documents that conform to that XML schema can be stored in that table or column. Any attempt to store or insert any other kind of XML document in an XML schema-based XMLType raises an error. Example 3-14 illustrates this.
Example 3-14 Error From Attempting to Insert an Incorrect XML Document
INSERT INTO purchaseorder
VALUES (XMLType(bfilename('XMLDIR', 'Invoice.xml'), nls_charset_id('AL32UTF8')))
VALUES (XMLType(bfilename('XMLDIR', 'Invoice.xml'), nls_charset_id('AL32UTF8')))
*
ERROR at line 2:
ORA-19007: Schema - does not match expected
http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd.
Such an error only occurs when content is inserted directly into an XMLType table. It indicates that Oracle XML DB did not recognize the document as a member of the class defined by the XML schema. For a document to be recognized as a member of the class defined by the schema, the following conditions must be true:
The name of the XML document root element must match the name of global element used to define the XMLType table or column.
The XML document must include the appropriate attributes from the XMLSchema-instance namespace, or the XML document must be explicitly associated with the XML schema using the XMLType constructor or the createSchemaBasedXML() method.
If the constraining XML schema declares a targetNamespace, then the instance documents must contain the appropriate namespace declarations to place the root element of the document in the targetNamespace defined by the XML schema.
Note:
XML constraints are enforced only within individual XML documents. Database (SQL) constraints are enforced across sets of XML documents.This section describes the differences between partial and full XML schema validation used when inserting XML documents into the database.
For binary XML storage, Oracle XML DB performs a full validation whenever an XML document is inserted into an XML schema-based XMLType table or column. For all other models of XML storage, Oracle XML DB performs only a partial validation of the document. This is because, except for binary XML storage, complete XML schema validation is quite costly, in terms of performance.
Partial validation ensures only that all of the mandatory elements and attributes are present, and that there are no unexpected elements or attributes in the document. That is, it ensures only that the structure of the XML document conforms to the SQL data type definitions that were derived from the XML schema. Partial validation does not ensure that the instance document is fully compliant with the XML schema. Example 3-15 provides an example of failing partial validation while inserting an XML document into table PurchaseOrder, which is stored object-relationally.
Example 3-15 Error When Inserting Incorrect XML Document (Partial Validation)
INSERT INTO purchaseorder
VALUES(XMLType(bfilename('XMLDIR', 'InvalidElement.xml'),
nls_charset_id('AL32UTF8')));
VALUES(XMLType(bfilename('XMLDIR', 'InvalidElement.xml'),
*
ERROR at line 2:
ORA-30937: No schema definition for 'UserName' (namespace '##local') in parent
'/PurchaseOrder'
Loading XML data into XML schema-based binary XML storage causes full validation against the target XML schemas. Otherwise, regardless of storage model, you can force full validation of XML instance documents against an XML schema at any time, using either of the following:
Table level CHECK constraint
PL/SQL BEFORE INSERT trigger
Both approaches ensure that only valid XML documents can be stored in the XMLType table.
The advantage of a TABLE CHECK constraint is that it is easy to code. The disadvantage is that it is based on SQL function XMLisValid, so it can only indicate whether or not the XML document is valid. When the XML document is invalid it cannot provide any information as to why it is invalid.
A BEFORE INSERT trigger requires slightly more code. The trigger validates the XML document by invoking the XMLType schemaValidate() method. The advantage of using schemaValidate() is that the exception raised provides additional information about what was wrong with the instance document. Using a BEFORE INSERT trigger also makes it possible to attempt corrective action when an invalid document is encountered.
Unless you are using binary XML storage, full XML schema validation costs processing time and memory. You should thus perform full XML schema validation only when necessary. If you can rely on your application to validate an XML document, you can obtain higher overall throughput with non-binary XML storage, by avoiding the overhead associated with full validation. If you cannot be sure about the validity of incoming XML documents, you can rely on the database to ensure that an XMLType table or column contains only schema-valid XML documents.
Example 3-16 shows how to force a full XML schema validation by adding a CHECK constraint to an XMLType table. In Example 3-16, the XML document InvalidReference is a not valid with respect to the XML schema. The XML schema defines a minimum length of 18 characters for the text node associated with the Reference element. In this document, the node contains the value SBELL-20021009, which is only 14 characters long. Partial validation would not catch this error. Unless the constraint or trigger is present, attempts to insert this document into the database would succeed.
Example 3-16 Using CHECK Constraint to Force Full XML Schema Validation
Here, a CHECK constraint is added to PurchaseOrder table. Any attempt to insert an invalid document into the table fails:
ALTER TABLE purchaseorder p
ADD CONSTRAINT validate_purchaseorder
CHECK (XMLIsValid(p.OBJECT_VALUE) = 1);
Table altered.
INSERT INTO purchaseorder
VALUES (XMLType(bfilename('XMLDIR', 'InvalidReference.xml'),
nls_charset_id('AL32UTF8')));
INSERT INTO purchaseorder
*
ERROR at line 1:
ORA-02290: check constraint (QUINE.VALIDATE_PURCHASEORDER) violated
The pseudocolumn name OBJECT_VALUE can be used to access the content of an XMLType table from within a trigger.
Example 3-17 Using BEFORE INSERT Trigger to Enforce Full XML Schema Validation
This example shows how to use a BEFORE INSERT trigger to validate that the data being inserted into the XMLType table conforms to the specified XML schema.
CREATE OR REPLACE TRIGGER validate_purchaseorder
BEFORE INSERT ON purchaseorder
FOR EACH ROW
BEGIN
IF (:new.OBJECT_VALUE IS NOT NULL) THEN :new.OBJECT_VALUE.schemavalidate();
END IF;
END;
/
Trigger created.
INSERT INTO purchaseorder VALUES (XMLType(bfilename('XMLDIR', 'InvalidReference.xml'),
nls_charset_id('AL32UTF8')));
VALUES (XMLType( bfilename('XMLDIR', 'InvalidReference.xml'),
*
ERROR at line 2:
ORA-31154: invalid XML document
ORA-19202: Error occurred in XML processing
LSX-00221: "SBELL-20021009" is too short (minimum length is 18)
ORA-06512: at "SYS.XMLTYPE", line 354
ORA-06512: at "QUINE.VALIDATE_PURCHASEORDER", line 3
ORA-04088: error during execution of trigger 'QUINE.VALIDATE_PURCHASEORDER'
The W3C XML Schema Recommendation defines a powerful language for defining the contents of an XML document. However, there are a number of simple data management concepts that are not currently addressed by the W3C XML Schema Recommendation. These include the ability to ensure that the value of an element or attribute has either of these properties:
It is unique across a set of XML documents (a UNIQUE constraint).
It exists in a particular data source that is outside of the current document (FOREIGN KEY constraint).
With Oracle XML DB, however, you can enforce such constraints. The mechanisms that you use to enforce integrity on XML data are the same mechanisms that you use to enforce integrity on relational data. Simple rules, such as uniqueness and foreign-key relationships, can be enforced by specifying constraints. More complex rules can be enforced by specifying database triggers.
Oracle XML DB lets you use the database to enforce business rules on XML content, in addition to enforcing rules that can be specified using XML Schema constructs. The database enforces these business rules regardless of whether XML is inserted directly into a table or uploaded using one of the protocols supported by Oracle XML DB Repository.
Example 3-18 Using a Virtual Column to Constrain an XMLType Table Stored as Binary XML
CREATE TABLE po_binaryxml OF XMLType XMLTYPE STORE AS BINARY XML VIRTUAL COLUMNS (c_reference AS (extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference'))); INSERT INTO po_binaryxml SELECT OBJECT_VALUE FROM OE.purchaseorder; 132 rows created. ALTER TABLE po_binaryxml ADD CONSTRAINT reference_is_unique UNIQUE (c_reference); INSERT INTO po_binaryxml VALUES (XMLType(bfilename('XMLDIR', 'DuplicateReference.xml'), nls_charset_id('AL32UTF8'))); INSERT INTO po_binaryxml * ERROR at line 1: ORA-00001: unique constraint (OE.REFERENCE_IS_UNIQUE) violated
Example 3-19 Database Integrity Constraints and Triggers for an XMLType Table Stored Object-Relationally
ALTER TABLE purchaseorder ADD CONSTRAINT reference_is_unique UNIQUE (XMLDATA."REFERENCE"); Table altered. ALTER TABLE purchaseorder ADD CONSTRAINT user_is_valid FOREIGN KEY (XMLDATA."USERID") REFERENCES hr.employees(email); Table altered. INSERT INTO purchaseorder VALUES (XMLType(bfilename('XMLDIR', 'purchaseOrder.xml'), nls_charset_id('AL32UTF8'))); 1 row created. INSERT INTO purchaseorder VALUES (XMLType(bfilename('XMLDIR', 'DuplicateReference.xml'), nls_charset_id('AL32UTF8'))); INSERT INTO purchaseorder * ERROR at line 1: ORA-00001: unique constraint (QUINE.REFERENCE_IS_UNIQUE) violated INSERT INTO purchaseorder VALUES (XMLType(bfilename('XMLDIR', 'InvalidUser.xml'), nls_charset_id('AL32UTF8'))); INSERT INTO purchaseorder * ERROR at line 1: ORA-02291: integrity constraint (QUINE.USER_IS_VALID) violated - parent key not found
Just as for Example 3-18, the uniqueness constraint reference_is_unique here ensures the uniqueness of the purchase-order Reference element across all documents stored in the table. The foreign key constraint user_is_valid here ensures that the value of element User corresponds to a value in the email column in the employees table.
The text node associated with the Reference element in the XML document DuplicateRefernce.xml contains the same value as the corresponding node in XML document PurchaseOrder.xml. This means that attempting to store both documents in Oracle XML DB violates the constraint reference_is_unique.
The text node associated with the User element in XML document InvalidUser.xml contains the value HACKER. There is no entry in the employees table where the value of the email column is HACKER. Attempting to store this document in Oracle XML DB violates the constraint user_is_valid.
Integrity rules defined using constraints and triggers are also enforced when XML schema-based XML content is loaded into Oracle XML DB Repository.
Example 3-20 Enforcing Database Integrity When Loading XML Using FTP
This example shows that database integrity is also enforced when a protocol, such as FTP, is used to upload XML schema-based XML content into Oracle XML DB Repository.
$ ftp localhost 2100 Connected to localhost. 220 mdrake-sun FTP Server (Oracle XML DB/Oracle Database 10g Enterprise Edition Release 10.1.0.0.0 - Beta) ready. Name (localhost:oracle10): QUINE 331 pass required for QUINE Password: 230 QUINE logged in ftp> cd /source/schemas 250 CWD Command successful ftp> put InvalidReference.xml 200 PORT Command successful 150 ASCII Data Connection 550- Error Response ORA-00604: error occurred at recursive SQL level 1 ORA-31154: invalid XML document ORA-19202: Error occurred in XML processing LSX-00221: "SBELL-20021009" is too short (minimum length is 18) ORA-06512: at "SYS.XMLTYPE", line 333 ORA-06512: at "QUINE.VALIDATE_PURCHASEORDER", line 3 ORA-04088: error during execution of trigger 'QUINE.VALIDATE_PURCHASEORDER' 550 End Error Response ftp> put InvalidElement.xml 200 PORT Command successful 150 ASCII Data Connection 550- Error Response ORA-30937: No schema definition for 'UserName' (namespace '##local') in parent 'PurchaseOrder' 550 End Error Response ftp> put DuplicateReference.xml 200 PORT Command successful 150 ASCII Data Connection 550- Error Response ORA-00604: error occurred at recursive SQL level 1 ORA-00001: unique constraint (QUINE.REFERENCE_IS_UNIQUE) violated 550 End Error Response ftp> put InvalidUser.xml 200 PORT Command successful 150 ASCII Data Connection 550- Error Response ORA-00604: error occurred at recursive SQL level 1 ORA-02291: integrity constraint (QUINE.USER_IS_VALID) violated - parent key not found 550 End Error Response
Full SQL Error Trace
When an error occurs while a document is being uploaded with a protocol, Oracle XML DB provides the client with the full SQL error trace. How the error is interpreted and reported to you is determined by the error-handling built into the client application. Some clients, such as the command line FTP tool, reports the error returned by Oracle XML DB, while others, such as Microsoft Windows Explorer, report a generic error message.
Another major advantage of using Oracle XML DB to manage XML content is that it leverages the power of Oracle Database to deliver powerful, flexible capabilities for querying and updating XML content, including the following:
Retrieving nodes and fragments within an XML document
Updating nodes and fragments within an XML document
Creating indexes on specific nodes within an XML document
Indexing the entire content of an XML document
Determining whether an XML document contains a particular node
Oracle XML DB includes XMLType methods and XML-specific SQL functions. With these, you can query and update XML content stored in Oracle Database. They use the W3C XPath Recommendation to identify the required node or nodes. Each node in an XML document can be uniquely identified by an XPath expression.
An XPath expression consists of a slash-separated list of element names, attributes names, and XPath functions. XPath expressions can contain positions and conditions that determine which branch of the tree is traversed in determining the target nodes.
By supporting XPath-based methods and functions, Oracle XML DB makes it possible for XML programmers to query and update XML documents in a familiar, standards-compliant manner.
Note:
Oracle SQL functions andXMLType methods respect the W3C XPath recommendation, which states that if an XPath expression targets no nodes when applied to XML data, then an empty sequence must be returned; an error must not be raised.
The specific semantics of an Oracle SQL function or XMLType method that applies an XPath-expression to XML data determines what is returned. For example, SQL function extract returns NULL if its XPath-expression argument targets no nodes, and the updating SQL functions, such as deleteXML, return the input XML data unchanged. An error is never raised if no nodes are targeted, but updating SQL functions may raise an error if an XPath-expression argument targets inappropriate nodes, such as attribute nodes or text nodes.
This section describes techniques for querying Oracle XML DB and retrieving XML content. This section contains these topics:
Retrieving the Content of an XML Document Using Pseudocolumn OBJECT_VALUE
Accessing Fragments or Nodes of an XML Document Using EXTRACT
Accessing Text Nodes and Attribute Values Using XMLCAST and XMLQUERY
Performing SQL Operations on XMLType Fragments with XMLTABLE
Examples in this section are based on the following PurchaseOrder XML document:
Example 3-21 PurchaseOrder XML Instance Document
<PurchaseOrder
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation=
"http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd">
<Reference>SBELL-2002100912333601PDT</Reference>
<Actions>
<Action>
<User>SVOLLMAN</User>
</Action>
</Actions>
<Reject/>
<Requestor>Sarah J. Bell</Requestor>
<User>SBELL</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions>
<name>Sarah J. Bell</name>
<address>400 Oracle Parkway
Redwood Shores
CA
94065
USA</address>
<telephone>650 506 7400</telephone>
</ShippingInstructions>
<SpecialInstructions>Air Mail</SpecialInstructions>
<LineItems>
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
</PurchaseOrder>
The OBJECT_VALUE pseudocolumn can be used as an alias for the value of an object table. For an XMLType table that consists of a single column of XMLType, the entire XML document is retrieved. (OBJECT_VALUE replaces the value(x) and SYS_NC_ROWINFO$ aliases used in releases prior to Oracle Database10g Release 1.)
Example 3-22 Using OBJECT_VALUE to Retrieve an Entire XML Document
In this example, the SQL*Plus settings PAGESIZE and LONG are used to ensure that the entire document is printed correctly, without line breaks. (The output has been formatted for readability.)
SET LONG 10000
SET PAGESIZE 100
SELECT OBJECT_VALUE FROM purchaseorder;
OBJECT_VALUE
-----------------------------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080/source/schemas
/poSource/xsd/purchaseOrder.xsd">
<Reference>SBELL-2002100912333601PDT</Reference>
<Actions>
<Action>
<User>SVOLLMAN</User>
</Action>
</Actions>
<Reject/>
<Requestor>Sarah J. Bell</Requestor>
<User>SBELL</User>
<CostCenter>S30</CostCenter>
<ShippingInstructions>
<name>Sarah J. Bell</name>
<address>400 Oracle Parkway
Redwood Shores
CA
94065
USA</address>
<telephone>650 506 7400</telephone>
</ShippingInstructions>
<SpecialInstructions>Air Mail</SpecialInstructions>
<LineItems>
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="2">
<Description>The Unbearable Lightness Of Being</Description>
<Part Id="37429140222" UnitPrice="29.95" Quantity="2"/>
</LineItem>
<LineItem ItemNumber="3">
<Description>Sisters</Description>
<Part Id="715515011020" UnitPrice="29.95" Quantity="4"/>
</LineItem>
</LineItems>
</PurchaseOrder>
1 row selected.
SQL function extract returns the nodes that match an XPath expression. Nodes are returned as an instance of XMLType. The result of extract can be either a complete document or an XML fragment. The functionality of SQL function extract is also available through XMLType method extract().
Example 3-23 Accessing XML Fragments Using EXTRACT
This query returns an XMLType value containing the Reference element that matches the XPath expression.
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/Reference') FROM purchaseorder; EXTRACT(OBJECT_VALUE, '/PURCHASEORDER/REFERENCE') ------------------------------------------------- <Reference>SBELL-2002100912333601PDT</Reference> 1 row selected.
This query returns an XMLType value containing the first LineItem element in the LineItems collection:
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[1]') FROM purchaseorder; EXTRACT(OBJECT_VALUE, '/PURCHASEORDER/LINEITEMS/LINEITEM[1]') ------------------------------------------------------------- <LineItem ItemNumber="1"> <Description>A Night to Remember</Description> <Part Id="715515009058" UnitPrice="39.95" Quantity="2"/> </LineItem> 1 row selected.
The following query returns an XMLType instance that contains the three Description elements that match the XPath expression. These elements are returned as nodes in a single XMLType, so the XMLType value does not have a single root node. It is treated as an XML fragment.
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem/Description') FROM purchaseorder; EXTRACT(OBJECT_VALUE, '/PURCHASEORDER/LINEITEMS/LINEITEM/DESCRIPTION') ---------------------------------------------------------------------- <Description>A Night to Remember</Description> <Description>The Unbearable Lightness Of Being</Description> <Description>Sisters</Description> 1 row selected.
You can access text node and attribute values using SQL/XML standard functions XMLQuery and XMLCast. To do this, the XPath expression passed to XMLQuery must uniquely identify a single text node or attribute value within the document – that is, a leaf node.
Example 3-24 Accessing a Text Node Value Using XMLCAST
This query returns the value of the text node associated with the Reference element that matches the XPath expression. The value is returned as a VARCHAR2 value.
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Reference/text()'
PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30))
FROM purchaseorder;
XMLCAST(XMLQUERY('$P/PURCHASEO
------------------------------
SBELL-2002100912333601PDT
1 row selected.
The following query returns the value of the text node associated with a Description element contained in a LineItem element. The particular LineItem element is specified by its Id attribute value. The predicate that identifies the LineItem element is [Part/@Id="715515011020"]. The at-sign character (@) specifies that Id is an attribute rather than an element. The value is returned as a VARCHAR2 value.
SELECT XMLCast(
XMLQuery('$p/PurchaseOrder/LineItems/LineItem[Part/@Id="715515011020"]/Description/text()'
PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(30))
FROM purchaseorder;
XMLCAST(XMLQUERY('$P/PURCHASEO
------------------------------
Sisters
1 row selected.
The following query returns the value of the text node associated with the Description element contained in the first LineItem element. The first LineItem element is indicated by the position predicate[1].
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/LineItems/LineItem[1]/Description'
PASSING OBJECT_VALUE AS "p" RETURNING CONTENT)
AS VARCHAR2(4000))
FROM purchaseorder;
XMLCAST(XMLQUERY('$P/PURCHASEORDER/LINEITEMS/LINEITEM[1]/DESCRIPTION'PASSINGOBJECT_VALUEAS"P"
---------------------------------------------------------------------------------------------
A Night to Remember
1 row selected.
See Also:
"Querying XMLType Data with SQL Functions" for information on SQL/XML function XMLCast
Chapter 18, "Using XQuery with Oracle XML DB" for information on SQL/XML function XMLQuery
Example 3-23 demonstrates how SQL function extract returns an XMLType instance containing the node or nodes that match an XPath expression. When the document contains multiple nodes that match the supplied XPath expression, extract returns an XML fragment that contains all of the matching nodes. Unlike an XML document, an XML fragment has no single element that is the root element.
This kind of result is common in these cases:
when extract is used to retrieve the set of elements contained in a collection, in which case all nodes in the fragment are of the same type – see Example 3-25
when the XPath expression ends in a wildcard, in which case the nodes in the fragment can be of different types – see Example 3-27
You can use SQL/XML standard function XMLTable to break up an XML fragment contained in an XMLType instance, inserting the collection-element data into a new, virtual table, which you can then query using SQL — in a join expression, for example. In particular, converting an XML fragment into a virtual table makes it easier to process the result of an extract expression that returns multiple nodes.
Example 3-25 Using XMLTABLE to Access Description Nodes
This example demonstrates how to access the text nodes for each Description element in the PurchaseOrder document.
An initial attempt uses SQL function extractValue. It fails, because there is more than one Description element in the document.
SELECT extractValue(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem/Description')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
SELECT extractValue(OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem/Description')
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
A second attempt uses SQL function extract to access the required values. This returns the set of Description nodes as a single XMLType object that contains a single fragment consisting of the three Description nodes. This is better, but still not very useful, because the objective is to be able to process the text-node values further, using SQL.
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem/Description')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM/DESCRIPTION')
---------------------------------------------------------------------
<Description>A Night to Remember</Description>
<Description>The Unbearable Lightness Of Being</Description>
<Description>Sisters</Description>
1 row selected.
To use SQL to process the contents of the text nodes, we convert the collection of Description nodes into a virtual table, using SQL/XML function XMLTable. The virtual table has three rows, each of which contains a single XMLType instance with a single Description element.
SELECT des.COLUMN_VALUE
FROM purchaseorder p,
XMLTable('/PurchaseOrder/LineItems/LineItem/Description'
PASSING p.OBJECT_VALUE) des
WHERE existsNode(p.OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
COLUMN_VALUE
------------------------------------------------------------
<Description>A Night to Remember</Description>
<Description>The Unbearable Lightness Of Being</Description>
<Description>Sisters</Description>
3 rows selected.
Function XMLTable is passed the XPath expression that targets the Description elements we want. The PASSING clause tells XMLTable to use the contents (OBJECT_VALUE) of XMLType table purchaseorder as the context for evaluating the XPath expression.
This means that the XMLTable expression depends on the purchaseorder table. This is a left lateral join. This correlated join ensures a one-to-many (1:N) relationship between the purchaseorder row accessed and the rows generated from it by XMLTable. Because of this correlated join, the purchaseorder table must appear before the XMLTable expression in the FROM list. This is a general requirement in any situation where the PASSING clause refers to a column of the table.
Since each XMLType instance in the virtual table contains a single Description element, SQL function extractValue can be used to access the value of the text node associated with the each Description element:
SELECT extractValue(des.COLUMN_VALUE, '/Description') FROM purchaseorder p, XMLTable('/PurchaseOrder/LineItems/LineItem/Description' PASSING p.OBJECT_VALUE) des WHERE existsNode(p.OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; EXTRACTVALUE(DES.COLUMN_VALUE,'/DESCRIPTION') --------------------------------------------- A Night to Remember The Unbearable Lightness Of Being Sisters 3 rows selected.
An equivalent but more readable query does away with the need to use extractValue, by using the COLUMNS clause of XMLTable to break up the Description elements into a column named description:
SELECT des.description FROM purchaseorder p, XMLTable('/PurchaseOrder/LineItems/LineItem' PASSING p.OBJECT_VALUE COLUMNS description VARCHAR2(256) PATH 'Description') des WHERE existsNode(p.OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; DESCRIPTION --------------------------------- A Night to Remember The Unbearable Lightness Of Being Sisters 3 rows selected.
The COLUMNS clause here tells XMLTable to break up the data targeted by the XPath expression 'Description' into a column named description of SQL data type VARCHAR2(256). The 'Description' expression defining this column is relative to the context XPath expression, '/PurchaseOrder/LineItems/LineItem'.
In this example, which uses only a single column (description), the gain in readability is perhaps negligible. However, when multiple XPath expressions are used to access different parts of the XMLType instance, it is much more readable to use the COLUMNS clause than extractValue. See, for instance, Example 3-32.
In addition to making queries more readable, use of the COLUMNS clause has the advantage that you can specify more precise SQL data types, which can make static type-checking more helpful.
When you need to expose data contained at multiple levels in an XMLType table as individual rows in a relational view, you apply XMLTable to each document level that needs to be broken up and stored in relational columns. See Example 3-32 for an example.
Example 3-26 Counting the Number of Elements in a Collection Using XMLTABLE
This example counts the number of elements in a collection. It also shows how SQL keywords such as ORDER BY and GROUP BY can be applied to the virtual table data created by SQL function XMLTable.
In this case, the query first locates the set of XML documents that match the XPath argument to SQL function existsNode. It then generates a virtual table containing the set of LineItem nodes for each document selected. Finally, it counts the number of LineItem nodes for each PurchaseOrder document. A correlated join ensures that the GROUP BY correctly determines which LineItem elements belong to which PurchaseOrder element.
SELECT extractValue(p.OBJECT_VALUE, '/PurchaseOrder/Reference'), count(*)
FROM purchaseorder p,
XMLTable('/PurchaseOrder/LineItems/LineItem' PASSING p.OBJECT_VALUE)
WHERE existsNode(p.OBJECT_VALUE, '/PurchaseOrder[User="SBELL"]') = 1
GROUP BY extractValue(p.OBJECT_VALUE, '/PurchaseOrder/Reference')
ORDER BY extractValue(p.OBJECT_VALUE, '/PurchaseOrder/Reference');
EXTRACTVALUE(P.OBJECT_VALUE,'/ COUNT(*)
------------------------------ --------
SBELL-20021009123335280PDT 20
SBELL-20021009123335771PDT 21
SBELL-2002100912333601PDT 3
SBELL-20021009123336231PDT 25
SBELL-20021009123336331PDT 10
SBELL-20021009123336362PDT 15
SBELL-20021009123336532PDT 14
SBELL-20021009123337353PDT 10
SBELL-2002100912333763PDT 21
SBELL-20021009123337673PDT 10
SBELL-20021009123338204PDT 14
SBELL-20021009123338304PDT 24
SBELL-20021009123338505PDT 20
13 rows selected.
Example 3-27 Counting the Number of Child Elements in an Element Using XMLTABLE
This example demonstrates how to use SQL function XMLTable to count the number of child elements of a given element. The XPath expression passed to XMLTable contains a wildcard (*) that matches all elements that are direct descendants of a PurchaseOrder element. Each row of the virtual table created by XMLTable contains a node that matches the XPath expression. Counting the number of rows in the virtual table provides the number of element children of element PurchaseOrder.
SELECT count(*)
FROM purchaseorder p, XMLTable('/PurchaseOrder/*' PASSING p.OBJECT_VALUE)
WHERE existsNode(p.OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
COUNT(*)
----------
9
1 row selected.
SQL/XML standard function XMLExists evaluates whether or not a given document contains a node that matches a W3C XPath expression. Function XMLExists returns a Boolean value of true if the document contains the node specified by the XPath expression supplied to the function and a value of false if it does not. Since XPath expressions can contain predicates, XMLExists can determine whether or not a given node exists in the document, and whether or not a node with the specified value exists in the document. Functionality similar to that provided by SQL/XML function XMLExists is also available through Oracle SQL function existsNode and XMLType method existsNode().
Example 3-28 Searching XML Content Using XMLExists
This query uses SQL/XML function XMLExists to check if the XML document contains an element named Reference that is a child of the root element PurchaseOrder:
SELECT count(*) FROM purchaseorder
WHERE XMLExists('$p/PurchaseOrder/Reference' PASSING OBJECT_VALUE AS "p");
COUNT(*)
----------
132
1 row selected.
This query checks if the value of the text node associated with the Reference element is SBELL-2002100912333601PDT:
SELECT count(*) FROM purchaseorder
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]'
PASSING OBJECT_VALUE AS "p");
COUNT(*)
----------
1
1 row selected.
This query checks if the value of the text node associated with the Reference element is SBELL-XXXXXXXXXXXXXXXXXX:
SELECT count(*) FROM purchaseorder
WHERE XMLExists('$p/PurchaseOrder[Reference="SBELL-XXXXXXXXXXXXXXXXXX"]'
PASSING OBJECT_VALUE AS "p");
COUNT(*)
----------
0
1 row selected.
This query checks if the XML document contains a root element PurchaseOrder that contains a LineItems element that contains a LineItem element that contains a Part element with an Id attribute.
SELECT count(*) FROM purchaseorder
WHERE XMLExists('$p/PurchaseOrder/LineItems/LineItem/Part/@Id'
PASSING OBJECT_VALUE AS "p");
COUNT(*)
----------
132
1 row selected.
This query checks if the XML document contains a root element PurchaseOrder that contains a LineItems element that contains a LineItem element that contains a Part element with Id attribute value 715515009058.
SELECT count(*) FROM purchaseorder
WHERE XMLExists('$p/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]'
PASSING OBJECT_VALUE AS "p");
COUNT(*)
----------
21
This query checks if the XML document contains a root element PurchaseOrder that contains a LineItems element whose third LineItem element contains a Part element with Id attribute value 715515009058.
SELECT count(*) FROM purchaseorder
WHERE XMLExists(
'$p/PurchaseOrder/LineItems/LineItem[3]/Part[@Id="715515009058"]'
PASSING OBJECT_VALUE AS "p");
COUNT(*)
----------
1
1 row selected.
This query limits the results of the SELECT statement to rows where the text node associated with the User element starts with the letter S. XPath 1.0 does not include support for LIKE-based queries.
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Reference' PASSING OBJECT_VALUE AS "p"
RETURNING CONTENT)
AS VARCHAR2(30))
FROM purchaseorder
WHERE XMLCast(XMLQuery('$p/PurchaseOrder/User' PASSING OBJECT_VALUE AS "p"
RETURNING CONTENT)
AS VARCHAR2(30))
LIKE 'S%';
XMLCAST(XMLQUERY('$P/PURCHASEORDER
----------------------------------
SBELL-20021009123336231PDT
SBELL-20021009123336331PDT
SKING-20021009123336321PDT
...
36 rows selected.
This query performs a join based on the values of a node in an XML document and data in another table:
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Reference' PASSING OBJECT_VALUE AS "p"
RETURNING CONTENT)
AS VARCHAR2(30))
FROM purchaseorder p, hr.employees e
WHERE XMLCast(XMLQuery('$p/PurchaseOrder/User' PASSING OBJECT_VALUE AS "p"
RETURNING CONTENT)
AS VARCHAR2(30)) = e.email
AND e.employee_id = 100;
XMLCAST(XMLQUERY('$P/PURCHASEOREDER
-----------------------------------
SKING-20021009123336321PDT
SKING-20021009123337153PDT
SKING-20021009123335560PDT
SKING-20021009123336952PDT
SKING-20021009123336622PDT
SKING-20021009123336822PDT
SKING-20021009123336131PDT
SKING-20021009123336392PDT
SKING-20021009123337974PDT
SKING-20021009123338294PDT
SKING-20021009123337703PDT
SKING-20021009123337383PDT
SKING-20021009123337503PDT
13 rows selected.
See Also:
"Querying XMLType Data with SQL Functions" for information on SQL/XML functions XMLCast and XMLExists
Chapter 18, "Using XQuery with Oracle XML DB" for information on SQL/XML function XMLQuery
The examples in the preceding section demonstrate how you can use SQL/XML function XMLExists in a SELECT list to return information that is contained in an XML document. You can also use XMLExists in a WHERE clause, to determine whether or not a document must be included in the result set of a SELECT, UPDATE, or DELETE statement.
Example 3-29 shows how to use XMLExists to restrict the result set to documents containing nodes that match an XPath expression.
Example 3-29 Limiting the Results of a SELECT Using XMLExists in a WHERE Clause
This query limits the results of the SELECT statement to rows where the text node associated of the User element contains the value SBELL.
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Reference' PASSING OBJECT_VALUE AS "p"
RETURNING CONTENT)
AS VARCHAR2(30)) "Reference"
FROM purchaseorder
WHERE XMLExists('$p/PurchaseOrder[User="SBELL"]' PASSING OBJECT_VALUE AS "p");
Reference
------------------------------
SBELL-20021009123336231PDT
SBELL-20021009123336331PDT
SBELL-20021009123337353PDT
SBELL-20021009123338304PDT
SBELL-20021009123338505PDT
SBELL-20021009123335771PDT
SBELL-20021009123335280PDT
SBELL-2002100912333763PDT
SBELL-2002100912333601PDT
SBELL-20021009123336362PDT
SBELL-20021009123336532PDT
SBELL-20021009123338204PDT
SBELL-20021009123337673PDT
13 rows selected.
Example 3-30 Finding the Reference for any PurchaseOrder Using XMLQuery and XMLExists
This example uses SQL/XML functions XMLQuery and XMLExists to find the Reference element for any PurchaseOrder element whose first LineItem element contains an order for the item with Id 715515009058. Function XMLExists is used in the WHERE clause to determine which rows are selected, and XMLQuery is used in the SELECT list to control which part of the selected documents appears in the result.
SELECT XMLCast(XMLQuery('$p/PurchaseOrder/Reference' PASSING OBJECT_VALUE AS "p"
RETURNING CONTENT)
AS VARCHAR2(30)) "Reference"
FROM purchaseorder
WHERE XMLExists('$p/PurchaseOrder/LineItems/LineItem[1]/Part[@Id="715515009058"]'
PASSING OBJECT_VALUE AS "p");
Reference
-------------------------
SBELL-2002100912333601PDT
1 row selected.
See Also:
"Querying XMLType Data with SQL Functions" for information on SQL/XML functions XMLCast and XMLExists
Chapter 18, "Using XQuery with Oracle XML DB" for information on SQL/XML function XMLQuery
The XML-specific functions and methods provided by Oracle XML DB can be used to create conventional relational views that provide relational access to XML content. This lets programmers, tools, and applications that understand Oracle Database, but not XML, to work with XML content stored in the database.
The relational views can use XPath expressions and SQL functions such as extractValue and XMLTable to define a mapping between columns in the view and nodes in the XML document. For performance reasons, this approach is recommended only when XML documents are stored using structured (object-relational) or binary XML storage, not when stored as CLOB instances.
See Also:
Chapter 4, "XMLType Operations" for a description of XMLType data type and functions
http://www.w3.org/TR/xpath for information about XPath 1.0
http://www.w3.org/TR/xpath20/ for information about XPath 2.0
http://www.w3.org/TR/2002/NOTE-unicode-xml-20020218/ for information about using Unicode in XML
When you need to expose each document in an XMLType table as a row in a relational view, you can use this technique:
Define the set of columns that make up the view, using CREATE OR REPLACE VIEW.
Map the nodes in the XML document to the columns defined by the view. You do this by extracting the nodes, using SQL function extractValue with appropriate XPath expressions.
This technique can be used whenever there is a one-to-one (1:1) relationship between documents in the XMLType table and the rows in the view.
Example 3-31 Creating a Relational View On XML Content
This example shows how to create a simple relational view that exposes XML content:
CREATE OR REPLACE VIEW
purchaseorder_master_view(reference, requestor, userid, costcenter,
ship_to_name, ship_to_address, ship_to_phone,
instructions)
AS SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference'),
extractValue(OBJECT_VALUE, '/PurchaseOrder/Requestor'),
extractValue(OBJECT_VALUE, '/PurchaseOrder/User'),
extractValue(OBJECT_VALUE, '/PurchaseOrder/CostCenter'),
extractValue(OBJECT_VALUE,
'/PurchaseOrder/ShippingInstructions/name'),
extractValue(OBJECT_VALUE,
'/PurchaseOrder/ShippingInstructions/address'),
extractValue(OBJECT_VALUE,
'/PurchaseOrder/ShippingInstructions/telephone'),
extractValue(OBJECT_VALUE, '/PurchaseOrder/SpecialInstructions')
FROM purchaseorder;
View created.
DESCRIBE purchaseorder_master_view
Name Null? Type
--------------------------------------------
REFERENCE VARCHAR2(30 CHAR)
REQUESTOR VARCHAR2(128 CHAR)
USERID VARCHAR2(10 CHAR)
COSTCENTER VARCHAR2(4 CHAR)
SHIP_TO_NAME VARCHAR2(20 CHAR)
SHIP_TO_ADDRESS VARCHAR2(256 CHAR)
SHIP_TO_PHONE VARCHAR2(24 CHAR)
INSTRUCTIONS VARCHAR2(2048 CHAR)
This example creates relational view purchaseorder_master_view. There is one row in the view for each row in table purchaseorder.
When you need to expose data contained at multiple levels in an XMLType table as individual rows in a relational view, you use the same general approach as for breaking up a single level: 1) define the columns making up the view, and 2) map the XML nodes to the columns. However, in this case it is best to use SQL/XML standard function XMLTable, applying it to each document level that needs to be broken up and stored in relational columns.
This technique can be used whenever there is a one-to-many (1:N) relationship between documents in the XMLType table and the rows in the view.
For example, each PurchaseOrder element contains a LineItems element, which in turn contains one or more LineItem elements. Each LineItem element has child elements, such as Description, and an ItemNumber attribute. To make such lower-level data accessible as a relational value, you must break up both the PurchaseOrder element and the LineItem collection. Each such decomposition is done with XMLTable. When element PurchaseOrder is broken up, the LineItem element is mapped to a relational column of type XMLType, which contains an XML fragment. That column is then passed to the second call to XMLType, to be broken into its various parts as multiple rows of relational values. See Example 3-32.
Example 3-32 Using a View to Access Individual Members of a Collection
This example shows how to use SQL function XMLTable for a one-to-many (1:N) relationship between the documents in XMLType table purchaseorder and the view rows. The view provides access to the individual members of a collection, and exposes the collection members as a set of rows.
CREATE OR REPLACE VIEW purchaseorder_detail_view AS
SELECT po.reference, li.*
FROM purchaseorder p,
XMLTable('/PurchaseOrder' PASSING p.OBJECT_VALUE
COLUMNS
reference VARCHAR2(30) PATH 'Reference',
lineitem XMLType PATH 'LineItems/LineItem') po,
XMLTable('LineItem' PASSING po.lineitem
COLUMNS
itemno NUMBER(38) PATH '@ItemNumber',
description VARCHAR2(256) PATH 'Description',
partno VARCHAR2(14) PATH 'Part/@Id',
quantity NUMBER(12, 2) PATH 'Part/@Quantity',
unitprice NUMBER(8, 4) PATH 'Part/@UnitPrice') li;
View created.
DESCRIBE purchaseorder_detail_view
Name Null? Type
----------------------------
REFERENCE VARCHAR2(30 CHAR)
ITEMNO NUMBER(38)
DESCRIPTION VARCHAR2(256 CHAR)
PARTNO VARCHAR2(14 CHAR)
QUANTITY NUMBER(12,2)
UNITPRICE NUMBER(8,4)
There is one row in view purchaseorder_detail_view for each LineItem element in the XML documents stored in XMLType table purchaseorder.
The CREATE OR REPLACE VIEW statement defines the set of columns that make up the view. The SELECT statement passes the purchaseorder table as context to function XMLTable, to create the virtual table p, which has columns reference and lineitem. These columns contain the Reference and LineItem elements of the purchase-order documents, respectively.
Column lineitem contains a collection of LineItem elements, as an XMLType instance — one row for each LineItem element. These rows are, in turn, passed to a second XMLTable expression, to serve as its context. This second XMLTable expression creates a virtual table of line-item rows, with columns corresponding to various descendant nodes of element LineItem. Most of these descendants are attributes (ItemNumber, Part/@Id, and so on); one of them is the Description child element.
The Reference element is included in view purchaseorder_detail_view as column reference. It provides a foreign key that can be used to joins rows in view purchaseorder_detail_view to the corresponding row in view purchaseorder_master_view. The correlated join in the CREATE VIEW statement ensures that the one-to-many (1:N) relationship between the Reference element and the associated LineItem elements is maintained whenever the view is accessed.
The examples in this section show relational queries of XML data. They point out some of the benefits provided by creating relational views over XMLType tables and columns.
Example 3-33 SQL queries on XML Content Using Views
This example uses a simple query against the master view. A conventional SELECT statement selects rows where the userid column starts with S.
SELECT reference, costcenter, ship_to_name FROM purchaseorder_master_view WHERE userid LIKE 'S%'; REFERENCE COST SHIP_TO_NAME ------------------------------ ---- -------------- SBELL-20021009123336231PDT S30 Sarah J. Bell SBELL-20021009123336331PDT S30 Sarah J. Bell SKING-20021009123336321PDT A10 Steven A. King ... 36 rows selected.
The following query is based on a join between the master view and the detail view. A conventional SELECT statement finds the purchaseorder_detail_view rows where the value of the itemno column is 1 and the corresponding purchaseorder_master_view row contains a userid column with the value SBELL.
SELECT d.reference, d.itemno, d.partno, d.description
FROM purchaseorder_detail_view d, purchaseorder_master_view m
WHERE m.reference = d.reference
AND m.userid = 'SBELL'
AND d.itemno = 1;
REFERENCE ITEMNO PARTNO DESCRIPTION
------------------------------ ------------------------------------------------
SBELL-20021009123336231PDT 1 37429165829 Juliet of the Spirits
SBELL-20021009123336331PDT 1 715515009225 Salo
SBELL-20021009123337353PDT 1 37429141625 The Third Man
SBELL-20021009123338304PDT 1 715515009829 Nanook of the North
SBELL-20021009123338505PDT 1 37429122228 The 400 Blows
SBELL-20021009123335771PDT 1 37429139028 And the Ship Sails on
SBELL-20021009123335280PDT 1 715515011426 All That Heaven Allows
SBELL-2002100912333763PDT 1 715515010320 Life of Brian - Python
SBELL-2002100912333601PDT 1 715515009058 A Night to Remember
SBELL-20021009123336362PDT 1 715515012928 In the Mood for Love
SBELL-20021009123336532PDT 1 37429162422 Wild Strawberries
SBELL-20021009123338204PDT 1 37429168820 Red Beard
SBELL-20021009123337673PDT 1 37429156322 Cries and Whispers
13 rows selected.
Because the views look and act like standard relational views they can be queried using standard relational syntax. No XML-specific syntax is required in either the query or the generated result set.
By exposing XML content as relational data, Oracle XML DB lets advanced database features, such as business intelligence and analytic capabilities, be applied to XML content. Even though the business intelligence features themselves are not XML-aware, the XML-SQL duality provided by Oracle XML DB lets these features be applied to XML content.
Example 3-34 Querying XML Using Views of XML Content
This example demonstrates how to use relational views over XML content to perform business-intelligence queries on XML documents. The query selects PurchaseOrder documents that contain orders for titles identified by UPC codes 715515009058 and 715515009126.
SELECT partno, count(*) "No of Orders", quantity "No of Copies"
FROM purchaseorder_detail_view
WHERE partno IN (715515009126, 715515009058)
GROUP BY rollup(partno, quantity);
PARTNO No of Orders No of Copies
-------------- ------------ ------------
715515009058 7 1
715515009058 9 2
715515009058 5 3
715515009058 2 4
715515009058 23
715515009126 4 1
715515009126 7 3
715515009126 11
34
9 rows selected.
The query determines the number of copies of each title that are ordered in each PurchaseOrder document. For part number 715515009126, there are four PurchaseOrder documents where one copy of the item is ordered and seven PurchaseOrder documents where three copies of the item are ordered.
Oracle XML DB lets update operations take place on XML content. Update operations can either replace the entire contents of a document or parts of a document. The ability to perform partial updates on XML documents is very powerful, particularly when you make small changes to large documents, as it can significantly reduce the amount of network traffic and disk input-output required to perform the update.
SQL function updateXML enables partial update of an XML document stored as an XMLType instance. It lets multiple changes be made to the document in a single operation. Each change consists of an XPath expression that identifies a node to be updated, and the new value for the node.
Example 3-35 Updating XML Content Using UPDATEXML
This example uses SQL function updateXML to update the text node associated with the User element.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
EXTRACTVAL
----------
SBELL
1 row selected.
UPDATE purchaseorder
SET OBJECT_VALUE =
updateXML(OBJECT_VALUE, '/PurchaseOrder/User/text()','SKING')
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
1 row updated.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/User')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
EXTRACTVAL
----------
SKING
1 row selected.
Example 3-36 Replacing an Entire Element Using UPDATEXML
This example uses SQL function updateXML to replace an entire element within an XML document. The XPath expression references the element, and the replacement value is passed as an XMLType object.
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[1]')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[1]')
------------------------------------------------------------
<LineItem ItemNumber="1">
<Description>A Night to Remember</Description>
<Part Id="715515009058" UnitPrice="39.95" Quantity="2"/>
</LineItem>
1 row selected.
UPDATE purchaseorder
SET OBJECT_VALUE =
updateXML(
OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem[1]',
XMLType('<LineItem ItemNumber="1">
<Description>The Lady Vanishes</Description>
<Part Id="37429122129" UnitPrice="39.95" Quantity="1"/>
</LineItem>'))
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
1 row updated.
SELECT extract(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem[1]')
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
EXTRACT(OBJECT_VALUE, '/PURCHASEORDER/LINEITEMS/LINEITEM[1]')
-------------------------------------------------------------
<LineItem ItemNumber="1">
<Description>The Lady Vanishes</Description>
<Part Id="37429122129" UnitPrice="39.95" Quantity="1"/>
</LineItem>
1 row selected.
Example 3-37 Incorrectly Updating a Node That Occurs Multiple Times In a Collection
This example show a common error that occurs when using SQL function updateXML to update a node that occurs multiple times in a collection. The UPDATE statement sets the value of the text node of a Description element to "The Wizard of Oz", where the current value of the text node is "Sisters". The statement includes an existsNode expression in the WHERE clause that identifies the set of nodes to be updated.
SELECT extractValue(des.COLUMN_VALUE, '/Description')
FROM purchaseorder p,
XMLTable('/PurchaseOrder/LineItems/LineItem/Description'
PASSING p.OBJECT_VALUE) des
WHERE existsNode(p.OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
EXTRACTVALUE(DES.COLUMN_VALUE,'/DESCRIPTION')
---------------------------------------------
The Lady Vanishes
The Unbearable Lightness Of Being
Sisters
3 rows selected.
UPDATE purchaseorder p
SET p.OBJECT_VALUE =
updateXML(p.OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem/Description/text()',
'The Wizard of Oz')
WHERE existsNode(p.OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem[Description="Sisters"]')
= 1
AND existsNode(p.OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
1 row updated.
SELECT extractValue(des.COLUMN_VALUE, '/Description')
FROM purchaseorder p,
XMLTable('/PurchaseOrder/LineItems/LineItem/Description'
PASSING p.OBJECT_VALUE) des
WHERE existsNode(p.OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
EXTRACTVALUE(DES.COLUMN_VALUE,'/DESCRIPTION')
---------------------------------------------
The Wizard of Oz
The Wizard of Oz
The Wizard of Oz
3 rows selected.
Instead of updating the required node, SQL function updateXML updates the values of all text nodes that belong to the Description element. This is the correct behavior, but it is not what was intended. A WHERE clause can be used only to identify which documents must be updated, not which nodes within a document must be updated.
After the document has been selected, the XPath expression passed to updateXML determines which nodes within the document must be updated. In this case, the XPath expression identifies all three Description nodes, so all three of the associated text nodes were updated. See Example 3-38 for the correct way to update the nodes.
Example 3-38 Correctly Updating a Node That Occurs Multiple Times In a Collection
To correctly use SQL function updateXML to update a node that occurs multiple times within a collection, use the XPath expression passed to updateXML to identify which nodes in the XML document to update. By introducing the appropriate predicate into the XPath expression, you can limit which nodes in the document are updated. This example shows the correct way of updating one node within a collection:
SELECT extractValue(des.COLUMN_VALUE, '/Description')
FROM purchaseorder p,
XMLTable('/PurchaseOrder/LineItems/LineItem/Description'
PASSING p.OBJECT_VALUE) des
WHERE existsNode(p.OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
EXTRACTVALUE(P.OBJECT_VALUE,'/DESCRIPTION')
-------------------------------------------
A Night to Remember
The Unbearable Lightness Of Being
Sisters
3 rows selected.
UPDATE purchaseorder p
SET p.OBJECT_VALUE =
updateXML(
p.OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem/Description[text()="Sisters"]/text()',
'The Wizard of Oz')
WHERE existsNode(p.OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')
= 1;
1 row updated.
SELECT extractValue(des.COLUMN_VALUE, '/Description')
FROM purchaseorder p,
XMLTable('/PurchaseOrder/LineItems/LineItem/Description'
PASSING p.OBJECT_VALUE) des
WHERE existsNode(p.OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
EXTRACTVALUE(DES.COLUMN_VALUE,'/DESCRIPTION')
---------------------------------------------
A Night to Remember
The Unbearable Lightness Of Being
The Wizard of Oz
3 rows selected.
Example 3-39 Changing Text Node Values Using UPDATEXML
SQL function updateXML lets multiple changes be made to the document in one statement. This example shows how to change the values of text nodes belonging to the User and SpecialInstructions elements in one statement.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/CostCenter') "Cost Center",
extractValue(OBJECT_VALUE,
'/PurchaseOrder/SpecialInstructions') "Instructions"
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
Cost Center Instructions
------------ ------------
S30 Air Mail
1 row selected.
This single UPDATE SQL statement changes the User and SpecialInstructions element text node values:
UPDATE purchaseorder
SET OBJECT_VALUE =
updateXML(OBJECT_VALUE,
'/PurchaseOrder/CostCenter/text()',
'B40',
'/PurchaseOrder/SpecialInstructions/text()',
'Priority Overnight Service')
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
1 row updated.
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/CostCenter') "Cost Center",
extractValue(OBJECT_VALUE,
'/PurchaseOrder/SpecialInstructions') "Instructions"
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
Cost Center Instructions
------------ --------------------------
B40 Priority Overnight Service
1 row selected.
The way SQL functions such as updateXML modify an XML document depends on how the XML document is stored and whether it is based on an XML schema:
XML documents stored in CLOB values – When a SQL function such as updateXML modifies an XML document stored as a CLOB (whether or not it is XML schema-based), Oracle XML DB performs the update by creating a Document Object Model (DOM) from the document and using DOM API methods to modify the appropriate XML data. After modification, the updated DOM is returned back to the underlying CLOB value.
XML documents stored object-relationally – When a SQL function such as updateXML modifies an XML schema-based document that is stored object-relationally, Oracle XML DB can use XPath rewrite to modify the underlying objects in place. This is a partial update, which translates the XPath argument to the SQL function into an equivalent SQL operation. The SQL operation then directly modifies the attributes of underlying objects. Such a partial update can be much quicker than a DOM-based update. This can improve performance significantly when executing SQL code that applies a SQL function such as updateXML to a large number of documents.
XML documents stored as binary XML – When SQL function updateXML is used on a binary XML column, Oracle XML DB often need not build a DOM. The exact portion of the document that needs to be updated is calculated using query evaluation techniques such as streaming and XMLIndex. The updated data is written to disk starting only where the first change occurs — anything before that is unchanged. In addition, if SecureFile LOBs are used for storing the data, then the change is applied in a sliding manner, without causing the rest of the LOB to be rewritten. That is, with SecureFile LOB storage of binary XML data, only the data that is actually changed is updated. This can significantly improve performance relative to unstructured storage. These optimizations apply to both non-schema-based and XML schema-based data.
See Also:
Chapter 7, "XPath Rewrite"Namespace support is a key feature of the W3C XML Recommendations. Oracle XML DB fully supports the W3C Namespace Recommendation. All XMLType methods and XML-specific SQL functions work with XPath expressions that include namespace prefixes. All methods and functions accept an optional namespace argument that provides the namespace declarations for correctly resolving namespace prefixes used in XPath expressions. The namespace parameter is required whenever the provided XPath expression contains namespace prefixes. When parameter namespace is provided, it must provide an explicit declaration for the default namespace in addition to the prefixed namespaces, unless the default namespace is the noNamespace namespace. When parameter namespace is not provided, Oracle XML DB makes the following assumptions about the XPath expression:
If the content of the XMLType instance is not based on a registered XML schema, then any term in the XPath expression that does include a namespace prefix is assumed to be in the noNamespace namespace.
If the content of the XMLType is based on a registered XML schema, then any term in the XPath expression that does not include a namespace prefix is assumed to be in the targetNamespace declared by the XML schema, if any. If the XML schema does not declare a targetnamespace, then names noNamespace is used.
Failing to correctly define the namespaces required to resolve XPath expressions results in XPath-based operations not working as expected. When the namespace declarations are incorrect or missing, the result of the operation is normally null, rather than an error. To avoid confusion, whenever any namespaces other than noNamespace are present in either the XPath expression or the target XML document, pass the complete set of namespace declarations, including the declaration for the default namespace.
Oracle XML DB processes SQL functions such as extract, extractValue, and existsNode — and their equivalent XMLType methods — using DOM-based or SQL-based techniques:
DOM-based XMLType processing – Oracle XML DB performs the required processing by constructing a DOM from the contents of the XMLType object. It uses methods provided by the DOM API to perform the required operation on the DOM. If the operation involves updating the DOM tree, then the entire XML document has to be written back to disk when the operation is completed. The process of using DOM-based operations on XMLType data is referred to as functional evaluation.
The advantage of functional evaluation is that it can be used regardless of the storage model (structured, binary XML, or unstructured) used for the XMLType instance. The disadvantage of functional evaluation is that it much more expensive than XPath rewrite, and does not scale across large numbers of XML documents.
SQL-based XMLType processing – Oracle XML DB constructs a SQL statement that performs the processing required to complete the function or method. The SQL statement works directly against the object-relational data structures that underlie a schema-based XMLType. This process is referred to as XPath rewrite. See Chapter 7, "XPath Rewrite".
The advantage of XPath rewrite is that it lets Oracle XML DB evaluate XPath-based SQL functions and methods at near relational speeds. This lets these operations scale across large numbers of XML documents. The disadvantage of XPath rewrite is that since it relies on direct access and updating the objects used to store the XML document, it can be used only when the XMLType instance is stored using XML schema-based object-relational storage techniques.
Streaming evaluation of binary XML data – If you use binary XML as the storage model, then XPath expressions used in SQL functions such as XMLQuery, XMLTable, XMLExists, XMLCast, extract, and extractValue are evaluated in a streaming fashion, without recourse to building a DOM.
XPath rewrite improves the performance of SQL statements containing XPath-based functions by converting the functions into conventional relational SQL statements. This insulates the database optimizer from having to understand the XPath notation and the XML data model. The database optimizer processes the rewritten SQL statement in the same manner as any other SQL statement. In this way, it can derive an execution plan based on conventional relational algebra. This results in the execution of SQL statements with XPath-based functions with near relational performance.
For XPath rewrite to take place the following conditions must be satisfied:
The XMLType column or table must be stored using structured (object-relational) storage techniques.
The XMLType column or table containing the XML documents must be based on a registered XML schema.
It must be possible to map the nodes referenced by the XPath expression to attributes of the underlying SQL object model.
Understanding the concept of XPath rewrite and the conditions under which XPath rewrite takes place is key to developing Oracle XML DB applications that deliver satisfactory levels of scalability and performance.
See Also:
Chapter 7, "XPath Rewrite"XPath rewrite on its own cannot guarantee scalable and performant applications. The performance of SQL statements generated by XPath rewrite is ultimately determined by the available indexes and the way data is stored on disk. Also, as with any other SQL application, a DBA must monitor the database and optimize storage and indexes if the application is to perform well.
The good news, from a DBA perspective, is that this information is nothing new. The same skills are required to tune an XML application as for any other database application. All of the tools that DBAs typically use with SQL-based applications can be applied to XML-based applications using Oracle XML DB functions.
Example 3-40 Using EXPLAIN PLAN to Analyze the Selection of PurchaseOrders
This example shows how to use an EXPLAIN PLAN to look at the execution plan for selecting the set of PurchaseOrders created by user SBELL.
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') "Reference"
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[User="SBELL"]') = 1;
Explained.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 713050960
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDER | 1 | 24 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PURCHASEORDER_USER_INDEX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PURCHASEORDER"."SYS_NC00022$"='SBELL')
Note
-----
- dynamic sampling used for this statement
18 rows selected.
Oracle XML DB supports the creation of the following kinds of index on XML content:
XMLIndex indexes (unstructured and binary XML storage only)
B-tree indexes other than function-based (structured storage only)
Function-based indexes
Oracle Text-based indexes (CONTEXT)
XMLIndex indexes can be created on XML fragments that are stored in CLOB instances, even when the rest of the XML document is stored object-relationally — this is sometimes called hybrid storage. XMLIndex indexes do not require you to know in advance which XPath expressions you will use in queries. XPath rewrite applies only to structured storage, and XMLIndex does not apply to structured storage.
With structured storage of an XMLType table or column, B-tree indexes can be created on the underlying SQL types. Oracle Text-based indexes and function-based indexes can be created on any XMLType table or column, no matter how it is stored.
With structured storage, XPath-rewrite analysis determines whether it is possible to map the nodes referenced in the XPath expression used in a CREATE INDEX statement to object attributes of the underlying SQL data types. If so, then a B-tree index is created on the underlying SQL objects. Otherwise, a function-based index is created. Function-based indexes are typically based on SQL function extractValue, although you sometimes base them on other functions, such as existsNode.
Example 3-41 Creating an Index on a Text Node
This example shows creation of index purchaseorder_user_index on the value of the User element text node. Table purchaseorder, in standard database schema OE, is stored object-relationally.
CREATE INDEX purchaseorder_user_index ON purchaseorder (extractValue(OBJECT_VALUE, '/PurchaseOrder/User'));
At first glance, the index appears to be function-based (based on function extractValue). However, because the XMLType table being indexed is stored object-relationally, XPath-rewrite analysis determines that a B-tree index can be created on the underlying SQL data types. In this example, the index is created on the userid attribute of the purchaseorder_t object.
Example 3-42 shows the EXPLAIN PLAN that is generated when the query used in Example 3-40 is executed after the index has been created as in Example 3-41. The query plan makes use of the newly created B-tree index, and is much more scalable than the query plan of Example 3-40.
Example 3-42 Explain Plan Showing Use of a B-Tree Index
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') "Reference"
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[User="SBELL"]') = 1;
Explained.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 713050960
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDER | 1 | 24 | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | PURCHASEORDER_USER_INDEX | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("PURCHASEORDER"."SYS_NC00022$"='SBELL')
18 rows selected.
Most XML documents contain collections of repeating elements. For Oracle XML DB to efficiently process the collection members, it is important that the storage model for managing the collection provide an efficient way of accessing the individual members of the collection. The storage model used determines whether it is possible to index individual elements within the collection and perform direct operations on them.
If a collection is stored as an ordered collection table or an XMLType instance, then you can directly access members of the collection. Each member of the collection becomes a row in a table, so you can access it directly with SQL.
If a collection is stored as a LOB, then you cannot directly access members of the collection. If a collection is stored as XML text in a CLOB value, then any operation on it requires parsing the CLOB contents and then using functional evaluation to perform the required operation. This is costly, in terms of performance.
If you convert a collection into a set of SQL objects that are serialized into a LOB, then this removes the parsing cost. However, the collection must still be loaded into memory before any operations on individual collection members can be performed.
Example 3-43 shows the execution plan for a query to find the Reference elements in documents that contain an order for part number 717951002372 (Part element with an Id attribute of value 717951002372). The collection of LineItem elements is stored as rows in the ordered collection table lineitem_table.
Example 3-43 EXPLAIN PLAN for a Selection of Collection Elements
EXPLAIN PLAN FOR
SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') "Reference"
FROM purchaseorder
WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem/Part[@Id="717951002372"]') = 1;
Explained.
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------
Plan hash value: 28173485
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 966 | 20 (10)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI | | 21 | 966 | 20 (10)| 00:00:01 |
| 2 | TABLE ACCESS FULL | PURCHASEORDER | 132 | 3564 | 5 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL | LINEITEM_TABLE | 22 | 418 | 14 (8)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$")
3 - filter("SYS_NC00011$"='717951002372')
Note
-----
- dynamic sampling used for this statement
20 rows selected.
The execution plan shows a full scan of ordered collection table lineitem_table. This might be acceptable if there are only a few hundred documents in the purchaseorder table, but it would be unacceptable if there were thousands or millions of documents in the table.
To improve the performance of such a query, you can create an index that provides direct access to pseudocolumn NESTED_TABLE_ID, given the value of attribute Id. Unfortunately, Oracle XML DB does not allow indexes on collections to be created using XPath expressions directly. To create the index, you must understand the structure of the SQL object that is used to manage the LineItem elements. Given this information, you can create the required index using conventional object-relational SQL.
In this case, element LineItem is stored as an instance of object type lineitem_t. Element Part is stored as an instance of SQL data type part_t. XML attribute Id is mapped to object attribute part_number. Given this information, you can create a composite index on attribute part_number and pseudocolumn NESTED_TABLE_ID, as shown in Example 3-44. This index provides direct access to those purchase-order documents that have LineItem elements that reference the required part.
Example 3-44 Creating an Index for Direct Access to an Ordered Collection Table
CREATE INDEX lineitem_part_index ON lineitem_table l (l.part.part_number, l.NESTED_TABLE_ID); Index created. EXPLAIN PLAN FOR SELECT extractValue(OBJECT_VALUE, '/PurchaseOrder/Reference') "Reference" FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder/LineItems/LineItem/Part[@Id="717951002372"]') = 1; Explained. PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------- Plan hash value: 1849679771 ------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 22 | 1012 | 4 (25)| 00:00:01 | | 1 | NESTED LOOPS | | | | | | | 2 | NESTED LOOPS | | 22 | 1012 | 4 (25)| 00:00:01 | | 3 | SORT UNIQUE | | 22 | 418 | 2 (0)| 00:00:01 | |* 4 | INDEX RANGE SCAN | LINEITEM_PART_INDEX | 22 | 418 | 2 (0)| 00:00:01 | |* 6 | INDEX UNIQUE SCAN | LINEITEM_TABLE_MEMBERS | 1 | | 0 (0)| 00:00:01 | |* 7 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDER | 1 | 530 | 1 (0)| 00:00:01 | ------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - access("SYS_NC00011$"='717951002372') 5 - access("NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$") 19 rows selected.
The EXPLAIN PLAN output shows that the same query as in Example 3-44 now makes use of the newly created index. The query is resolved by using index lineitem_part_index to determine which documents in table purchaseorder satisfy the condition in the XPath-expression argument to function existsNode.
The query is now much more scalable, with no change to its syntax. XPath rewrite lets the optimizer analyze the query, and this analysis determines that indexes purchaseorder_user_index and lineitem_part_index provide a more efficient way to resolve the queries.
The EXPLAIN PLAN output for a query on an XMLType table created as a result of calling PL/SQL procedure DBMS_XMLSCHEMA.register_schema contains a filter similar to the following:
3 - filter(SYS_CHECKACL("ACLOID","OWNERID",xmltype(''<privilege xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xsd DAV:http://xmlns.oracle.com/xdb/dav.xsd"> <read-properties/><read-contents/></privilege>''))=1)
This shows that ACL-based security is implemented for this table. In this example, the filter checks that the user performing the SQL query has read-contents privilege on each of the documents to be accessed.
Oracle XML DB Repository uses an ACL-based security mechanism that provides control of access to XML content document by document, rather than only table by table. When XML content is accessed using a SQL statement, a call to sys_checkACL is automatically added to the WHERE clause to ensure that the security defined is enforced at the SQL level.
However, enforcing ACL-based security adds overhead to the SQL query. If ACL-based security is not required, use procedure disable_hierarchy in package DBMS_XDBZ to turn off ACL checking. After calling this procedure, the sys_checkACL filter no longer appears in the output generated by EXPLAIN PLAN.
See Also:
Oracle Database PL/SQL Packages and Types Reference for information about procedureDBMS_XDBZ.disable_hierarchyExample 3-45 shows the kind of EXPLAIN PLAN output that is generated when Oracle XML DB cannot perform XPath rewrite. Function existsNode appears in the output (line 3), indicating that the query is not rewritten.
Example 3-45 EXPLAIN PLAN Generated When XPath Rewrite Does Not Occur
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NESTED_TABLE_ID"=:B1)
2 - access("NESTED_TABLE_ID"=:B1)
3 - filter(EXISTSNODE(SYS_MAKEXML('C0A5497E8DCF110BE034080020E5CF39',
3044, "SYS_ALIAS_4". "XMLEXTRA",
"SYS_ALIAS_4"."XMLDATA"),
'/PurchaseOrder[User="SBELL"]')
=1)
5 - access("NESTED_TABLE_ID"=:B1)
6 - access("NESTED_TABLE_ID"=:B1)
In this situation, Oracle XML DB constructs a pre-filtered result set based on any other conditions specified in the query WHERE clause. It then filters the rows in this potential result set to determine which rows belong in the result set. The filtering is performed by constructing a DOM on each document and performing a functional evaluation (using the methods defined by the DOM API) to determine whether or not each document is a member of the result set.
Performance can be poor when there are many documents in the potential result set. However, when the use of additional predicates in the WHERE clause leads to a small number of documents in the potential result set, this may be not be a problem.
XMLType and XPath abstractions make it possible for you to develop applications that are independent of the underlying storage technology. As in conventional relational applications, creating and dropping indexes makes it possible to tune the performance of an application without having to rewrite it.
Oracle XML DB provides a number of ways to generate XML from relational data. The most powerful and flexible method is based on the evolving SQL/XML standard. This ANSI standard defines a set of SQL functions that allow XML to be generated directly from a SELECT statement. Using these functions, a query can generate one or more XML documents, rather than a traditional tabular result set. The SQL/XML standard functions allow almost any shape of XML data to be generated. These functions include the following:
XMLElement creates a element
XMLAttributes adds attributes to an element
XMLForest creates forest of elements
XMLAgg creates a single element from a collection of elements
Example 3-46 Using SQL/XML Functions to Generate XML
This query generates an XML document that contains information from the tables departments, locations, countries, employees, and jobs:
SELECT XMLElement(
"Department",
XMLAttributes(d.Department_id AS "DepartmentId"),
XMLForest(d.department_name AS "Name"),
XMLElement(
"Location",
XMLForest(street_address AS "Address",
city AS "City",
state_province AS "State",
postal_code AS "Zip",
country_name AS "Country")),
XMLElement(
"EmployeeList",
(SELECT XMLAgg(
XMLElement(
"Employee",
XMLAttributes(e.employee_id AS "employeeNumber"),
XMLForest(
e.first_name AS "FirstName",
e.last_name AS "LastName",
e.email AS "EmailAddress",
e.phone_number AS "PHONE_NUMBER",
e.hire_date AS "StartDate",
j.job_title AS "JobTitle",
e.salary AS "Salary",
m.first_name || ' ' || m.last_name AS "Manager"),
XMLElement("Commission", e.commission_pct)))
FROM hr.employees e, hr.employees m, hr.jobs j
WHERE e.department_id = d.department_id
AND j.job_id = e.job_id
AND m.employee_id = e.manager_id)))
AS XML
FROM hr.departments d, hr.countries c, hr.locations l
WHERE department_name = 'Executive'
AND d.location_id = l.location_id
AND l.country_id = c.country_id;
The query returns the following XML:
XML -------------------------------------------------------------------------------- <Department DepartmentId="90"><Name>Executive</Name><Location><Address>2004 Charade Rd</Address><City>Seattle</City><State>Washingto n</State><Zip>98199</Zip><Country>United States of America</Country></Location><EmployeeList><Employee employeeNumber="101"><FirstNa me>Neena</FirstName><LastName>Kochhar</LastName><EmailAddress>NKOCHHAR</EmailAdd ess><PHONE_NUMBER>515.123.4568</PHONE_NUMBER><Start Date>1989-09-21</StartDate><JobTitle>Administration Vice President</JobTitle><Salary>17000</Salary><Manager>Steven King</Manager><Com mission></Commission></Employee><Employee employeeNumber="102"><FirstName>Lex</FirstName><LastName>De Haan</LastName><EmailAddress>L DEHAAN</EmailAddress><PHONE_NUMBER>515.123.4569</PHONE NUMBER><StartDate>1993-01-13</StartDate><JobTitle>Administration Vice Presiden t</JobTitle><Salary>17000</Salary><Manager>Steven King</Manager><Commission></Commission></Employee></EmployeeList></Department>
This query generates element Department for each row in the departments table.
Each Department element contains attribute DepartmentID. The value of DepartmentID comes from the department_id column. The Department element contains sub-elements Name, Location, and EmployeeList.
The text node associated with the Name element will come from the name column in the departments table.
The Location element will have child elements Address, City, State, Zip, and Country. These elements are constructed by creating a forest of named elements from columns in the locations and countries tables. The values in the columns become the text node for the named element.
The EmployeeList element will contain an aggregation of Employee Elements. The content of the EmployeeList element is created by a subquery that returns the set of rows in the employees table that correspond to the current department. Each Employee element will contain information about the employee. The contents of the elements and attributes for each Employee element is taken from tables employees and jobs.
The output generated by the SQL/XML functions is not pretty-printed. This lets these functions avoid creating a full DOM when generating the required output, and reduce the size of the generated document. This lack of pretty-printing by SQL/XML functions will not matter to most applications. However, it makes verifying the generated output manually more difficult. When pretty-printing is required, invoke method on the generated document to force construction of a DOM and pretty-print the output. Since invoking forces a conventional DOM to be constructed, this technique should be used when working with queries that create large documents.
Example 3-47 Creating XMLType Views Over Conventional Relational Tables
CREATE OR REPLACE VIEW department_xml OF XMLType
WITH OBJECT ID (substr(extractValue(OBJECT_VALUE, '/Department/Name'), 1, 128))
AS
SELECT XMLElement(
"Department",
XMLAttributes(d.department_id AS "DepartmentId"),
XMLForest(d.department_name AS "Name"),
XMLElement("Location", XMLForest(street_address AS "Address",
city AS "City",
state_province AS "State",
postal_code AS "Zip",
country_name AS "Country")),
XMLElement(
"EmployeeList",
(SELECT XMLAgg(
XMLElement(
"Employee",
XMLAttributes (e.employee_id AS "employeeNumber" ),
XMLForest(e.first_name AS "FirstName",
e.last_name AS "LastName",
e.email AS "EmailAddress",
e.phone_number AS "PHONE_NUMBER",
e.hire_date AS "StartDate",
j.job_title AS "JobTitle",
e.salary AS "Salary",
m.first_name || ' ' ||
m.last_name AS "Manager"),
XMLElement("Commission", e.commission_pct)))
FROM hr.employees e, hr.employees m, hr.jobs j
WHERE e.department_id = d.department_id
AND j.job_id = e.job_id
AND m.employee_id = e.manager_id))).extract('/*')
AS XML
FROM hr.departments d, hr.countries c, hr.locations l
WHERE d.location_id = l.location_id
AND l.country_id = c.country_id;
View created.
The XMLType view lets relational data be persisted as XML content. Rows in XMLType views can be persisted as documents in Oracle XML DB Repository. The contents of an XMLType view can be queried, as shown in Example 3-48.
Example 3-48 Querying XMLType Views
This example shows a simple query against an XMLType view. The XPath expression passed to SQL function existsNode restricts the result set to the node that contains the Executive department information. The result is shown pretty-printed here for clarity.
SELECT OBJECT_VALUE FROM department_xml
WHERE existsNode(OBJECT_VALUE, '/Department[Name="Executive"]') = 1;
OBJECT_VALUE
------------------------------------------------
<Department DepartmentId="90">
<Name>Executive</Name>
<Location>
<Address>2004 Charade Rd</Address>
<City>Seattle</City>
<State>Washington</State>
<Zip>98199</Zip>
<Country>United States of America</Country>
</Location>
<EmployeeList>
<Employee employeeNumber="101">
<FirstName>Neena</FirstName>
<LastName>Kochhar</LastName>
<EmailAddress>NKOCHHAR</EmailAddress>
<PHONE_NUMBER>515.123.4568</PHONE_NUMBER>
<StartDate>1989-09-21</StartDate>
<JobTitle>Administration Vice President</JobTitle>
<Salary>17000</Salary>
<Manager>Steven King</Manager>
<Commission/>
</Employee>
<Employee employeeNumber="102">
<FirstName>Lex</FirstName>
<LastName>De Haan</LastName>
<EmailAddress>LDEHAAN</EmailAddress>
<PHONE_NUMBER>515.123.4569</PHONE_NUMBER>
<StartDate>1993-01-13</StartDate>
<JobTitle>Administration Vice President</JobTitle>
<Salary>17000</Salary>
<Manager>Steven King</Manager>
<Commission/>
</Employee>
</EmployeeList>
</Department>
1 row selected.
As can be seen from the following EXPLAIN PLAN output, Oracle XML DB is able to correctly rewrite the XPath-expression argument in the existsNode expression into a SELECT statement on the underlying relational tables.
EXPLAIN PLAN FOR
SELECT OBJECT_VALUE FROM department_xml
WHERE existsNode(OBJECT_VALUE, '/Department[Name="Executive"]') = 1;
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2414180351
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 80 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 114 | | |
|* 2 | HASH JOIN | | 10 | 1140 | 7 (15)| 00:00:01 |
|* 3 | HASH JOIN | | 10 | 950 | 5 (20)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 680 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 1 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | JOBS | 19 | 513 | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 2 (0)| 00:00:01 |
| 8 | NESTED LOOPS | | 1 | 80 | 3 (0)| 00:00:01 |
| 9 | NESTED LOOPS | | 1 | 68 | 3 (0)| 00:00:01 |
|* 10 | TABLE ACCESS FULL | DEPARTMENTS | 1 | 19 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS BY INDEX ROWID | LOCATIONS | 1 | 49 | 1 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | LOC_ID_PK | 1 | | 0 (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 | 12 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("M"."EMPLOYEE_ID"="E"."MANAGER_ID")
3 - access("J"."JOB_ID"="E"."JOB_ID")
5 - access("E"."DEPARTMENT_ID"=:B1)
10 - filter("D"."DEPARTMENT_NAME"='Executive')
12 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
13 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")
30 rows selected.
Note:
XPath rewrite on XML expressions that operate onXMLType views is only supported when nodes referenced in the XPath expression are not descendants of an element created using SQL function XMLAgg.Another way to generate XML from relational data is with SQL function DBURIType. Function DBURIType exposes one or more rows in a given table or view as a single XML document. The name of the root element is derived from the name of the table or view. The root element contains a set of ROW elements. There is one ROW element for each row in the table or view. The children of each ROW element are derived from the columns in the table or view. Each child element contains a text node with the value of the column for the given row.
Example 3-49 Accessing DEPARTMENTS Table XML Content Using DBURIType and getXML()
This example shows how to use SQL function DBURIType to access the contents of the departments table in schema hr. The example uses method getXML() to return the resulting document as an XMLType instance.
SELECT DBURIType('/HR/DEPARTMENTS').getXML() FROM DUAL;
DBURITYPE('/HR/DEPARTMENTS').GETXML()
------------------------------------------------------
<?xml version="1.0"?>
<DEPARTMENTS>
<ROW>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<MANAGER_ID>200</MANAGER_ID>
<LOCATION_ID>1700</LOCATION_ID>
</ROW>
...
<ROW>
<DEPARTMENT_ID>20</DEPARTMENT_ID>
<DEPARTMENT_NAME>Marketing</DEPARTMENT_NAME>
<MANAGER_ID>201</MANAGER_ID>
<LOCATION_ID>1800</LOCATION_ID>
</ROW>
</DEPARTMENTS>
SQL function DBURIType lets XPath notation be used to control how much of the data in the table or view is returned when the table or view is accessed using DBURIType. Predicates in the XPath expression allow control over which of the rows in the table are included in the generated document.
Example 3-50 Using a Predicate in the XPath Expression to Restrict Which Rows Are Included
This example demonstrates how to use a predicate in an XPath expression to restrict the rows that are included in the generated XML document. Here, the XPath expression restricts the XML document to DEPARTMENT_ID columns with value 10.
SELECT DBURIType('/HR/DEPARTMENTS/ROW[DEPARTMENT_ID="10"]').getXML()
FROM DUAL;
DBURITYPE('/HR/DEPARTMENTS/ROW[DEPARTMENT_ID="10"]').GETXML()
------------------------------------------------------------------
<?xml version="1.0"?>
<ROW>
<DEPARTMENT_ID>10</DEPARTMENT_ID>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
<MANAGER_ID>200</MANAGER_ID>
<LOCATION_ID>1700</LOCATION_ID>
</ROW>
1 row selected.
As can be seen from the examples in this section, SQL function DBURIType provides a simple way to expose some or all rows in a relational table as one or more XML documents. The URL passed to function DBURIType can be extended to return a single column from the view or table, but in that case the URL must also include predicates that identify a single row in the target table or view. For example, the following URI would return just the value of the department_name column for the departments row where the department_id column has value 10.
SELECT DBURIType(
'/HR/DEPARTMENTS/ROW[DEPARTMENT_ID="10"]/DEPARTMENT_NAME').getXML()
FROM DUAL;
DBURITYPE('/HR/DEPARTMENTS/ROW[DEPARTMENT_ID="10"]/DEPARTMENT_NAME').GETXML()
-----------------------------------------------------------------------------
<?xml version="1.0"?>
<DEPARTMENT_NAME>Administration</DEPARTMENT_NAME>
1 row selected.
SQL function DBURIType does not provide the flexibility of the SQL/XML functions: DBURIType provides no way to control the shape of the generated document. The data can only come from a single table or view. The generated document consists of one or more ROW elements. Each ROW element contains a child for each column in the target table. The names of the child elements are derived from the column names.
To control the names of the XML elements, to include columns from more than one table, or to control which columns from a table appear in the generated document, create a relational view that exposes the desired set of columns as a single row, and then use function DBURIType to generate an XML document from the contents of that view.
The W3C XSLT Recommendation defines an XML language for specifying how to transform XML documents from one form to another. Transformation can include mapping from one XML schema to another or mapping from XML to some other format such as HTML or WML.
See Also:
http://www.w3.org/XML/Schema for information about the XSLT standardXSL transformation is typically expensive in terms of the amount of memory and processing required. Both the source document and the style sheet need to be parsed and loaded into memory structures that allow random access to different parts of the documents. Most XSL processors use DOM to provide the in-memory representation of both documents. The XSL processor then applies the style sheet to the source document, generating a third document.
Oracle XML DB includes an XSLT processor that lets XSL transformations be performed inside the database. In this way, Oracle XML DB can provide XML-specific memory optimizations that significantly reduce the memory required to perform the transformation. It can also eliminate overhead associated with parsing the documents. These optimizations are only available when the source for the transformation is a schema-based XML document, however.
Oracle XML provides three ways to invoke the XSL processor:
SQL function XMLtransform
XMLType method transform()
PL/SQL package DBMS_XSLPROCESSOR
Of these different ways to transform XML data, DBMS_XSLPROCESSOR has the best performance, because the style sheet is parsed only once.
Each of these XML transformation methods takes as input a source XML document and an XSL style sheet in the form of XMLType instances. For SQL function XMLtransform and XMLType method transform(), the result of the transformation can be an XML document or a non-XML document, such as HTML. However, for PL/SQL package DBMS_XSLPROCESSOR, the result of the transformation is expected to be a valid XML document. This means that any HTML generated by a transformation using package DBMS_XSLPROCESSOR must be XHTML, which is both valid XML and valid HTML.
Example 3-51 XSLT Style Sheet Example: PurchaseOrder.xsl
This example shows part of an XSLT style sheet, PurchaseOrder.xsl. The complete style sheet is given in "XSL Style Sheet Example, PurchaseOrder.xsl".
<?xml version="1.0" encoding="WINDOWS-1252"?>
<xsl:stylesheet version="1.0"
xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:xdb="http://xmlns.oracle.com/xdb"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<xsl:template match="/">
<html>
<head/>
<body bgcolor="#003333" text="#FFFFCC" link="#FFCC00" vlink="#66CC99" alink="#669999">
<FONT FACE="Arial, Helvetica, sans-serif">
<xsl:for-each select="PurchaseOrder"/>
<xsl:for-each select="PurchaseOrder">
<center>
<span style="font-family:Arial; font-weight:bold">
<FONT COLOR="#FF0000">
<B>PurchaseOrder </B>
</FONT>
</span>
</center>
<br/>
<center>
<xsl:for-each select="Reference">
<span style="font-family:Arial; font-weight:bold">
<xsl:apply-templates/>
</span>
</xsl:for-each>
</center>
</xsl:for-each>
<P>
<xsl:for-each select="PurchaseOrder">
<br/>
</xsl:for-each>
<P/>
<P>
<xsl:for-each select="PurchaseOrder">
<br/>
</xsl:for-each>
</P>
</P>
<xsl:for-each select="PurchaseOrder"/>
<xsl:for-each select="PurchaseOrder">
<table border="0" width="100%" BGCOLOR="#000000">
<tbody>
<tr>
<td WIDTH="296">
<P>
<B>
<FONT SIZE="+1" COLOR="#FF0000" FACE="Arial, Helvetica, sans-serif">Internal</FONT>
</B>
</P>
...
</td>
<td width="93"/>
<td valign="top" WIDTH="340">
<B>
<FONT COLOR="#FF0000">
<FONT SIZE="+1">Ship To</FONT>
</FONT>
</B>
<xsl:for-each select="ShippingInstructions">
<xsl:if test="position()=1"/>
</xsl:for-each>
<xsl:for-each select="ShippingInstructions">
</xsl:for-each>
...
These is nothing Oracle XML DB-specific about this style sheet. The style sheet can be stored in an XMLType table or column, or stored as non-schema-based XML inside Oracle XML DB Repository.
Performing transformations inside the database lets Oracle XML DB optimize features such as memory usage, I/O operations, and network traffic. These optimizations are particularly effective when the transformation operates on a small subset of the nodes in the source document.
In traditional XSL processors, the entire source document must be parsed and loaded into memory before XSL processing can begin. This process requires significant amounts of memory and processor. When only a small part of the document is processed this is inefficient.
When Oracle XML DB performs XSL transformations on a schema-based XML document there is no need to parse the document before processing can begin. The lazily loaded virtual DOM eliminates the need to parse the document, by loading content directly from disk as the nodes are accessed. The lazy load also reduces the amount of memory required to perform the transformation, because only the parts of the document that are processed are loaded into memory.
Example 3-52 Applying a Style Sheet Using TRANSFORM
This example shows how to use SQL function XMLtransform to apply an XSL style sheet to a document stored in an XMLType table, producing HTML code. SQL function XDBURIType reads the XSL style sheet from Oracle XML DB Repository. Method is called here on the result of to force pretty-printing, for readability only.
In the interest of brevity, only part of the result of the transformation is shown here; omitted parts are indicated with an ellipsis (. . .). Figure 3-7 shows what the transformed result looks like in a Web browser.
SELECT XMLtransform( OBJECT_VALUE, XDBURIType('/source/schemas/poSource/xsl/purchaseOrder.xsl').getXML()) FROM purchaseorder WHERE existsNode(OBJECT_VALUE, '/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]')=1; XMLTRANSFORM(OBJECT_VALUE, XDBURITYPE('/SOURCE/SCHEMAS/POSOURCE/XSL/PURCHASEORDER.XSL').GET --------------------------------------------------------------------------------------------- <html> <head/> <body bgcolor="#003333" text="#FFFFCC" link="#FFCC00" vlink="#66CC99" alink="#669999"> <FONT FACE="Arial, Helvetica, sans-serif"> <center> <span style="font-family:Arial; font-weight:bold"> <FONT COLOR="#FF0000"> <B>PurchaseOrder </B> </FONT> </span> </center> <br/> <center> <span style="font-family:Arial; font-weight:bold">SBELL-2002100912333601PDT</span> </center> <P> <br/> <P/> <P> <br/> </P> </P> <table border="0" width="100%" BGCOLOR="#000000"> <tbody> <tr> <td WIDTH="296"> <P> <B> <FONT SIZE="+1" COLOR="#FF0000" FACE="Arial, Helvetica, sans-serif">Internal</FONT> </B> </P> <table border="0" width="98%" BGCOLOR="#000099"> . . . </table> </td> <td width="93"/> <td valign="top" WIDTH="340"> <B> <FONT COLOR="#FF0000"> <FONT SIZE="+1">Ship To</FONT> </FONT> </B> <table border="0" BGCOLOR="#999900"> . . . </table> </td> </tr> </tbody> </table> <br/> <B> <FONT COLOR="#FF0000" SIZE="+1">Items:</FONT> </B> <br/> <br/> <table border="0"> . . . </table> </FONT> </body> </html> 1 row selected.
Oracle XML DB Repository makes it possible to organize XML content using a file/folder metaphor. This lets you use a URL to uniquely identify XML documents stored in the database. This approach appeals to XML developers used to using constructs such as URLs and XPath expressions to identify content.
Oracle XML DB Repository is modelled on the DAV standard. The DAV standard uses the term resource to describe any file or folder managed by a WebDAV server. A resource consists of a combination of metadata and content. The DAV specification defines the set of (system-defined) metadata properties that a WebDAV server is expected to maintain for each resource and the set of XML documents that a DAV server and DAV-enabled client uses to exchange metadata.
Although Oracle XML DB Repository can manage any kind of content, it provides specialized capabilities and optimizations related to managing resources where the content is XML.
All of the metadata and content managed by Oracle XML DB Repository is stored using a set of tables in the database schema owned by database schema (user account) XDB. User XDB is a locked account that is installed using DBCA or by running script catqm.sql. Script catqm.sql is located in the directory ORACLE_HOME/rdbms/admin. The repository can be uninstalled using DBCA or by running the script catnoqm.sql. Great care should be taken when running catnoqm.sql as this will drop all content stored in Oracle XML DB Repository and invalidate any XMLType tables or columns associated with registered XML schemas.
When using a relational database to maintain hierarchical folder structures, ensuring a high degree of concurrency when adding and removing items in a folder is a challenge. In conventional file system there is no concept of a transaction. Each operation (add a file, create a subfolder, rename a file, delete a file, and so on) is treated as an atomic transaction. Once the operation has completed the change is immediately available to all other users of the file system.
Note:
As a consequence of transactional semantics enforced by the database, folders created using SQL statements will not be visible to other database users until the transaction is committed. Concurrent access to Oracle XML DB Repository is controlled by the same mechanism used to control concurrency in Oracle Database. The integration of the repository with Oracle Database provides strong management options for XML content.One key advantage of Oracle XML DB Repository is the ability to use SQL for repository operations in the context of a logical transaction. Applications can create long-running transactions that include updates to one or more folders. In this situation, a conventional locking strategy that takes an exclusive lock on each updated folder or directory tree would quickly result in significant concurrency problems.
Queued Folder Modifications are Locked Until Committed
Oracle XML DB solves this by providing for name-level locking rather than folder-level locking. Repository operations such as creating, renaming, moving, or deleting a sub-folder or file do not require that your operation be granted an exclusive write lock on the target folder. The repository manages concurrent folder operations by locking the name within the folder rather than the folder itself. The name and the modification type are put on a queue.Only when the transaction is committed is the folder locked and its contents modified. Hence Oracle XML DB lets multiple applications perform concurrent updates on the contents of a folder. The queue is also used to manage folder concurrency by preventing two applications from creating objects with the same name.Queuing folder modifications until commit time also minimizes I/O when a number of changes are made to a single folder in the same transaction.This is useful when several applications generate files quickly in the same directory, for example when generating trace or log files, or when maintaining a spool directory for printing or email delivery.
You can work with content stored in Oracle XML DB Repository in these ways:
Using industry standard protocols such as HTTP(S), WebDAV, and FTP to perform document-level operations such as insert, update, and delete.
By directly accessing Oracle XML DB Repository content at the table or row level, using SQL.
Using Oracle XML DB Content Connector — see Chapter 31, "Using Oracle XML DB Content Connector".
Oracle XML DB supports industry-standard internet protocols such as HTTP(S), WebDav, and FTP. The combination of protocol support and URL-based access makes it possible to insert, retrieve, update, and delete content stored in Oracle Database from standard desktop applications such as Windows Explorer, Microsoft Word, and XMLSpy.
Figure 3-4 shows Windows Explorer used to insert a folder from the local hard drive into Oracle Database. Windows Explorer includes support for the WebDAV protocol. WebDAV extends the HTTP standard, adding additional verbs that allow an HTTP server to act as a file server.
When a Windows Explorer copy operation or FTP input command is used to transfer a number of documents into Oracle XML DB Repository, each put or post command is treated as a separate atomic operation. This ensures that the client does not get confused if one of the file transfers fails. It also means that changes made to a document through a protocol are visible to other users as soon as the request has been processed.
Figure 3-4 Copying Files into Oracle XML DB Repository
The following example shows commands issued and output generated when a standard command line FTP tool loads documents into Oracle XML DB Repository:
Example 3-53 Uploading Content into the Repository Using FTP
$ ftp mdrake-sun 2100 Connected to mdrake-sun. 220 mdrake-sun FTP Server (Oracle XML DB/Oracle Database 10g Enterprise Edition Release 10.1.0.1.0 - Beta) ready. Name (mdrake-sun:oracle10): QUINE 331 pass required for QUINE Password: 230 QUINE logged in ftp> cd /source/schemas 250 CWD Command successful ftp> mkdir PurchaseOrders 257 MKD Command successful ftp> cd PurchaseOrders 250 CWD Command successful ftp> mkdir 2002 257 MKD Command successful ftp> cd 2002 250 CWD Command successful ftp> mkdir "Apr" 257 MKD Command successful ftp> put "Apr/AMCEWEN-20021009123336171PDT.xml" "Apr/AMCEWEN-20021009123336171PDT.xml" 200 PORT Command successful 150 ASCII Data Connection 226 ASCII Transfer Complete local: Apr/AMCEWEN-20021009123336171PDT.xml remote: Apr/AMCEWEN-20021009123336171PDT.xml 4718 bytes sent in 0.0017 seconds (2683.41 Kbytes/s) ftp> put "Apr/AMCEWEN-20021009123336271PDT.xml" "Apr/AMCEWEN-20021009123336271PDT.xml" 200 PORT Command successful 150 ASCII Data Connection 226 ASCII Transfer Complete local: Apr/AMCEWEN-20021009123336271PDT.xml remote: Apr/AMCEWEN-20021009123336271PDT.xml 4800 bytes sent in 0.0014 seconds (3357.81 Kbytes/s) ..... ftp> cd "Apr" 250 CWD Command successful ftp> ls -l 200 PORT Command successful 150 ASCII Data Connection -rw-r--r1 QUINE oracle 0 JUN 24 15:41 AMCEWEN-20021009123336171PDT.xml -rw-r--r1 QUINE oracle 0 JUN 24 15:41 AMCEWEN-20021009123336271PDT.xml -rw-r--r1 QUINE oracle 0 JUN 24 15:41 EABEL-20021009123336251PDT.xml -rw-r--r1 QUINE oracle 0 JUN 24 15:41 PTUCKER-20021009123336191PDT.xml -rw-r--r1 QUINE oracle 0 JUN 24 15:41 PTUCKER-20021009123336291PDT.xml -rw-r--r1 QUINE oracle 0 JUN 24 15:41 SBELL-20021009123336231PDT.xml -rw-r--r1 QUINE oracle 0 JUN 24 15:41 SBELL-20021009123336331PDT.xml -rw-r--r1 QUINE oracle 0 JUN 24 15:41 SKING-20021009123336321PDT.xml -rw-r--r1 QUINE oracle 0 JUN 24 15:41 SMCCAIN-20021009123336151PDT.xml -rw-r--r1 QUINE oracle 0 JUN 24 15:41 SMCCAIN-20021009123336341PDT.xml -rw-r--r1 QUINE oracle 0 JUN 24 15:41 VJONES-20021009123336301PDT.xml 226 ASCII Transfer Complete remote: -l 959 bytes received in 0.0027 seconds (349.45 Kbytes/s) ftp> cd ".." 250 CWD Command successful .... ftp> quit 221 QUIT Goodbye. $
The key point demonstrated by both of these examples is that neither Windows Explorer nor the FTP tool is aware that it is working with Oracle XML DB. Since the tools and Oracle XML DB both support open Internet protocols they work with each other out of the box.
Any tool that understands the WebDAV or FTP protocol can be used to create content managed by Oracle XML DB Repository. No additional software has to installed on the client or the mid-tier.
When the contents of the folders are viewed using a tool such as Windows Explorer or FTP, the length of any schema-based XML documents contained in the folder is shown as zero (0) bytes. This was designed as such for two reasons:
It is not clear what the size of the document should be. Is it the size of the CLOB instance generated by printing the document, or the number of bytes required to store the objects used to persist the document inside the database?
Regardless of which definition is chosen, calculating and maintaining this information is costly.
Figure 3-5 shows Internet Explorer using a URL and the HTTP protocol to view an XML document stored in the database.
Figure 3-5 Path-Based Access Using HTTP and a URL
Oracle XML DB Repository can be accessed and updated directly from SQL. This means that any application or programming language that can use SQL to interact with Oracle Database can also access and update content stored in the repository. Oracle XML DB includes PL/SQL package DBMS_XDB, which provides methods that allow resources to be created, modified, and deleted programmatically.
Example 3-54 Creating a Text Document Resource Using DBMS_XDB
This example shows how to create a resource using DBMS_XDB. Here the resource will be a simple text document containing the supplied text.
DECLARE
res BOOLEAN;
BEGIN
res := DBMS_XDB.createResource('/home/QUINE/NurseryRhyme.txt',
bfilename('XMLDIR', 'tdadxdb-03-01.txt'),
nls_charset_id('AL32UTF8'));
END;
/
This section describes features for accessing and updating Oracle XML DB Repository content.
Access XML Documents Using SQL
Content stored in the repository can be accessed and updated from SQL and PL/SQL. You can interrogate the structure of the repository in complex ways. For example, you can query to determine how many files with extension .xsl are under a location other than /home/mystylesheetdir.
You can also mix path-based repository access with content-based access. You can, for example, ask "How many documents not under /home/purchaseOrders have a node identified by the XPath /PurchaseOrder/User/text() with a value of KING?"
All of the metadata for managing the repository is stored in a database schema owned by database schema (user account) XDB. User XDB is created during Oracle XML DB installation. The primary table in this schema is an XMLType table called XDB$RESOURCE. This contains one row for each resource (file or folder) in the repository. Documents in this table are referred to as resource documents. The XML schema that defines the structure of an Oracle XML DB resource document is registered under URL, "http://xmlns.oracle.com/xdb/XDBResource.xsd.
Repository Content is Exposed Through RESOURCE_VIEW and PATH_VIEW
Table XDB$RESOURCE is not directly exposed to SQL programmers. Instead, the contents of the repository are exposed through two public views, RESOURCE_VIEW and PATH_VIEW. Through these views, you can access and update both the metadata and the content of documents stored in the repository. Both views contain a virtual column, RES. Use RES to access and update resource documents with SQL statements using a path notation. Operations on the views use underlying tables in the repository.
Use EXISTS_PATH and UNDER_PATH to Include Path-Based Predicates in the WHERE Clause
Oracle XML DB includes two repository-specific SQL functions: exists_path and under_path. Use these functions to include path-based predicates in the WHERE clause of a SQL statement. SQL operations can select repository content based on the location of the content in the repository folder hierarchy. The hierarchical repository index ensures that path-based queries are executed efficiently.
When XML schema-based XML documents are stored in the repository, the document content is stored as an object in the default table identified by the XML schema. The repository contains only metadata about the document and a pointer (REF of XMLType) that identifies the row in the default table that contains the content.
Documents Other Than XML Can Be Stored In the Repository
It is also possible to store other kinds of documents in the repository. When a document that is not XML or is not schema-based XML is stored in the repository, the document content is stored in a LOB along with the metadata about the document.
PL/SQL Packages to Create, Delete, Rename, Move,... Folders and Documents
Since Oracle XML DB repository can be accessed and updated using SQL, any application capable of calling a PL/SQL procedure can use the repository. All SQL and PL/SQL repository operations are transactional, and access to the repository and its contents is subject to database security, as well as the repository access control lists (ACLs).
With supplied PL/SQL packages DBMS_XDB, DBMS_XDBZ, and DBMS_XDB_VERSION, you can create, delete, and rename documents and folders, move a file or folder within the folder hierarchy, set and change the access permissions on a file or folder, and initiate and manage versioning.
Example 3-55 Using PL/SQL Package DBMS_XDB To Create Folders
This example uses PL/SQL package DBMS_XDB to create a set of subfolders beneath folder /public.
DECLARE
RESULT BOOLEAN;
BEGIN
IF (NOT DBMS_XDB.existsResource('/public/mysource')) THEN
result := DBMS_XDB.createFolder('/public/mysource');
END IF;
IF (NOT DBMS_XDB.existsResource('/public/mysource/schemas')) THEN
result := DBMS_XDB.createFolder('/public/mysource/schemas');
END IF;
IF (NOT DBMS_XDB.existsResource('/public/mysource/schemas/poSource')) THEN
result := DBMS_XDB.createFolder('/public/mysource/schemas/poSource');
END IF;
IF (NOT DBMS_XDB.existsResource('/public/mysource/schemas/poSource/xsd')) THEN
result := DBMS_XDB.createFolder('/public/mysource/schemas/poSource/xsd');
END IF;
IF (NOT DBMS_XDB.existsResource('/public/mysource/schemas/poSource/xsl')) THEN
result := DBMS_XDB.createFolder('/public/mysource/schemas/poSource/xsl');
END IF;
END;
/
You can access the content of documents stored in Oracle XML DB Repository in several ways. The easiest way is to use XDBURIType. XDBURIType uses a URL to specify which resource to access. The URL passed to the XDBURIType is assumed to start at the root of the repository. Data type XDBURIType provides methods getBLOB(), getCLOB(), and getXML() to access the different kinds of content that can be associated with a resource.
Example 3-56 Using XDBURIType to Access a Text Document in the Repository
This example shows how to use XDBURIType to access the content of the text document:
SELECT XDBURIType('/home/QUINE/NurseryRhyme.txt').getCLOB() FROM DUAL;
XDBURITYPE('/HOME/QUINE/NURSERYRHYME.TXT').GETCLOB()
----------------------------------------------------
Mary had a little lamb
Its fleece was white as snow
and everywhere that Mary went
that lamb was sure to go
1 row selected.
Example 3-57 Using XDBURIType and a Repository Resource to Access Content
The contents of a document can also be accessed using the resource document. This example shows how to access the content of a text document:
SELECT
DBMS_XMLGEN.convert(
extract(RES,
'/Resource/Contents/text/text()',
'xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd"').getCLOBVal(),
1)
FROM RESOURCE_VIEW r
WHERE equals_path(RES, '/home/QUINE/NurseryRhyme.txt') = 1;
DBMS_XMLGEN.CONVERT(EXTRACT(RES,'/RESOURCE/CONTENTS/TEXT/TEXT()','XMLNS="HTTP://
--------------------------------------------------------------------------------
Mary had a little lamb
Its fleece was white as snow
and everywhere that Mary went
that lamb was sure to go
1 row selected.
SQL function extract, rather than extractValue, is used to access the text node. This returns the content of the text node as an XMLType instance, which makes it possible to access the content of the node using XMLType method getCLOBVal(). Hence, you can access the content of documents larger than 4K. Here, DBMS_XMLGEN.convert removes any entity escaping from the text.
Example 3-58 Accessing XML Documents Using Resource and Namespace Prefixes
The content of non-schema-based and schema-based XML documents can also be accessed through the resource. This example shows how to use an XPath expression that includes nodes from the resource document and nodes from the XML document to access the contents of a PurchaseOrder document using the resource.
SELECT des.description
FROM RESOURCE_VIEW rv,
XMLTable(XMLNAMESPACES ('http://xmlns.oracle.com/xdb/XDBResource.xsd' AS "r"),
'/r:Resource/r:Contents/PurchaseOrder/LineItems/LineItem'
PASSING rv.RES
COLUMNS description VARCHAR2(256) PATH 'Description') des
WHERE
equals_path(rv.RES, '/home/QUINE/PurchaseOrders/2002/Mar/SBELL-2002100912333601PDT.xml') = 1;
DES.DESCRIPTION
---------------------------------
A Night to Remember
The Unbearable Lightness Of Being
The Wizard of Oz
3 rows selected.
In this case, a namespace prefix, r, was used to identify which nodes in the XPath expression are members of the resource namespace. This is necessary, because the purchase-order XML schema does not define a namespace, and it is not possible to apply a namespace prefix to nodes in the PurchaseOrder document. Namespace prefix r is defined using the XMLNAMESPACES clause of SQL function XMLTable.
See Also:
Chapter 18, "Using XQuery with Oracle XML DB" for more information about theXMLNAMESPACES clause of XMLTableThe content of a schema-based XML document can be accessed in two ways.
In the same manner as for non-schema-based XML documents, by using the resource document. This lets RESOURCE_VIEW be used to query different types of schema-based XML documents with a single SQL statement.
As a row in the default table that was defined when the XML schema was registered with Oracle XML DB.
The XMLRef element in the resource document provides the join key required when a SQL statement needs to access or update metadata and content as part of a single operation.
The following queries use joins based on the value of the XMLRef to access resource content.
Example 3-59 Querying Repository Resource Data Using SQL Function REF and Element XMLRef
This example locates a row in the defaultTable based on a path in Oracle XML DB Repository. SQL function ref locates the target row in the default table, based on the value of the XMLRef element in the resource document, RES.
SELECT des.description
FROM RESOURCE_VIEW rv,
purchaseorder p,
XMLTable('/PurchaseOrder/LineItems/LineItem' PASSING p.OBJECT_VALUE
COLUMNS description VARCHAR2(256) PATH 'Description') des
WHERE equals_path(res, '/home/QUINE/PurchaseOrders/2002/Mar/SBELL-2002100912333601PDT.xml') = 1
AND ref(p) = extractValue(rv.RES, '/Resource/XMLRef');
DES.DESCRIPTION
---------------------------------
A Night to Remember
The Unbearable Lightness Of Being
The Wizard of Oz
3 rows selected.
Example 3-60 Selecting XML Document Fragments Based on Metadata, Path, and Content
This example shows how to select fragments from XML documents based on metadata, path, and content. The query returns the value of element Reference for documents under /home/QUINE/PurchaseOrders/2002/Mar that contain orders for part number 715515009058.
SELECT extractValue(p.OBJECT_VALUE, '/PurchaseOrder/Reference')
FROM RESOURCE_VIEW rv, purchaseorder p
WHERE under_path(rv.RES, '/home/QUINE/PurchaseOrders/2002/Mar') = 1
AND ref(p) = extractValue(rv.RES, '/Resource/XMLRef')
AND existsNode(p.OBJECT_VALUE,
'/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]')
= 1;
EXTRACTVALUE(P.OBJECT_VALUE,'/
------------------------------
CJOHNSON-20021009123335851PDT
LSMITH-2002100912333661PDT
SBELL-2002100912333601PDT
3 rows selected.
In general, when accessing the content of schema-based XML documents, joining RESOURCE_VIEW or PATH_VIEW with the default table is more efficient than using RESOURCE_VIEW or PATH_VIEW on its own. An explicit join between the resource document and the default table tells Oracle XML DB that the SQL statement will only work on one type of XML document. This lets XPath rewrite be used to optimize the operation on the default table as well as the operation on the resource.
You can update the content of documents stored in Oracle XML DB Repository using protocols or SQL.
The most popular content authoring tools now support HTTP, FTP, and WebDAV protocols. These tools can use a URL and the HTTP verb get to access the content of a document, and the HTTP verb put to save the contents of a document. Hence, given the appropriate access permissions, a simple URL is all you need to access and edit content stored in Oracle XML DB Repository.
Figure 3-6 shows how, with the WebDAV support included in Microsoft Word, you can use Microsoft Word to update and edit a document stored in Oracle XML DB Repository.
Figure 3-6 Using Microsoft Word to Update and Edit Content Stored in Oracle XML DB
When an editing application such as Microsoft Word updates an XML document stored in Oracle XML DB, the database receives an input stream containing the new content of the document. Unfortunately, products such as Word do not provide Oracle XML DB with any way of identifying which changes have taken place in the document. This means that partial updates are not possible, and it is necessary to re-parse the entire document, replacing all the objects derived from the original document with objects derived from the new content.
SQL functions such as updateXML can be used to update the content of any document stored in Oracle XML DB Repository. The content of the document can be modified by updating the resource document or by updating the default table that holds the content of the document.
Example 3-61 Updating a Document Using UPDATE and UPDATEXML on the Resource
This example shows how to update the contents of a simple text document using the SQL UPDATE statement and SQL function updateXML on the resource document. An XPath expression is passed to updateXML as the target of the update operation, identifying the text node belonging to element /Resource/Contents/text.
DECLARE
file BFILE;
contents CLOB;
dest_offset NUMBER := 1;
src_offset NUMBER := 1;
lang_context NUMBER := 0;
conv_warning NUMBER := 0;
BEGIN
file := bfilename('XMLDIR', 'tdadxdb-03-02.txt');
DBMS_LOB.createTemporary(contents, true, DBMS_LOB.SESSION);
DBMS_LOB.fileopen(file, DBMS_LOB.file_readonly);
DBMS_LOB.loadClobfromFile(contents,
file,
DBMS_LOB.getLength(file),
dest_offset,
src_offset,
nls_charset_id('AL32UTF8'),
lang_context,
conv_warning);
DBMS_LOB.fileclose(file);
UPDATE RESOURCE_VIEW
SET res = updateXML(res,
'/Resource/Contents/text/text()',
contents,
'xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd"')
WHERE equals_path(res, '/home/QUINE/NurseryRhyme.txt') = 1;
DBMS_LOB.freeTemporary(contents);
END;
/
The technique for updating the content of a document by updating the associated resource has the advantage that it can be used to update any kind of document stored in Oracle XML DB Repository.
Example 3-62 Updating a Node in the XML Document Using UPDATE and UPDATEXML
This example shows how to update a node in an XML document by performing an update on the resource document. Here, SQL function updateXML changes the value of the text node associated with element User.
UPDATE RESOURCE_VIEW SET res = updateXML(res, '/r:Resource/r:Contents/PurchaseOrder/User/text()', 'SKING', 'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"') WHERE equals_path( res, '/home/QUINE/PurchaseOrders/2002/Mar/SBELL-2002100912333601PDT.xml') = 1; 1 row updated. SELECT extractValue(res, '/r:Resource/r:Contents/PurchaseOrder/User/text()', 'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"') FROM RESOURCE_VIEW WHERE equals_path( res, '/home/QUINE/PurchaseOrders/2002/Mar/SBELL-2002100912333601PDT.xml') = 1; EXTRACTVALUE(RES, '/R:RESOURCE/R:CONTENTS/PURCHASEORDER/USER/TEXT()', 'XMLNS:R="HTTP://XMLNS.ORACLE.COM/XDB/XDBRESOURCE.XSD"') --------------------------------------------------------------------- SKING 1 row selected.
You can update XML schema-based XML documents by performing the update operation directly on the default table that is used to manage the content of the document. If the document must be located by a WHERE clause that includes a path or conditions based on metadata, then the UPDATE statement must use a join between the resource and the default table.
In general, when updating the contents of XML schema-based XML documents, joining the RESOURCE_VIEW or PATH_VIEW with the default table is more efficient than using the RESOURCE_VIEW or PATH_VIEW on its own. The explicit join between the resource document and the default table tells Oracle XML DB that the SQL statement will work on only one type of XML document. This lets a partial update be used on the default table and resource.
Example 3-63 Updating XML Schema-Based Documents in the Repository
In this example, SQL function updateXML operates on the default table, with the target row identified by a path. The row to be updated is identified by a REF. The REF is identified by a repository path using SQL function equals_path. This limits the update to the row corresponding to the resource identified by the specified path.
UPDATE purchaseorder p SET p.OBJECT_VALUE = updateXML(p.OBJECT_VALUE, '/PurchaseOrder/User/text()', 'SBELL') WHERE ref(p) = (SELECT extractValue(rv.RES,'/Resource/XMLRef') FROM RESOURCE_VIEW rv WHERE equals_path(rv.RES, '/home/QUINE/PurchaseOrders/2002/Mar/SBELL-2002100912333601PDT.xml') = 1); 1 row updated. SELECT extractValue(p.OBJECT_VALUE, '/PurchaseOrder/User/text()') FROM purchaseorder p, RESOURCE_VIEW rv WHERE ref(p) = extractValue(rv.RES, '/Resource/XMLRef') AND equals_path(rv.RES, '/home/QUINE/PurchaseOrders/2002/Mar/SBELL-2002100912333601PDT.xml') = 1; EXTRACTVAL ---------- SBELL 1 row selected.
You can control access to the resources in Oracle XML DB Repository by using access control lists (ACLs). An ACL is a list of access control entries (ACEs), each of which grants or denies a set of privileges to a specific principal. The principal can be a database user, a database role, an LDAP user, an LDAP group or the special principal dav:owner, which refers to the owner of the resource. Each resource in the repository is protected by an ACL. The ACL determines what privileges, such as read-properties and update, a user has on the resource. Each repository operation includes a check of the ACL to determine if the current user is allowed to perform the operation. By default, a new resource inherits the ACL of its parent folder. But you can set the ACL of a resource using procedure DBMS_XDB.setACL(). For more details on Oracle XML DB resource security, see Chapter 27, "Repository Resource Security".
In the following example, the current user is QUINE. The query gives the number of resources in the folder /public. Assume that there are only two resources in this folder: f1 and f2. Also assume that the ACL on f1 grants the read-properties privilege to QUINE while the ACL on f2 does not grant QUINE any privileges. A user needs the read-properties privilege on a resource for it to be visible to the user. The result of the query is 1, because only f1 is visible to QUINE.
SELECT count(*) FROM RESOURCE_VIEW r WHERE under_path(r.res, '/public') = 1;
COUNT(*)
--------
1
When working from SQL, normal transactional behavior is enforced. Multiple calls to SQL functions such as updateXML can be used within a single logical unit of work. Changes made through functions like updateXML are not visible to other database users until the transaction is committed. At any point, ROLLBACK can be used to back out the set of changes made since the last commit.
In Oracle XML DB, the system-defined metadata for each resource is preserved as an XML document. The structure of these resource documents is defined by the XDBResource.xsd XML schema. This schema is registered as a global XML schema at URL http://xmlns.oracle.com/xdb/XDBResource.xsd.
Oracle XML DB gives you access to metadata and information about the folder hierarchy using two public views, RESOURCE_VIEW and PATH_VIEW.
RESOURCE_VIEW contains one entry for each file or folder stored in Oracle XML DB Repository. Column RES of RESOURCE_VIEW contains the resource, an XML document that manages the metadata properties associated with the resource content. Column ANY_PATH contains a valid URL that the current user can pass to XDBURIType to access the resource content. If this content is not binary data, then the resource itself also contains the content.
Oracle XML DB supports the concept of linking. Linking makes it possible to define multiple paths to a given document. A separate XML document, called the link-properties document, maintains metadata properties that are specific to the path, rather than to the resource. Whenever a resource is created, an initial link is also created.
PATH_VIEW exposes the link-properties documents. There is one entry in PATH_VIEW for each possible path to a document. Column RES of PATH_VIEW contains the resource document pointed to by this link. Column PATH contains the path that the link lets you use to access the resource. Column LINK contains the link-properties document (metadata) for this PATH.
Example 3-64 Viewing RESOURCE_VIEW and PATH_VIEW Structures
The following example shows the description of public views RESOURCE_VIEW and PATH_VIEW:
DESCRIBE RESOURCE_VIEW
Name Null? Type
-------------------------------------------------------------
RES SYS.XMLTYPE(XMLSchema
"http://xmlns.oracle.com/xdb/XDBResource.xsd"
Element
"Resource")
ANY_PATH VARCHAR2(4000)
RESID RAW(16)
DESCRIBE PATH_VIEW
Name Null? Type
-------------------------------------------------------------
PATH VARCHAR2(1024)
RES SYS.XMLTYPE(XMLSchema
"http://xmlns.oracle.com/xdb/XDBResource.xsd"
Element
"Resource")
LINK SYS.XMLTYPE
RESID RAW(16)
See Also:
Oracle Database Reference for more information about view PATH_VIEW
Oracle Database Reference for more information about view RESOURCE_VIEW
Oracle XML DB provides two SQL functions, equals_path and under_path, that can be used to perform folder-restricted queries. Such queries limit SQL statements that operate on the RESOURCE_VIEW or PATH_VIEW to documents that are at a particular location in Oracle XML DB folder hierarchy. Function equals_path restricts the statement to a single document identified by the specified path. Function under_path restricts the statement to those documents that exist beneath a certain point in the hierarchy.
The following examples demonstrate simple folder-restricted queries against resource documents stored in RESOURCE_VIEW and PATH_VIEW.
Example 3-65 Accessing Resources Using EQUALS_PATH and RESOURCE_VIEW
The following query uses SQL function equals_path and RESOURCE_VIEW to access the resource created in Example 3-64.
SELECT r.RES.getCLOBVal()
FROM RESOURCE_VIEW r
WHERE equals_path(res, '/home/QUINE/NurseryRhyme.txt') = 1;
R.RES.GETCLOBVAL()
--------------------------------------------------------------------------------
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd"
Hidden="false"
Invalid="false"
Container="false"
CustomRslv="false"
VersionHistory="false"
StickyRef="true">
<CreationDate>2005-06-13T13:19:20.566623</CreationDate>
<ModificationDate>2005-06-13T13:19:22.997831</ModificationDate>
<DisplayName>NurseryRhyme.txt</DisplayName>
<Language>en-US</Language>
<CharacterSet>UTF-8</CharacterSet>
<ContentType>text/plain</ContentType>
<RefCount>1</RefCount>
<ACL>
<acl description=
"Private:All privileges to OWNER only and not accessible to others"
xmlns="http://xmlns.oracle.com/xdb/acl.xsd" xmlns:dav="DAV:"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/xdb/acl.xsd
http://xmlns.oracle.com/xdb/acl.xsd"
shared="true">
<ace>
<grant>true</grant>
<principal>dav:owner</principal>
<privilege>
<all/>
</privilege>
</ace>
</acl>
</ACL>
<Owner>QUINE</Owner>
<Creator>QUINE</Creator>
<LastModifier>QUINE</LastModifier>
<SchemaElement>http://xmlns.oracle.com/xdb/XDBSchema.xsd#text</SchemaElement>
<Contents>
<text>Hickory Dickory Dock
The Mouse ran up the clock
The clock struck one
The Mouse ran down
Hickory Dickory Dock
</text>
</Contents>
</Resource>
1 row selected.
As Example 3-65 shows, a resource document is an XML document that captures the set of metadata defined by the DAV standard. The metadata includes information such as CreationDate, Creator, Owner, ModificationDate, and DisplayName. The content of the resource document can be queried and updated just like any other XML document, using SQL functions such as extract, extractValue, existsNode, and updateXML.
Example 3-66 Determining the Path to XSL Style Sheets Stored in the Repository
The first query finds a path to each of the XSL style sheets stored in Oracle XML DB Repository. It performs a search based on the DisplayName ending in .xsl.
SELECT ANY_PATH FROM RESOURCE_VIEW WHERE extractValue(RES, '/Resource/DisplayName') LIKE '%.xsl'; ANY_PATH ------------------------------------------- /source/schemas/poSource/xsl/empdept.xsl /source/schemas/poSource/xsl/purchaseOrder.xsl 2 rows selected.
Example 3-67 Counting Resources Under a Path
This example counts the number of resources (files and folders) under the path /home/QUINE/PurchaseOrders. Using RESOURCE_VIEW rather than PATH_VIEW ensures that any resources that are the target of multiple links are only counted once. SQL function under_path restricts the result set to documents that can be accessed using a path that starts from /home/QUINE/PurchaseOrders.
SELECT count(*)
FROM RESOURCE_VIEW
WHERE under_path(RES, '/home/QUINE/PurchaseOrders') = 1;
COUNT(*)
----------
145
1 row selected.
Example 3-68 Listing the Folder Contents in a Path
This query lists the contents of the folder identified by path /home/QUINE/PurchaseOrders/2002/Apr. This is effectively a directory listing of the folder.
SELECT PATH FROM PATH_VIEW WHERE under_path(RES, '/home/QUINE/PurchaseOrders/2002/Apr') = 1; PATH ---------------------------------------------------------------------- /home/QUINE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336171PDT.xml /home/QUINE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336271PDT.xml /home/QUINE/PurchaseOrders/2002/Apr/EABEL-20021009123336251PDT.xml /home/QUINE/PurchaseOrders/2002/Apr/PTUCKER-20021009123336191PDT.xml /home/QUINE/PurchaseOrders/2002/Apr/PTUCKER-20021009123336291PDT.xml /home/QUINE/PurchaseOrders/2002/Apr/SBELL-20021009123336231PDT.xml /home/QUINE/PurchaseOrders/2002/Apr/SBELL-20021009123336331PDT.xml /home/QUINE/PurchaseOrders/2002/Apr/SKING-20021009123336321PDT.xml /home/QUINE/PurchaseOrders/2002/Apr/SMCCAIN-20021009123336151PDT.xml /home/QUINE/PurchaseOrders/2002/Apr/SMCCAIN-20021009123336341PDT.xml /home/QUINE/PurchaseOrders/2002/Apr/VJONES-20021009123336301PDT.xml 11 rows selected.
Example 3-69 Listing the Links Contained in a Folder
This query lists the set of links contained in the folder identified by the path /home/QUINE/PurchaseOrders/2002/Apr where the DisplayName element in the associated resource starts with an S.
SELECT PATH
FROM PATH_VIEW
WHERE extractValue(RES, '/Resource/DisplayName') like 'S%'
AND under_path(RES, '/home/QUINE/PurchaseOrders/2002/Apr') = 1;
PATH
----------------------------------------------------------------------
/home/QUINE/PurchaseOrders/2002/Apr/SBELL-20021009123336231PDT.xml
/home/QUINE/PurchaseOrders/2002/Apr/SBELL-20021009123336331PDT.xml
/home/QUINE/PurchaseOrders/2002/Apr/SKING-20021009123336321PDT.xml
/home/QUINE/PurchaseOrders/2002/Apr/SMCCAIN-20021009123336151PDT.xml
/home/QUINE/PurchaseOrders/2002/Apr/SMCCAIN-20021009123336341PDT.xml
5 rows selected.
Example 3-70 Finding Paths to Resources that Contain Purchase-Order XML Documents
This query finds a path to each resource in Oracle XML DB Repository that contains a PurchaseOrder document. The documents are identified based on the metadata property SchemaElement that identifies the XML schema URL and global element for schema-based XML data stored in the repository.
SELECT ANY_PATH
FROM RESOURCE_VIEW
WHERE existsNode(RES,
'/Resource[SchemaElement=
"http://localhost:8080/source/schemas/poSource/xsd/purchaseOrder.xsd#PurchaseOrder"]')
= 1;
This returns the following paths, each of which contains a PurchaseOrder document:
ANY_PATH
-----------------------------------------------------------------------
/home/QUINE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336171PDT.xml
/home/QUINE/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336271PDT.xml
/home/QUINE/PurchaseOrders/2002/Apr/EABEL-20021009123336251PDT.xml
/home/QUINE/PurchaseOrders/2002/Apr/PTUCKER-20021009123336191PDT.xml
...
132 rows selected.
In a conventional relational database, path-based access and folder-restricted queries would have to be implemented using CONNECT BY operations. Such queries are expensive, so path-based access and folder-restricted queries would become inefficient as the number of documents and depth of the folder hierarchy increase.
To address this issue, Oracle XML DB introduces a new index type, the hierarchical repository index. This lets the database resolve folder-restricted queries without relying on a CONNECT BY operation. Because of this, Oracle XML DB can execute path-based and folder-restricted queries efficiently. The hierarchical repository index is implemented as an Oracle domain index. This is the same technique used to add Oracle Text indexing support and many other advanced index types to the database.
Example 3-71 EXPLAIN Plan Output for a Folder-Restricted Query
This example shows the EXPLAIN PLAN output generated for a folder-restricted query. As shown, the hierarchical repository index XDBHI_IDX is used to resolve the query.
EXPLAIN PLAN FOR
SELECT PATH
FROM PATH_VIEW
WHERE extractValue(RES, '/Resource/DisplayName') LIKE 'S%'
AND under_path(RES, '/home/QUINE/PurchaseOrders/2002/Apr') = 1;
Explained.
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2568289845
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 254 | 46736 | 34 (6)| 00:00:01 |
| 1 | NESTED LOOPS | | 254 | 46736 | 34 (6)| 00:00:01 |
| 2 | NESTED LOOPS | | 254 | 42418 | 34 (6)| 00:00:01 |
| 3 | NESTED LOOPS | | 254 | 35306 | 34 (6)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | XDB$RESOURCE | 1 | 137 | 3 (0)| 00:00:01 |
|* 5 | DOMAIN INDEX | XDBHI_IDX | | | | |
| 6 | COLLECTION ITERATOR PICKLER FETCH| | | | | |
|* 7 | INDEX UNIQUE SCAN | XDB_PK_H_LINK | 1 | 28 | 0 (0)| 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C003728 | 1 | 17 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("P"."SYS_NC00011$" LIKE 'S%')
5 - access("XDB"."UNDER_PATH"(SYS_MAKEXML('8758D485E6004793E034080020B242C6',734,"XMLEXTRA"
,"XMLDATA"),'/home/QUINE/PurchaseOrders/2002/Apr',9999)=1)
7 - access("H"."PARENT_OID"=SYS_OP_ATG(VALUE(KOKBF$),3,4,2) AND
"H"."NAME"=SYS_OP_ATG(VALUE(KOKBF$),2,3,2))
8 - access("R2"."SYS_NC_OID$"=SYS_OP_ATG(VALUE(KOKBF$),3,4,2))
25 rows selected.
Oracle XML DB provides special handling for XML documents. The rules for storing the contents of schema-based XML document are defined by the XML schema. The content of the document is stored in the default table associated with the global element definition.
Oracle XML DB Repository also stores files that do not contain XML data, such as JPEG images or Word documents. The XML schema for each resource defines which elements are allowed, and specifies whether the content of these files is to be stored as BLOB or CLOB instances. The content of a non-schema-based XML document is stored as a CLOB instance in the repository.
There is one resource and one link-properties document for each file or folder in the repository. If there are multiple access paths to a given document, there will be a link-properties document for each possible link. Both the resource document and the link-properties are stored as XML documents. All these documents are stored in tables in the repository.
When an XML file is loaded into the repository, the following sequence of events takes place:
Oracle XML DB examines the root element of the XML document to see if it is associated with a known (registered) XML schema. This involves looking to see if the document includes a namespace declaration for the XMLSchema-instance namespace, and then looking for a schemaLocation or noNamespaceSchemaLocation attribute that identifies which XML schema the document is associated with.
If the document is based on a known XML schema, then the metadata for the XML schema is loaded from the XML schema cache.
The XML document is parsed and decomposed into a set of SQL objects derived from the XML schema.
The SQL objects created from the XML file are stored in the default table defined when the XML schema was registered with the database.
A resource document is created for each document processed. This lets the content of the document be accessed using the repository. The resource document for a schema-based XMLType includes an element XMLRef. This contents of this element is a REF of XMLType that can be used to locate the row in the default table containing the content associated with the resource.
The HTTP server built into Oracle XML DB makes it possible to use a browser to access any document stored in Oracle XML DB Repository. Since a resource can include a REF to a row in an XMLType table or view, it is possible to use a path to access this type of content.
Oracle XML DB includes the DBUri servlet, which makes it possible to access the content of any table or view directly from a browser. DBUri servlet uses the facilities of the DBURIType to generate a simple XML document from the contents of the table. The servlet is C-language based and installed in the Oracle XML DB HTTP server. By default, the servlet is installed under the virtual directory /oradb.
The URL passed to the DBUri Servlet is an extension of the URL passed to the DBURIType. The URL is extended with the address and port number of the Oracle XML DB HTTP server and the virtual root that directs HTTP(S) requests to the DBUri servlet. The default configuration for this is /oradb.
This means that the URL http://localhost:8080/oradb/HR/DEPARTMENTS would return an XML document containing the contents of the DEPARTMENTS table in the HR database schema. This assumes that the Oracle XML DB HTTP server is running on port 8080, the virtual root for the DBUri servlet is /oradb, and that the user making the request has access to the HR database schema.
DBUri servlet accepts parameters that allow you to specify the name of the ROW tag and MIME-type of the document that is returned to the client.
Content in XMLType table or view can also be accessed through the DBUri servlet. When the URL passed to the DBUri servlet references an XMLType table or XMLType view the URL can be extended with an XPath expression that can determine which documents in the table or row are returned. The XPath expression appended to the URL can reference any node in the document.
XML generated by DBUri servlet can be transformed using the XSLT processor built into Oracle XML DB. This lets XML that is generated by DBUri servlet be presented in a more legible format such as HTML.
Style sheet processing is initiated by specifying a transform parameter as part of the URL passed to DBUri servlet. The style sheet is specified using a URI that references the location of the style sheet within database. The URI can either be a DBURIType value that identifies a XMLType column in a table or view, or a path to a document stored in Oracle XML DB Repository. The style sheet is applied directly to the generated XML before it is returned to the client. When using DBUri servlet for XSLT processing, it is good practice to use the contenttype parameter to explicitly specify the MIME type of the generated output.
If the XML document being transformed is stored as an XML schema-based XMLType instance, then Oracle XML DB can reduce the overhead associated with XSL transformation by leveraging the capabilities of the lazily loaded virtual DOM.
The root of the URL is /oradb, so the URL is passed to the DBUri servlet that accesses the purchaseorder table in the SCOTT database schema, rather than as a resource in Oracle XML DB Repository. The URL includes an XPath expression that restricts the result set to those documents where node /PurchaseOrder/Reference/text() contains the value specified in the predicate. The contenttype parameter sets the MIME type of the generated document to text/xml.
Figure 3-7 shows how an XSL transformation can be applied to XML content generated by the DBUri servlet. In this example the URL passed to the DBUri includes the transform parameter. This causes the DBUri servlet to use SQL function XMLtransform to apply the style sheet /home/SCOTT/xsl/purchaseOrder.xsl to the PurchaseOrder document identified by the main URL, before returning the document to the browser. This style sheet transforms the XML document to a more user-friendly HTML page. The URL also uses contentType parameter to specify that the MIME-type of the final document will be text/html.
Figure 3-7 Database XSL Transformation of a PurchaseOrder Using DBUri Servlet
Figure 3-8 shows the departments table displayed as an HTML document. You need no code to achieve this, you only need an XMLType view, based on SQL/XML functions, an industry-standard XSL style sheet, and DBUri servlet.
Figure 3-8 Database XSL Transformation of Departments Table Using DBUri Servlet
Footnote Legend
Footnote 1: If you use XML schema annotationmaintainOrder = "false", then an unordered collection is used, instead of an ordered collection. Oracle recommends that you use ordered collections (maintainOrder="true") for XML data.