| Oracle® XML DB Developer's Guide 10g Release 1 (10.1) Part Number B10790-01 |
|
|
View PDF |
| Oracle® XML DB Developer's Guide 10g Release 1 (10.1) Part Number B10790-01 |
|
|
View PDF |
This chapter provides an overview of how to use Oracle XML DB. The examples here illustrate techniques for accessing and managing XML content in purchase orders (POs). The format and data of XML POs are well suited for Oracle XML DB storage and processing techniques because POs 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 containing non-structured or semi-structured data. This chapter also further explains Oracle XML DB concepts introduced in Chapter 1, " Introducing 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:
By using Oracle XML Developer's Kit (XDK) to parse the XML document outside Oracle Database and store the XML data as rows in one or more tables in the database. In this case Oracle Database was unaware that it was managing XML content.
By storing the XML document in Oracle Database using a Character Large Object (CLOB), Binary Large Object (BLOB), Binary File (BFILE), or VARCHAR column. Again, in this case, Oracle Database was unaware that it was managing XML content.
The introduction of Oracle XML DB and the XMLType datatype 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 allows the database to:
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 datatype, XMLType, to facilitate native handling of XML data in the database. The following summarizes XMLType:
XMLType can represent an XML document as an instance (of XMLType) in SQL.
XMLType has built-in member functions that operate on XML content. For example, you can use XMLType functions to create, extract, and index XML data stored in Oracle Database.
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 as parameters, return values, and variables
With XMLType and these capabilities, SQL developers can leverage the power of the relational database while working in the context of XML. Likewise, XML developers can leverage the power of XML standards while working in the context of a relational database.
XMLType datatype can be used as the datatype of columns in tables and views. Variables of XMLType can be used in PL/SQL stored procedures as parameters, return values, and so on. 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 functions that operate on XML content. Many of these functions are provided as both SQL functions and XMLType methods. For example, the extract() function extracts one or more nodes from an XMLType instance.
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.
The XMLType datatype and application programming interface (API) provide significant advantages as they enable both SQL operations on XML content and XML operations on SQL content:
Versatile API. XMLType has a versatile API for application development, because it includes built-in functions, indexing, and navigation support.
XMLType and SQL. You can use XMLType in SQL statements combined with other columns and datatypes. For example, you can query XMLType columns and join the result of the extraction with a relational column. Oracle Database can then determine an optimal way to run these queries.
Indexing. Oracle XML DB lets you create Btree indexes on the object-relational tables that are used to provide structured storage of XMLType tables and columns. Oracle Text indexing supports text indexing of the content of structured and unstructured XMLType tables and columns. The CTXXPATH domain index type of Oracle Text provides an XML-specific text index with transactional semantics. This index type can speed up certain XPath-based searches on both structured and unstructured content. Finally, function-based indexes can be used to create indexes on explicit XPATH expressions for both structured and unstructured XMLType.
Use XMLType any time you want to use the database a persistent storage of XML. For example, you can use XMLType functionality to perform the following tasks:
SQL queries on part of or the whole XML document: The XMLType functions existsNode() and extract() provide the necessary SQL query functions over XML documents.
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.
XPath functionality provided by extract() and existsNode() functions: Note that XMLType uses the built-in C XML parser and processor and hence provides better performance and scalability when used inside the server.
Indexing on XPath queries on documents: XMLType has member functions that you can use to create function-based indexes to optimize searches.
To shield applications from storage models. Using XMLType instead of CLOBs or relational storage allows applications to gracefully move to various storage alternatives later without affecting any of the query or DML statements in the application.
To prepare 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.
XMLType data can be stored in two ways:
In Large Objects (LOBs). LOB storage maintains content fidelity, that is, the original XML is preserved including whitespace. XML documents are stored composed as whole documents such as files. For non-schema-based storage, XMLType offers a Character Large Object (CLOB) storage option.
In Structured storage (in tables and views). Structured storage maintains DOM (Document Object Model) fidelity.
Native XMLType instances contain hidden columns that store this extra information that does not quite fit in the SQL object model. This information can be accessed through APIs in SQL or Java, using member functions, such as extractNode().
Changing XMLType storage from structured storage to LOB, or vice versa, is possible using database IMPORT and EXPORT. Your application code does not have to change. You can then change XML storage options when tuning your application, because each storage option has its own benefits.
Table 3-1 summarizes some advantages and disadvantages to consider when selecting your Oracle XML DB storage option. Storage options are also discussed in Table 1-1, "XML Storage Options: Structured or Unstructured " and Chapter 2, " Getting Started with Oracle XML DB".
Table 3-1 XML Storage Options in Oracle XML DB
The following examples create XMLType columns and tables for managing XML content in Oracle Database:
You can load XML content into Oracle XML DB using several techniques, including the following:
Table-based loading techniques:
Path-based loading techniques:
You can perform a simple INSERT 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 first be converted into an XMLType instance using one of the XMLType constructors.
|
See Also: Chapter 4, " XMLType Operations ", Appendix F, " SQL and PL/SQL APIs: Quick Reference ", and Oracle XML API Reference for a description of theXMLType constructors |
The XMLType constructors allow an XMLType instance to be created from different sources including VARCHAR and CLOB datatypes. The constructors also accept additional arguments that reduce the amount of processing associated with XMLType creation. For example, if the source XML document is well-formed and valid, the constructor accepts flags that disable the default checking typically performed when instantiating the XMLType.
In addition, if the source data is not encoded in the database character set, an XMLType instance can be constructed using either BFILE or BLOB datatypes. The encoding of the source data is specified through the character set id (csid) argument of the constructor.
Before using this procedure 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 'The path to the folder containing the XML File';
Example 3-3 shows how to create an XMLType instance from a CLOB value using PL/SQL procedure call getFileContent(). This procedure returns the content of the specified file as a CLOB value. It also uses the DBMS_LOB package to create the CLOB value from a BFILE value.
Example 3-3 Inserting XML Content into an XMLType Table
INSERT INTO example2
VALUES
(
xmltype
(
bfilename('XMLDIR', 'purchaseOrder.xml'),
nls_charset_id('AL32UTF8')
)
);
1 row created.
The following code lists the getFileContent() procedure definition:
CREATE OR REPLACE FUNCTION getFileContent(filename varchar2,
directoryName varchar2 default USER,
charset varchar2 default 'AL32UTF8')
return CLOB
is
fileContent CLOB := NULL;
file bfile := bfilename(directoryName,filename);
dest_offset number := 1;
src_offset number := 1;
lang_context number := 0;
conv_warning number := 0;
begin
DBMS_LOB.createTemporary(fileContent,true,DBMS_LOB.SESSION);
DBMS_LOB.fileopen(file, DBMS_LOB.file_readonly);
DBMS_LOB.loadClobfromFile
(
fileContent,
file,
DBMS_LOB.getLength(file),
dest_offset,
src_offset,
nls_charset_id(charset),
lang_context,
conv_warning
);
DBMS_LOB.fileclose(file);
return fileContent;
end;/
|
See Also: Oracle Database Application Developer's Guide - Large Objects and PL/SQL Packages and Types Reference for information onDBMS_LOB and methods used in this procedure |
After calling this procedure you must dispose of the temporary CLOB value by calling procedure DBMS_LOB.freeTemporary. If the file with XML content is not stored in the same character set as the database, the character set of the file must be passed as a third argument to the getFileContent() procedure, so that the contents of the file are converted to the appropriate database character set as the CLOB value is created.
Example 3-4 shows how to load XML content into Oracle XML DB by first creating an XMLType instance in Java given a Document Object Model (DOM).
Example 3-4 Inserting XML Content into an XML Type Table Using Java
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://otn.oracle.com/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 <xml.h>
#include <string.h>
#include <ocixmldb.h>
OCIEnv *envhp;
OCIError *errhp;
OCISvcCtx *svchp;
OCIStmt *stmthp;
OCIServer *srvhp;
OCIDuration dur;
OCISession *sesshp;
oratext *username;
oratext *password;
oratext *filename;
oratext *schemaloc;
/*--------------------------------------------------------*/
/* execute a sql statement which 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()
{
sword status;
if (OCIEnvCreate((OCIEnv **) &(envhp), (ub4) OCI_OBJECT,
(dvoid *) 0, (dvoid * (*)(dvoid *,size_t)) 0,
(dvoid * (*)(dvoid *, dvoid *, size_t)) 0,
(void (*)(dvoid *, dvoid *)) 0, (size_t) 0, (dvoid **) 0))
{
printf("FAILED: OCIEnvCreate()\n");
return OCI_ERROR;
}
/* allocate error handle */
if (OCIHandleAlloc((dvoid *) envhp, (dvoid **) &(errhp),
(ub4) OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0))
{
printf("FAILED: OCIHandleAlloc() on errhp\n");
return OCI_ERROR;
}
/* allocate server handle */
if (status = OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp,
(ub4) OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0))
{
printf("FAILED: OCIHandleAlloc() on srvhp\n");
return OCI_ERROR;
}
/* allocate service context handle */
if (status = OCIHandleAlloc((dvoid *) envhp,
(dvoid **) &(svchp), (ub4) OCI_HTYPE_SVCCTX,
(size_t) 0, (dvoid **) 0))
{
printf("FAILED: OCIHandleAlloc() on svchp\n");
return OCI_ERROR;
}
/* allocate session handle */
if (status = OCIHandleAlloc((dvoid *) envhp, (dvoid **) &sesshp ,
(ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0))
{
printf("FAILED: OCIHandleAlloc() on sesshp\n");
return OCI_ERROR;
}
/* allocate statement handle */
if (OCIHandleAlloc((dvoid *)envhp, (dvoid **) &stmthp,
(ub4)OCI_HTYPE_STMT, (CONST size_t) 0, (dvoid **) 0))
{
printf("FAILED: OCIHandleAlloc() on stmthp\n");
return status;
}
if (status = OCIServerAttach((OCIServer *) srvhp, (OCIError *) errhp,
(CONST oratext *)"", 0, (ub4) OCI_DEFAULT))
{
printf("FAILED: OCIServerAttach() on srvhp\n");
return OCI_ERROR;
}
/* set server attribute to service context */
if (status = OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
(dvoid *) srvhp, (ub4) 0, (ub4) OCI_ATTR_SERVER,
(OCIError *) errhp))
{
printf("FAILED: OCIAttrSet() on svchp\n");
return OCI_ERROR;
}
/* set user attribute to session */
if (status = OCIAttrSet((dvoid *)sesshp, (ub4) OCI_HTYPE_SESSION,
(dvoid *)username,
(ub4) strlen((const char *)username),
(ub4) OCI_ATTR_USERNAME, (OCIError *) errhp))
{
printf("FAILED: OCIAttrSet() on authp for user\n");
return OCI_ERROR;
}
/* set password attribute to session */
if (status = OCIAttrSet((dvoid *) sesshp, (ub4) OCI_HTYPE_SESSION,
(dvoid *)password,
(ub4) strlen((const char *)password),
(ub4) OCI_ATTR_PASSWORD, (OCIError *) errhp))
{
printf("FAILED: OCIAttrSet() on authp for password\n");
return OCI_ERROR;
}
/* Begin a session */
if (status = OCISessionBegin((OCISvcCtx *) svchp,
(OCIError *) errhp,
(OCISession *) sesshp, (ub4) OCI_CRED_RDBMS,
(ub4) OCI_STMT_CACHE))
{
printf("FAILED: OCISessionBegin(). Make sure database is up and
the username/password is valid. \n");
return OCI_ERROR;
}
/* set session attribute to service context */
if (status = OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX,
(dvoid *)sesshp, (ub4) 0, (ub4) OCI_ATTR_SESSION,
(OCIError *) errhp))
{
printf("FAILED: OCIAttrSet() on svchp\n");
return OCI_ERROR;
}}
/*--------------------------------------------------------*/
/* free oci handles and disconnect */
/*--------------------------------------------------------*/
void free_oci(){
sword status = 0;
/* End the session */
if (status = OCISessionEnd((OCISvcCtx *)svchp, (OCIError *)errhp,
(OCISession *)sesshp, (ub4) OCI_DEFAULT))
{
if (envhp)
OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);
return;
}
/* Detach from the server */
if (status = OCIServerDetach((OCIServer *)srvhp, (OCIError *)errhp,
(ub4)OCI_DEFAULT))
{
if (envhp)
OCIHandleFree((dvoid *)envhp, OCI_HTYPE_ENV);
return;
}
/* Free the handles */
if (stmthp)
OCIHandleFree((dvoid *)stmthp, (ub4) OCI_HTYPE_STMT);
if (sesshp)
OCIHandleFree((dvoid *)sesshp, (ub4) OCI_HTYPE_SESSION);
if (svchp)
OCIHandleFree((dvoid *)svchp, (ub4) OCI_HTYPE_SVCCTX);
if (srvhp)
OCIHandleFree((dvoid *)srvhp, (ub4) OCI_HTYPE_SERVER);
if (errhp)
OCIHandleFree((dvoid *)errhp, (ub4) OCI_HTYPE_ERROR);
if (envhp)
OCIHandleFree((dvoid *)envhp, (ub4) OCI_HTYPE_ENV);
return;}
void main(){
OCIType *xmltdo;
xmldocnode *doc;
ocixmldbparam params[1];
xmlerr err;
xmlctx *xctx;
oratext *ins_stmt;
sword status;
/* 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");
printf("The xml document is :\n");
XmlSaveDom(xctx, &err, (xmlnode *)doc, "stdio", stdout, NULL);
/* Insert the document to 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();}
1 row selected.
|
See Also: Appendix H, " Oracle XML DB-Supplied XML Schemas and Additional Examples" for a more detailed listing of this example |
When loading very large XML files consisting of a collection of smaller XML documents, into Oracle Database, if it is often more efficient to use Simple API for XML (SAX) parsing to break the file into a set of smaller documents before inserting the 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.
The "SAX Loader Application", available on the Oracle Technology Network (OTN) site at http://otn.oracle.com/sample_code/tech/xml/xmldb/content.html, demonstrates how to do this.
Use 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-2 compares these modes.
Table 3-2 Comparing SQL*Loader Conventional and Direct 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. |
|
See Also:
|
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 Oracle XML DB repository under a given path, you can use PL/SQL package DBMS_XDB. This is illustrated by the following example.
Example 3-6 Inserting XML Content Into XML DB Repository Using PL/SQL DBMS_XDB
declare
res boolean;
begin
res := dbms_xdb.createResource('/home/SCOTT/purchaseOrder.xml',
bfilename('XMLDIR','purchaseOrder.xml'),
nls_charset_id('AL32UTF8'));
end;/
PL/SQL procedure successfully completed.
Many operations for configuring and using Oracle XML DB are based on processing one or more XML documents. For example, 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, the WebDAV protocol, 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 Oracle XML DB repository.
Figure 3-1 Using Windows Explorer to Load Content Into Oracle XML DB Repository
Here the folder tree contains an XML schema document, an HTML page, and a couple of XSLT style sheets.
|
Note: Oracle XML DB repository can also store non-XML content, such as HTML files, JPEG images, word documents, and so on, as well as both XML schema-based and non-XML schema-based XML documents. |
This section describes how to load documents that are formatted in non-ASCII character sets.
According to XML 1.0 Reference, 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 which character encoding is 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 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. Note that since ASCII is a subset of UTF-8, ordinary ASCII entities do not require an encoding declaration.
In many cases, external sources of information are available in addition to 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 request as follows:
Content-Type: text/xml; charset=ISO-8859-4
In releases prior to Oracle Database 10g Release 1, all XML documents are assumed to be in the database character set regardless of the document's 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 data type, then the encoding declaration is ignored because these two data types are always encoded in the database character set. In addition, when loading data to XML DB, either through programmatic APIs or transfer protocols, you can provide external encoding to override the document's internal encoding declaration. An error is raised if a schema-based XML document containing characters that are not legal in the determined encoding is loaded into XML DB.
The following examples show some ways which external encoding can be specified:
Using the PL/SQL package DBMS_XDB.CreateResource to create a resource from a BFILE, you can specify the file encoding through the csid argument. If a zero csid is specified then the file encoding is auto-detected from the document's 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/mypo.xml', xbfile,
file_csid);
end;/
When loading documents into XML DB through FTP protocol, you can specify the quote set_charset command to indicate the encoding of the files subsequently sent to the server.
FTP> quote set_charset Shift_JIS FTP> put mypo.xml
When using the HTTP protocol, you can specify the encoding of the data transmitted to XML DB in the request header as follows:
Content-Type: text/xml; charset= EUC-JP
XML documents stored in XML DB can be retrieved using transfer protocols programmatic APIs. In Oracle Database releases prior to 10g release 1, XML data is retrieved only in the database character set. Starting with 10g release 1, you can specify the encoding of the retrieved data.The following examples show different ways to specify the output encoding:
Using programmatic APIs, you can retrieve XML data into VARCHAR, CLOB, or XMLType datatypes. When using these techniques, you can control the encoding of the retrieved data by setting the NLS_LANG environment variable to an Oracle Database-supported character set. See the Oracle Database Globalization Support Guide for details on setting the NLS_LANG environment variable.
Also, methods are provided on the XMLType and URIType classes to retrieve XML data into a BLOB datatype. Using these methods, you can specify the desired character set of the returned BLOB value through the csid argument.
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 any_path = pathname;
return xblob;
end;/
Using the FTP quote set_nls_locale command:
FTP> quote set_nls_locale EUC-JP FTP> get mypo.xml
Using the Accept-Charset parameter in the HTTP request:
/httptest/mypo.xml 1.1 HTTP/Host: localhost:2345 Accept: text/* Accept-Charset: iso-8859-1, utf-8
|
See Also: For more information on specifying external coding: |
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 Notepad 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 datatypes. 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 a standard W3C XML schema example fragment, in its native form, as an XML Document:
Example 3-7 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="1"/>
<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>
1 row selected.
|
See Also: Appendix B, "XML Schema Primer" for a more detailed listing ofPurchaseOrder.xsd |
Figure 3-2 shows the PurchaseOrder 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. For example:
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
XML schema are used with Oracle XML DB for a number of reasons.
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 datatype 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 should be stored inside the database. Currently Oracle XML DB provides two options:
Unstructured storage. The content of the XMLType is persisted as XML text using a CLOB datatype. This option is available for non-schema-based and schema-based XML content. When the XML is to be stored and retrieved as complete documents, unstructured storage may be the best solution as it offers the fastest rates of throughput when storing and retrieving XML content.
Structured storage. The content of the XMLType is persisted as a set of SQL objects. The structured storage option is only available when the XMLType table or column has been constrained to a global element defined by XML schema.
If there is a need to extract or update sections of the document, perform XSL transformation on the document, or work through the DOM API, then structured storage may be the preferred storage type. Structured storage allows all these operations to take place more efficiently but at a greater overhead when storing and retrieving the entire document.
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 datatypes 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 allows Oracle XML DB to 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 allows vendor-specific information to be added to an XML schema. Oracle XML DB uses this to control the mapping between the XML schema and the SQL object model.
Annotating an XML schema allows control over the naming of the SQL objects and attributes created. Annotations can also be used to override the default mapping between the XML schema data types and SQL data types and to specify which table should be used to store the data.
Annotations are also used to control how collections in the XML are stored in the database. Currently there are four options:
Character Large Object (CLOB). The entire set of elements is persisted as XML text stored in a CLOB column.
VARRAY in LOB. Each element in the collection is converted into a SQL object. The collection of SQL objects is serialized and stored in a LOB column.
VARRAY as a nested table. Each element in the collection is converted into a SQL object. The collection of SQL objects is stored as a set of rows in an Index Organized Nested Table (IOT).
VARRAY as XMLType. Each element in the collection is treated as a separate XMLType. The collection of XMLType values is stored as a set of rows in an XMLType table.
These storage options allow you to tune the performance of applications that use XMLType datatypes to store XML in the database.
However, there is no requirement to annotate an XML schema before using it with Oracle XML DB. Oracle XML DB uses a set of default assumptions when processing an XML schema that contains no annotations.
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:
xmlns:xdb="http://xmlns.oracle.com/xdb"
to the root element of the XML schema. Note the use of a namespace prefix. This makes it possible to abbreviate the namespace to xdb when adding annotations.
Example 3-8 shows the PurchaseOrder XML schema with annotations.
Example 3-8 Annotated 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="1"/>
<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>
1 row selected.
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 XML schema also uses the following annotations:
defaultTable annotation in the PurchaseOrder element. This specifies that XML documents, compliant with this XML schema are stored in a table called PURCHASEORDER.
SQLType annotation. The first occurrence of SQLType specifies that the name of the SQL type generated from complexType PurchaseOrderType is PURCHASEORDER_T.
SQLName annotation. This provides an explicit name for the each SQL attribute of PURCHASEORDER_T.
SQLType annotation. The second occurrence of SQLType specifies that the name of the SQL type generated from the complexType LineItemType is LINEITEM_T and the SQL type that manages the collection of LineItem elements is LINEITEM_V.
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. Once 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, once it is registered with the 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 often referred to as the Schema Location Hint.
XML schema registration is performed using a simple PL/SQL procedure, dbms_xmlschema.registerschema(). See Example 3-9. By default, when an XML schema is registered, Oracle XML DB automatically generates all the SQL object types and XMLType tables required to manage the instance documents.
XML schemas can be registered as global or local. See Chapter 5, " XML Schema Storage and Query: The Basics" for a discussion of the differences between global and local schemas.
Example 3-9 Using the DBMS_XMLSCHEMA Package to Register an XML Schema
begin
dbms_xmlschema.registerSchema(
'http://localhost:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd',
xdbURIType('/home/SCOTT/poSource/xsd/purchaseOrder.xsd').getClob(),
TRUE,TRUE,FALSE,TRUE
);
end;
/
PL/SQL procedure successfully completed.
In this example the unique identifier for the XML schema is: http://localhost:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd
The XML schema document was previously loaded into Oracle XML DB repository at the path: /home/SCOTT/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 the registerSchema() procedure 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.
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 complexTypes 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 -- desc LINEITEMS_T LINEITEMS_T is NOT FINAL Name Null? Type --------------------------------------- ------------------------------------------------- SYS_XDBPD$ XDB.XDB$RAW_LIST_T LINEITEM LINEITEM_V -- desc 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
These examples show 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 'shredded' (or 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. LINEITEMS_T consists of a single attribute LINEITEM, defined as an instance of 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 the error ORA-01792: maximum number of columns in a table or view is 1000 is encountered when registering an XML schema or creating a table based on a global element defined by an XML schema. This error occurs 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 error only occurs when creating an XMLType table or column that uses object-relational storage. When object-relational storage is selected the XMLType is persisted as a SQL type. When a table or column is based on a SQL type, each attribute defined by the type counts as a column in the underlying table. If the SQL type contains attributes that are based on other SQL types, the attributes defined by those types also count as columns in the underlying table. If the total number of attributes in all the SQL types exceeds the Oracle Database limit of 1000 columns in a table the storage table cannot be created. This means that as the total number of elements and attributes defined by a complexType approaches 1000, it is no longer possible to create a single table that can manage the SQL objects generated when an instance of the type is stored in the database.To resolve this you must reduce the total number of attributes in the SQL types that are used to create the storage tables. Looking at the schema there are two approaches for achieving this:
Using a top-down technique with multiple XMLType tables that manage the XML documents. This technique reduces the number of SQL attributes in the SQL type hierarchy for a given storage table. As long as none of the tables have to manage more than 1000 attributes, the problem is resolved.
Using a bottom-up technique that reduces the number of SQL attributes in the SQL type hierarchy, collapsing some of 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 sub-elements 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 define a choice where each element within the choice is defined as a complexType, or where the XML schema defines an element based on a complexType that contains a very 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 complexTypes 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 type of queries and updates to be performed against the data.
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. If an XML schema contains a large number of global element definitions it can cause significant overhead in processor time and space used. There are two ways to avoid this:
Add the annotation xdb:defaultTable="" to every global element that does not appear as the root element of an instance document.
Set the genTables parameter 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.
Once the XML schema has been registered with Oracle XML DB, it can be referenced when defining tables that contain XMLType columns, or when creating XMLType tables.
Example 3-11 shows how to manually create the PurchaseOrder table, the default table for PurchaseOrder elements, as defined by the PurchaseOrder XML schema.
Example 3-11 Creating an XMLType Table that Conforms to an XML Schema
CREATE TABLE PurchaseOrder of XMLType XMLSCHEMA "http://localhost:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd" ELEMENT "PurchaseOrder" varray "XMLDATA"."ACTIONS"."ACTION" STORE AS table ACTION_TABLE ( (primary key (NESTED_TABLE_ID, ARRAY_INDEX)) organization index overflow ) varray "XMLDATA"."LINEITEMS"."LINEITEM" store as table LINEITEM_TABLE ( (primary key (NESTED_TABLE_ID, ARRAY_INDEX)) organization index overflow ); Table created.
In this example each member of the VARRAY that manages the collection of LineItem elements is stored as a row in nested table LINEITEM_TABLE. Each member of the VARRAY that manages the collection of Action elements is stored in the nested table ACTION_TABLE. The nested tables are index organized and automatically contain the NESTED_TABLE_ID and ARRAY_INDEX columns required to link them back to the parent column.
The CREATE TABLE statement is equivalent to the CREATE TABLE statement automatically generated by Oracle XML DB if the schema annotation storeVarrayAsTable="true" was included in the root element of the PurchaseOrder XML schema. Note that when this annotation is used to create nested tables, the nested tables are given system-generated names. Since these names are somewhat difficult to work with, nested tables generated by the XML schema registration process can be given more meaningful names using the SQL statement, RENAME TABLE.
A SQL*Plus DESCRIBE statement, abbreviated to desc, can be used to view information about an XMLType table.
Example 3-12 Using DESCRIBE for an XML Schema-Based XMLType Table
desc PURCHASEORDER Name Null? Type ----------------------------- ------------------------------------------------- TABLE of SYS.XMLTYPE(XMLSchema "http://localhost:8080/home/SCOTT/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. This means that 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 XML schema it is associated with. There are two ways to achieve 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 the XMLType createSchemaBasedXML() method.
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 allows the Oracle XML DB protocol servers to recognize that an XML document inserted into Oracle XML DB repository is an instance of a registered XML schema. This means that the content of the instance document is automatically stored in the default table defined 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 called the Schema Location Hint. This is the unique identifier passed to dbms_xmlschema.registerSchema() when the XML 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/home/SCOTT/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 the attribute is a pair of values separated by a space. The left hand side of the pair is the value of the target namespace declared in the XML schema. The right hand side of the pair is the Schema Location Hint, the unique identifier passed to dbms_xmlschema.registerSchema() when the XML schema is registered with the database.
For example, assume that the PurchaseOrder XML schema includes a target namespace declaration. The root element of the XML schema would look something like:
<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"/>
and 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/home/SCOTT/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 causes an ORA-19007 error. Example 3-13 illustrates this.
Example 3-13 ORA-19007 Error From Attempting to Insert an Incorrect XML Document
INSERT INTO PURCHASEORDER
VALUES
(
XMLType
(
bfilename('XMLDIR','Invoice.xml'),
nls_charset_id('AL32UTF8')
)
);
INSERT INTO PURCHASEORDER
*
ERROR at line 1:
ORA-19007: Schema - does not match expected
http://localhost:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd.
This error only occurs when content is inserted directly into an XMLType table. This means 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's 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 within XML documents whereas database (SQL) constraints are enforced across sets of XML documents. |
This section describes the differences between the partial and full XML schema validation used when inserting XML documents into the database.
When an XML document is inserted into an XML schema-based XMLType table or column Oracle XML DB performs a partial validation of the document. A partial validation ensures that all the mandatory elements and attributes are present and that there are no unexpected elements or attributes in the document. It ensures that the structure of the XML document conforms to the SQL type definitions that were derived from the XML schema. However, it does not ensure that the instance document is fully compliant with the XML schema. Example 3-14 provides an example of failing a partial validation while inserting an XML document into table PurchaseOrder:
Example 3-14 ORA-19007 When Inserting Incorrect XML Document (Partial Validation)
INSERT INTO PURCHASEORDER
VALUES
(
XMLType
(
bfilename('XMLDIR','InvalidElement.xml'),
nls_charset_id('AL32UTF8')
)
);
XMLType
*
ERROR at line 4:
ORA-30937: No schema definition for 'UserName' (namespace '##local') in parent
'PurchaseOrder'
ORA-06512: at "SYS.XMLTYPE", line 259
ORA-06512: at "SYS.XMLTYPE", line 284
ORA-06512: at line 1
When full validation of the instance document against the XML schema is required, you can enable XML schema validation 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 the XMLisValid() SQL function and 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.
Full XML Schema validation costs CPU and memory. By leaving the decision on whether or not to force a full XML schema validation to you, Oracle XML DB lets you perform full XML schema validation only when necessary. If you can rely on the application validating the XML document, you can obtain higher overall throughput by avoiding overhead associated with a full validation. If you cannot be sure about the validity of the incoming XML documents, you can rely on the database to ensure that the XMLType table or column only contains schema-valid XML documents.
In Example 3-15 the XML document, InvalidReference, is a not a valid XML document according 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 are present, attempts to insert this document into the database would succeed. Example 3-15 shows how to force a full XML schema validation by adding a CHECK constraint to an XMLType table.
Example 3-15 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, namely one that does not pass the CHECK constraint, into the table fails:
ALTER TABLE PURCHASEORDER
ADD constraint VALIDATE_PURCHASEORDER
CHECK (XMLIsValid(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 (SCOTT.VALIDATE_PURCHASEORDER) violated
Note that the pseudo column name object_value can be used to access the content of an XMLType table from within a trigger.
Example 3-16 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.
Example 3-16 Using BEFORE INSERT Trigger to Enforce Full XML Schema Validation
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(getFileContent('InvalidReference.xml')));
VALUES (xmltype(getFileContent('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 333
ORA-06512: at "SCOTT.VALIDATE_PURCHASEORDER", line 3
ORA-04088: error during execution of trigger 'SCOTT.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 not currently addressed by the W3C XML Schema Recommendation. These include the following:
The ability to define that the value of an element or attribute has to be unique across a set of XML documents (a UNIQUE constraint)
That the value of an element or attribute must exist in some data source outside the current document (a FOREIGN KEY constraint)
The mechanisms used to enforce integrity on XML are the same mechanisms used to enforce integrity on conventional relational data. In other words, simple rules such as uniqueness and foreign-key relationships, are enforced by specifying constraints. More complex rules are enforced by specifying database triggers. Example 3-17 and Example 3-18 illustrate how you can use SQL constraints to enforce referential integrity.
Oracle XML DB makes it possible to implement database-enforced business rules on XML content, in addition to rules that can be specified using the 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-17 Applying Database Integrity Constraints and Triggers to an XMLType Table
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')
)
);
INSERT INTO PURCHASEORDER
*
ERROR at line 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 "SCOTT.VALIDATE_PURCHASEORDER", line 3
ORA-04088: error during execution of trigger 'SCOTT.VALIDATE_PURCHASEORDER'
--
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 (SCOTT.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 (SCOTT.USER_IS_VALID) violated - parent key not
found
The unique constraint REFERENCE_IS_UNIQUE enforces the rule that the value of the node /PurchaseOrder/Reference/text() is unique across all documents stored in the PURCHASEORDER table. The foreign key constraint USER_IS_VALID enforces the rule that the value of the node /PurchaseOrder/User/text() corresponds to one of the values in the EMAIL column in the EMPLOYEES table.
Oracle XML DB constraints must be specified in terms of attributes of the SQL types used to manage the XML content.
The following examples show how database-enforced data integrity ensures that only XML documents that do not violate the database-enforced referential constraints can be stored in the PURCHASEORDER 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 results in the constraint REFERENCE_IS_UNIQUE being violated.
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. This means attempting to store this document in Oracle XML DB results in the constraint USER_IS_VALID being violated.
INSERT INTO PURCHASEORDER
VALUES (xmltype(getFileContent('PurchaseOrder.xml')));
1 row created.
INSERT INTO PURCHASEORDER
VALUES (xmltype(getFileContent('DuplicateReference.xml')));
insert into PURCHASEORDER*
ERROR at line 1:
ORA-00001: unique constraint (SCOTT.REFERENCE_IS_UNIQUE) violated
INSERT INTO PURCHASEORDER
VALUES (xmltype(getFileContent('InvalidUser.xml')));
insert into PURCHASEORDER*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.USER_IS_VALID) violated - parent key not
found
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-18 demonstrates 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.
Example 3-18 Enforcing Database Integrity When Loading XML Using FTP
$ 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): SCOTT 331 pass required for SCOTT Password: 230 SCOTT logged in ftp> cd /home/SCOTT 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 "SCOTT.VALIDATE_PURCHASEORDER", line 3 ORA-04088: error during execution of trigger 'SCOTT.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 (SCOTT.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 (SCOTT.USER_IS_VALID) violated - parent key not found 550 End Error Response
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, simply 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 new 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. Every 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 may contain indexes 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.
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 Object_Value
Accessing Fragments or Nodes of an XML Document Using extract()
Accessing Text Nodes and Attribute Values Using extractValue()
Using XMLSequence() to Perform SQL Operations on XMLType Fragments
Examples in this section are based on the following PurchaseOrder XML document:
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080/home/SCOTT
/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.
The object_value keyword 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 prior releases.
The SQL*Plus settings PAGESIZE and LONG ensure that the entire document is printed correctly without line breaks.
Example 3-19 Using object_value to Retrieve an Entire XML Document
set long 10000
set pagesize 100
set linesize 132
--
SELECT object_value
FROM PURCHASEORDER;
OBJECT_VALUE
--------------------------------------------------------------------------------
----------------------------------------------------
<PurchaseOrder xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:noNamespaceSchemaLocation="http://localhost:8080/home/SCOTT
/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.
The extract() function returns the node or nodes that match the XPath expression. Nodes are returned as an instance of XMLType. The results of extract() can be either a document or DocumentFragment. The functionality of extract() is also available through the XMLType datatype, extract() method.
Example 3-20 Accessing XML Fragments Using extract()
The following SQL statement returns an XMLType containing the Reference element that matches the XPath expression.
set pages 100 set linesize 132 set long 10000 -- SELECT extract(object_value,'/PurchaseOrder/Reference') FROM PURCHASEORDER; EXTRACT(OBJECT_VALUE,'/PURCHASEORDER/REFERENCE') -------------------------------------------------------------------------------- <Reference>SBELL-2002100912333601PDT</Reference> 1 row selected.
The following statement returns an XMLType 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 SQL statement returns an XMLType containing the three Description elements that match the XPath expression. The three Description elements are returned as nodes in a single XMLType. This means that the XMLType does not have a single root node. Consequently it is treated as an XML DocumentFragment.
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.
The extractValue() function returns the value of the text node or attribute value that matches the supplied XPath expression. The value is returned as a SQL scalar datatype. This means that the XPath expression passed to extractValue() must uniquely identify a single text node or attribute value within the document.
Example 3-21 Accessing a Text Node Value Matching an XPath Expression Using extractValue()
The following SQL statement returns the value of the text node associated with the Reference element that matches the XPath expression. The value is returned as a VARCHAR2 datatype.
SELECT extractValue(object_value,'/PurchaseOrder/Reference') FROM PURCHASEORDER; EXTRACTVALUE(OBJECT_VALUE,'/PU ------------------------------ SBELL-2002100912333601PDT 1 row selected.
The following SQL statement returns the value of the text node associated with the Description element associated with the first LineItem element. The value is returned as VARCHAR2 datatype. Note the use of the Index to identify which of the LineItem nodes should be processed.
SELECT extractValue(object_value, '/PurchaseOrder/LineItems/LineItem[1]/Description') FROM PURCHASEORDER; EXTRACTVALUE(OBJECT_VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[1]/DESCRIPTION') ----------------------------------------------------------------------------- A Night to Remember 1 row selected.
The following SQL statement returns the value of the text node associated with the Description element, in turn associated with the LineItem element. The LineItem element contains an Id attribute with the specified value. The value is returned as VARCHAR2 datatype. Note how the predicate that identifies which LineItem to process is enclosed in Square Brackets ([]). The at-sign character (@) specifies that Id is an attribute rather than an element.
SELECT extractValue(object_value, '/PurchaseOrder/LineItems/LineItem[Part/@Id="715515011020"]/Description') FROM PURCHASEORDER; EXTRACTVALUE(OBJECT _VALUE,'/PURCHASEORDER/LINEITEMS/LINEITEM[PART/@ID="715515011020"]/DESCRIPTION') -------------------------------------------------------------------------------- Sisters 1 row selected.
The following examples show invalid uses of extractValue(). In the first example the XPath expression matches three nodes in the document. In the second example the XPath expression identifies a node tree, not a text node or attribute value.
Example 3-22 Invalid Uses of extractValue()
SELECT extractValue(object
_value,'/PurchaseOrder/LineItems/LineItem/Description')
FROM PURCHASEORDER;
SELECT extractValue(object
_value,'/PurchaseOrder/LineItems/LineItem/Description')
*
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row
--
SELECT extractValue(object_value,'/PurchaseOrder/LineItems/LineItem[1]')
FROM PURCHASEORDER;
FROM PURCHASEORDER
*
ERROR at line 2:
ORA-19026: EXTRACTVALUE can only retrieve value of leaf node
--
SELECT extractValue(object
_value,'/PurchaseOrder/LineItems/LineItem/Description/text()')
FROM PURCHASEORDER;
SELECT extractValue(object
_value,'/PurchaseOrder/LineItems/LineItem/Description/text()')
*
Note that depending on whether or not XPath rewrite takes place, the two preceding statements can also result in the following error being reported:
ORA-01427: single-row subquery returns more than one row
The existsNode function evaluates whether or not a given document contains a node which matches a W3C XPath expression. The existsNode() function returns true (1) if the document contains the node specified by the XPath expression supplied to the function and false (0) if it does not. Since XPath expressions can contain predicates existsNode() can determine whether or not a given node exists in the document, or whether or not a node with the specified value exists in the document. The functionality provided by the existsNode() function is also available through the XMLType datatype existsNode() method.
Example 3-23 Searching XML Content Using the existsNode() Function
This example checks if the XML document contains a root element named Reference that is a child of the root element PurchaseOrder:
SELECT COUNT(*)
FROM PURCHASEORDER
WHERE existsNode(object_value,'/PurchaseOrder/Reference') = 1;
COUNT(*)
----------
132
The following example checks if the value of the text node associated with the Reference element is SBELL-2002100912333601PDT:
SELECT count(*)
FROM PURCHASEORDER
WHERE existsNode(object_value,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
COUNT(*)
----------
1
1 row selected.
The following example checks if the value of the text node associated with the Reference element is SBELL-XXXXXXXXXXXXXXXXXX:
SELECT count(*)
FROM PURCHASEORDER
WHERE existsNode(object_value,'/PurchaseOrder/Reference[Reference="SBELL-XXXXXXXXXXXXXXXXXX"]') = 1;
COUNT(*)
----------
0
1 row selected.
The following example checks if the XML document contains a root element PurchaseOrder that contains a LineItems element containing a LineItem element, which in turn contains a Part element with an Id attribute:
SELECT count(*)
FROM PURCHASEORDER
WHERE existsNode(object_value,'/PurchaseOrder/LineItems/LineItem/Part/@Id') = 1;
COUNT(*)
----------
132
1 row selected.
The following checks if the XML document contains a root element PurchaseOrder that contains a LineItems element, contain a LineItem element which contains a Part element where the value of the Id attribute is 715515009058:
SELECT count(*)
FROM PURCHASEORDER
WHERE existsNode(object_value,'/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]') = 1;
COUNT(*)
----------
21
The following checks if the XML document contains a root element PurchaseOrder that contains LineItems element, where the third LineItem element contains a Part element where the value of the Id attribute is 715515009058:
SELECT count(*)
FROM PURCHASEORDER
WHERE existsNode(object_value,'/PurchaseOrder/LineItems/LineItem[3]/Part[@Id="715515009058"]') = 1;
COUNT(*)
----------
1
1 row selected.
The following query shows how to use extractValue() to limit the results of the SELECT statement to those 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 extractValue(object_value,'/PurchaseOrder/Reference') "Reference" FROM PURCHASEORDER WHERE extractValue(object_value,'/PurchaseOrder/User') LIKE 'S%'; Reference ------------------------------ SBELL-20021009123336231PDT SBELL-20021009123336331PDT SKING-20021009123336321PDT ... 36 rows selected.
The following query shows how to use extractValue() to perform a join based on the values of a node in an XML document and data in another table.
SELECT extractValue(object_value,'/PurchaseOrder/Reference') "Reference" FROM PURCHASEORDER, HR.EMPLOYEES e WHERE extractValue(object_value,'/PurchaseOrder/User') = e.EMAIL AND e.EMPLOYEE_ID = 100; Reference ------------------------------ 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.
The preceding examples demonstrated how extractValue() can be used in the SELECT list to return information contained in an XML document. You can also use these functions in the WHERE clause to determine whether or not a document must be included in the resultset of a SELECT, UPDATE, or DELETE statement.
You can use existsNode() to restrict the resultset to those documents containing nodes that match an XPath expression. You can use extractValue() when joining across multiple tables based on the value of one or more nodes in the XML document. Also use existsNode() when specifying the condition in SQL is easier than specifying it with XPath.
Example 3-24 Limiting the Results of a SELECT Using existsNode() and extractValue() in the WHERE Clause
The following query shows how to use existsNode() to limit the results of the SELECT statement to rows where the text node associated with the User element contains the value SBELL.
SELECT extractValue(object_value,'/PurchaseOrder/Reference') "Reference" FROM PURCHASEORDER WHERE existsNode(object_value,'/PurchaseOrder[User="SBELL"]') = 1; 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-25 Finding the Reference for any PurchaseOrder Using extractValue() and existsNode()
This example combines extractValue() and existsNode() to find the Reference for any PurchaseOrder where the first LineItem element contains an order for the item with the Id 715515009058. In this example the existsNode() function is used in the WHERE clause to determine which rows are selected, and the extractValue() function is used in the SELECT list to control which part of the selected documents appear in the result.
SELECT extractValue(object_value,'/PurchaseOrder/Reference') "Reference" FROM PURCHASEORDER WHERE existsNode(object_value,'/PurchaseOrder/LineItems/LineItem[1]/Part[@Id="715515009058"]') = 1; Reference ------------------------------ SBELL-2002100912333601PDT 1 row selected.
Example 3-20 demonstrated how the extract() function returns an XMLType containing the node or nodes that matched the supplied XPath expression. When the document contains multiple nodes that match the supplied XPath expression, extract() returns a document fragment containing all of the matching nodes. A fragment differs from a document in that it may contain multiple root elements which may be unrelated.
This kind of result is very common when the extract() function is used to retrieve the set of elements contained in a collection (in this case each node in the fragment will be of the same type), or when the XPath expression terminates in a wildcard (where the nodes in the fragment will be of different types).
The XMLSequence() function makes it possible to take an XMLType containing a fragment and perform SQL operations on it. It generates a collection of XMLType objects from an XMLType containing a fragment. The collection contains one XMLType for each of the root elements in the fragment. This collection of XMLType objects can then be converted into a virtual table using the SQL table() function. Converting the fragment into a virtual table makes it easier to use SQL to process the results of an extract() function that returned multiple nodes.
Example 3-26 Using XMLSequence() and Table() to view Description Nodes
The following example demonstrates how to access the text nodes for each Description element in the PurchaseOrder document.
The initial approach, based on using extractValue(), fails as there is more then one Description element in the document.
SELECT extractValue(p.object_value,'/PurchaseOrder/LineItems/LineItem/Description') FROM purchaseorder p WHERE existsNode(p.object_value,'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; SELECT extractValue(p.object_value,'/PurchaseOrder/LineItems/LineItem/Description') * ERROR at line 1: ORA-01427: single-row subquery returns more than one row
Next use extract() to access the required values. This returns the set of Description nodes as a single XMLType object containing a fragment consisting of the three Description nodes. This is better but not ideal because the objective is to perform further SQL-based processing on the values in the text nodes.
SELECT extract(p.object_value, '/PurchaseOrder/LineItems/LineItem/Description') FROM purchaseorder p WHERE existsNode(p.object _value,'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; EXTRACT(P.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 you must convert the collection of Description nodes into a virtual table using the XMLSequence() and table() functions. These functions convert the three Description nodes retuned by extract() into a virtual table consisting of three XMLType objects, each of which contains a single Description element.
SELECT value(d) FROM purchaseorder p, table (xmlsequence(extract(p.object _value,'/PurchaseOrder/LineItems/LineItem/Description'))) d WHERE existsNode(p.object _value,'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; VALUE(D) -------------------------------------------------------------------------------- <Description>A Night to Remember</Description> <Description>The Unbearable Lightness Of Being</Description> <Description>Sisters</Description> 3 rows selected.
Since each XMLType in the virtual table contains a single Description element, extractValue() function can be used to access the value of the text node associated with the each Description element.
SELECT extractValue(value(d),'/Description') FROM purchaseorder p, table (xmlsequence(extract(p.object _value,'/PurchaseOrder/LineItems/LineItem/Description'))) d WHERE existsNode(p.object _value,'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1; EXTRACTVALUE(VALUE(D),'/DESCRIPTION') -------------------------------------------------------------------------------- A Night to Remember The Unbearable Lightness Of Being Sisters 3 rows selected.
|
Note: There is a correlated join between the results of thetable() function and the row operated on by the extract() function. This means that the table that provides input to the extract() function must appear before the table() operator in the FROM list. The correlated join ensures a 1 : N relationship between the rows generated by the table() function and the row containing the value that was processed by the extract() function. |
Example 3-27 Counting the Number of Elements in a Collection Using XMLSequence()
The following example demonstrates using XMLSequence() to count the number of elements in a collection. It also shows how SQL functionality such as ORDER BY and GROUP BY can be applied to results of the extractValue() operator.
In this case the query will first locate the set of the XML documents that match the XPath expression contained in the existsNode() function. It will then generate 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. The correlated join ensures that the GROUP BY correctly determines which LineItems belong to which PurchaseOrder.
SELECT extractValue(p.object_value,'/PurchaseOrder/Reference'), count(*) FROM PURCHASEORDER p, table (xmlsequence(extract(p.object_value, '/PurchaseOrder/LineItems/LineItem'))) d 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-28 Counting the Number of Child Elements in an Element Using XMLSequence()
The following example demonstrates using XMLSequence() to count the number of child elements of a given element. The XPath expression passed to the extract() function contains a wildcard that matches the elements that are direct descendants of the PurchaseOrder element. The XMLType returned by extract() will contain the set of nodes which match the XPath expression. The XMLSequence() function transforms each root element in the fragment into a separate XMLType object, and the table() function converts the collection returned by XMLSequence() into a virtual table. Counting the number of rows in the virtual table provides the number of child elements in the PurchaseOrder element.
SELECT count(*)
FROM PURCHASEORDER p,
TABLE (xmlSequence(extract(p.object_value,'/PurchaseOrder/*'))) n
WHERE existsNode(p.object_value,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
COUNT(*)
----------
9
1 row selected.
These sections describe features for accessing and updating Oracle XML DB repository content.
Another benefit of XML DB repository is that it can be queried from SQL. Content stored in Oracle XML DB 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 issue a query to determine how many documents with an .xsl extension are under a location other than /home/mystylesheetdir.
For document access, you can also mix path-based repository access with content-based access. For example, "how many documents not under /home/purchaseOrders have a node named /PurchaseOrder/User/text() with a value of DRAKE?
All the metadata for managing Oracle XML DB repository is stored in a database schema owned by the database user XDB. This user 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 file or folder in Oracle XML DB 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.oralce.com/xdb/XDBResource.xsd.
XDB$RESOURCE table 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 metadata and content of documents stored in Oracle XML DB repository. Both views contain a virtual column, RES. Use RES to access and update resource documents with SQL statements based on a path notation. Operations on the views use underlying tables in Oracle XML DB repository.
Use exists_Path() and under_Path() Operators to Include Path-Based Predicates in the WHERE Clause
Oracle XML DB includes two repository specific SQL operators: exists_path() and under_path(). Use these operators to include path-based predicates in the WHERE clause of a SQL statement. This means that SQL operations can select repository content based on the location of the content in the folder hierarchy. The Hierarchical Index ensures that path-based queries are executed efficiently.
When XML schema-based XML documents are stored in Oracle XML DB repository the document content is stored as an object in the default table identified by the XML schema. The repository contains metadata about the document and a pointer (REF of XMLType) identifies the row in the default table that contains the content.
It is also possible to store other kinds of documents in the repository. When non-XML and non-XML schema-based XML documents are stored in the repository, the documents' content is stored in a LOB along with the metadata about the document.
Since Oracle XML DB repository can be accessed and updated using SQL, any application capable of calling a PL/SQL procedure can work with Oracle XML DB 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 XML DB repository Access Control Lists (ACLs).
With supplied PL/SQL packages DBMS_XDB, DBMS_XDBZ, and DBMS_XDB_VERSION, SQL programmers can perform common tasks on the repository itself. Methods provided by the packages make it possible to create, delete, and rename documents and folders, to move a file or folder within the folder hierarchy, to set and change the access permissions on a file or folder, and the ability to initiate and manage versioning.
The following example shows PL/SQL package DBMS_XDB used to create a set of subfolders beneath folder /home/SCOTT.
connect &1/&2@&3
DECLARE
RESULT boolean;
BEGIN
if (not xdb_utilities.ResourceExists('/home/' || USER || '/poSource')) then
result := dbms_xdb.createFolder('/home/' || USER || '/poSource');
end if;
if (not xdb_utilities.ResourceExists('/home/' || USER || '/poSource/xsd')) then
result := dbms_xdb.createFolder('/home/' || USER || '/poSource/xsd');
end if;
if (not xdb_utilities.ResourceExists('/home/' || USER || '/poSource/xsl')) then
result := dbms_xdb.createFolder('/home/' || USER || '/poSource/xsl');
end if;
result := dbms_xdb.createFolder('/home/' || USER || '/purchaseOrders');
END;/
--
-- Refresh the contents of WebDAV folder to show that new directories have been created.
--
PAUSE
--
-- The new directories were not visible from WebDAV as the transaction had not been committed.
-- Issue a COMMIT statement and then refresh the contents of the WebDAV folder.
-- The new directories should now be visible as the transaction that created them have been
-- committed.
--
COMMIT/
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 allows programmers, tools, and applications that understand Oracle Database, but not XML, to work with XML content stored in the database.
The views use XPath expressions and functions such as extractValue() to define the mapping between columns in the view and nodes in the XML document. For performance reasons this approach is recommended when XML documents are stored as XMLType, that is, stored using object-relational storage techniques.
Example 3-29 Creating Relational Views On XML Content
This example shows how to create a simple relational view that exposes XML content in a relational manner:
CREATE OR REPLACE view PURCHASEORDER_MASTER_VIEW (REFERENCE, REQUESTOR, USERID, COSTCENTER, SHIP_TO_NAME,SHIP_TO_ADDRESS, SHIP_TO_PHONE, INSTRUCTIONS) AS SELECT extractValue(value(p),'/PurchaseOrder/Reference'), extractValue(value(p),'/PurchaseOrder/Requestor'), extractValue(value(p),'/PurchaseOrder/User'), extractValue(value(p),'/PurchaseOrder/CostCenter'), extractValue(value(p),'/PurchaseOrder/ShippingInstructions/name'), extractValue(value(p),'/PurchaseOrder/ShippingInstructions/address'), extractValue(value(p),'/PurchaseOrder/ShippingInstructions/telephone'), extractValue(value(p),'/PurchaseOrder/SpecialInstructions') FROM PURCHASEORDER p; 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 created view PURCHASEORDER_MASTER_VIEW. There will be one row in the view for each row in table PURCHASEORDER.
The CREATE VIEW statement defines the set of columns that will make up the view. The SELECT statement uses XPath expressions and the extractValue() function to map between the nodes in the XML document and the columns defined by the view. This technique can be used when there is a 1:1 relationship between documents in the XMLType table and the rows in the view.
Example 3-30 Using a View to Access Individual Members of a Collection
This example shows how to use extract() and xmlSequence() for a 1:many relationship between the documents in the XMLType table and rows in the view. This situation arises when the view must provide access to the individual members of a collection and expose the members of a collection as a set rows.
CREATE OR REPLACE VIEW PURCHASEORDER_DETAIL_VIEW (REFERENCE, ITEMNO, DESCRIPTION, PARTNO, QUANTITY, UNITPRICE) AS SELECT extractValue(value(p),'/PurchaseOrder/Reference'), extractvalue(value(l),'/LineItem/@ItemNumber'), extractvalue(value(l),'/LineItem/Description'), extractvalue(value(l),'/LineItem/Part/@Id'), extractvalue(value(l),'/LineItem/Part/@Quantity'), extractvalue(value(l),'/LineItem/Part/@UnitPrice') FROM PURCHASEORDER p, TABLE (xmlsequence(extract(value(p),'/PurchaseOrder/LineItems/LineItem'))) l; View created. -- describe PURCHASEORDER_DETAIL_VIEW Name Null? Type -------------------------------------------------------------- REFERENCE VARCHAR2(30 CHAR) ITEMNO NUMBER(38) DESCRIPTION VARCHAR2(1024) PARTNO VARCHAR2(56) QUANTITY NUMBER(12,2) UNITPRICE NUMBER(8,4)
This example creates a view called PURCHASEORDER_DETAIL_VIEW. There will be one row in the view for each LineItem element the occurs in the XML documents stored in table PURCHASEORDER.
The CREATE VIEW statement defines the set of columns that will make up the view. The SELECT statement uses extract() to access the set of LineItem elements in each PurchaseOrder document. It then uses xmlSequence() and TABLE() to create a virtual table that contains one XML document for each LineItem in the PURCHASEORDER table.
The XPath expressions passed to the extractValue() function are used to map between the nodes in the LineItem documents and the columns defined by the view. The Reference element included in the view to create a Foreign Key that can used to joins rows in PURCHASEORDER_DETAIL_VIEW to the corresponding row in PURCHASEORDER_MASTER_VIEW. The correlated join in the CREATE VIEW statement ensures that the 1:many relationship between the Reference element and the associated LineItem elements is maintained when the view is accessed.
As can be seen from the output of the DESCRIBE statement, both views appear to be a standard relational views. Since the XMLType table referenced in the CREATE VIEW statements is based on an XML schema, Oracle XML DB can determine the datatypes of the columns in the views from the information contained in the XML schema.
The following examples show some of the benefits provided by creating relational views over XMLType tables and columns.
Example 3-31 SQL queries on XML Content Using Views
This example uses a simple query against the master view. The query uses a conventional SQL SELECT statement to select rows where the USERID column starts with S.
column REFERENCE format A30 column DESCRIPTION format A40 -- 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 next query is based on a join between the master view and detail view. Again, a conventional SQL 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 SMITH.
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.
Since 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 syntax or the generated result set.
By exposing XML content as relational data Oracle XML DB allows advanced features of Oracle Database, such as business intelligence and analytic capabilities, to 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 allows these features to be applied to XML content.
Example 3-32 Querying XML Using Views of XML Content
This example demonstrates using relational views over XML content to perform business intelligence queries on XML documents. The query performs an analysis of 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 being ordered on each PurchaseOrder. Looking at the results for the part number 715515009126, the query shows that there are seven PurchaseOrder values where one copy of the item is ordered and two PurchaseOrder values where four copies of the item are ordered.
|
See Also: Chapter 4, "Using XMLType" for a description ofXMLType datatype and functions and Appendix C, "XPath and Namespace Primer" for an introduction to the W3C XPath Recommendation |
Oracle XML DB allows update operations to take place on XML content. Update operations can either replace the entire contents or parts of a document. The ability to perform partial updates on XML documents is very powerful, particularly when trying to 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.
The updateXML() function enables partial update of an XML document stored as an XMLType. It allows multiple changes to be made to the document in a single operation. Each change consists of an XPath expression which identifies the node to be updated, and the new value for the node.
Example 3-33 Updating XML Content Using updateXML()
The following example shows an updateXML() function used to update the value of 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-34 Replacing an Entire Element Using updateXML()
This example uses updateXML() to replace an entire element within the XML document. Here 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-35 Updating a Node Occurring Multiple Times Within a Collection Using updateXML(): Incorrect Usage
This example show a common error that occurs when using updateXML() to update a node occurring multiple times in a collection. The UPDATE statement sets the value of the text node belonging to a Description element to "The Wizard of Oz", where the current value of the text node is "Sisters". The statement includes an existsNode() term in the WHERE clause that identifies the set of nodes to be updated.
SELECT extractValue(value(l),'/Description')
FROM purchaseorder p,
table (xmlsequence(extract(p.object_value,
'/PurchaseOrder/LineItems/LineItem/Description'))) l
WHERE existsNode(object_value,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
EXTRACTVALUE(VALUE(L),'/DESCRIPTION')
--------------------------------------------------------------------------------
The Lady Vanishes
The Unbearable Lightness Of Being
Sisters
3 rows selected.
--
UPDATE PURCHASEORDER
SET object_value = updateXML
(
object_value,
'/PurchaseOrder/LineItems/LineItem/Description/text()',
'The Wizard of Oz')
WHERE existsNode(object_value,
'/PurchaseOrder/LineItems/LineItem[Description="Sisters"]') = 1
AND existsNode(object_value,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
1 row updated.
--
SELECT extractValue(value(l),'/Description')
FROM purchaseorder p,
table (xmlsequence(extract(p.object_value,
'/PurchaseOrder/LineItems/LineItem/Description'))) l
WHERE existsNode(object_value,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
EXTRACTVALUE(VALUE(L),'/DESCRIPTION')
--------------------------------------------------------------------------------
The Wizard of Oz
The Wizard of Oz
The Wizard of Oz
3 rows selected.
As shown in the preceding example, instead of updating the required node, updateXML() updates the values of any text node that belongs to the Description element. This is actually the expected behavior. The WHERE clause can only be used to identify which documents must be updated, not which nodes within the document must be updated. Once 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 identified all three Description nodes, and so all three of the associated text nodes were updated. See Example 3-36 for the correct way to update the nodes.
Example 3-36 Updating a Node Occurring Multiple Times Within a Collection Using updateXML(): Correct Usage
To correctly use updateXML() to update a node occurring 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. The following statement shows the correct way of updating one node within a collection:
SELECT extractValue(value(l),'/Description')
FROM purchaseorder p,
table (xmlsequence(extract(p.object_value,
'/PurchaseOrder/LineItems/LineItem/Description'))) l WHERE existsNode(object_value,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
EXTRACTVALUE(VALUE(L),'/DESCRIPTION')
---------------------------------------------------------------------------------
A Night to Remember
The Unbearable Lightness Of Being
Sisters
3 rows selected.
--
UPDATE PURCHASEORDER
SET object_value = updateXML
(
object_value,
'/PurchaseOrder/LineItems/LineItem/Description[text()="Sisters"]/text()',
'The Wizard of Oz'
)
WHERE existsNode(object_value,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
1 row updated.
updateXML() allows multiple changes to be made to the document in one statement.
SELECT extractValue(value(l),'/Description')
FROM purchaseorder p,
table (xmlsequence(extract(p.object_value,
'/PurchaseOrder/LineItems/LineItem/Description'))) l
WHERE existsNode(object_value,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
EXTRACTVALUE(VALUE(L),'/DESCRIPTION')
---------------------------------------------------------------------------------
A Night to Remember
The Unbearable Lightness Of Being
The Wizard of Oz
3 rows selected.
Example 3-37 Changing Text Node Values Using updateXML()
This example shows how to change the values of text nodes belonging to the User and SpecialInstructions elements in one statement.
column "Cost Center" format A12
column "Instructions" format A40
--
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.
Here is the UPDATE statement that changes the User and SpecialInstruct 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.
Use the following statement to check that the nodes have changed:
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 updateXML() updates XML documents is primarily determined by whether or not the XML document is XML schema-based or non-XML schema-based, and how the XML document is stored:
Storing XML documents in CLOBs. When updateXML() updates a non-XML schema-based or XML schema-based XML document stored as a CLOB, Oracle XML DB performs the update by creating a Document Object Model (DOM) from the XML document and then uses DOM API methods, updates the specified nodes. When the updates have been applied, the updated DOM is returned back to the underlying CLOB.
Storing XML documents object-relationally. When updateXML() updates a schema-based XML document stored object-relationally, Oracle XML DB can use XPath rewrite to perform an in-place update of the underlying option. This is a partial-update. Partial-updates translate the XPath expression passed to the updateXML() function to an equivalent SQL statement. The update is then performed by executing the SQL statement that directly updates the attributes of underlying objects. This partial-update can result in an updateXML() operation that executes many times faster than a DOM-based update. This can make a significant difference when executing a SQL statement that applies updateXML() to a large number of documents.
These updates techniques are explained further in the following section.
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 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 the namespace parameter is not provided, Oracle XML DB makes the following assumptions about the XPath expression:
If the content of the XMLType is not based on a registered XML schema 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 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 the XML schema does not declare a targetnamespace, this defaults to the noNamespace namespace.
When the namespace parameter is provided the parameter must provide an explicit declaration for the default namespace in addition to the prefixed namespaces, unless the default namespace is the noNamespace namespace.
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, Oracle Corporation strongly recommends that you always pass the set of namespace declarations, including the declaration for the default namespace, when any namespaces other than the noNamespace namespace are present in either the XPath expression or the target XML document.
Oracle XML DB processes extract(), extractValue(), existsNode(), and updateXML() functions and their equivalent XMLType methods using DOM-based or SQL-based techniques:
DOM-Based XMLType Processing (Functional Evaluation). Oracle XML DB performs the required processing by constructing a DOM from the contents of the XMLType. 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 disc 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 whether the XMLType is stored using structured or unstructured storage techniques The disadvantage of functional evaluation is that it much more expensive than XPath rewrite, and will not scale across large numbers of XML documents.
SQL-Based XMLType Processing (XPath rewrite). 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 underly a schema-based XMLType. This process is referred to as XPath rewrite, but it can also occur with updateXML() operations.
The advantage of XPath rewrite is that it allows Oracle XML DB to evaluate XPath-based SQL functions and methods at near relational speeds. This allows these operations to 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 only be used when the XMLType is stored using XML schema-based object-relational storage techniques.
XPath rewrite improves the performance of SQL statements containing XPath-based functions, by converting the functions into conventional relational SQL statements. By translating XPath-based functions into conventional SQL statements, Oracle XML DB insulates the database optimizer from having to understand the XPath notation and the XML data model. The database optimizer processes the re-written 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.
When Can XPath Rewrite Occur?
For XPath rewrite to take place the following conditions must be satisfied:
The XMLType column or table containing the XML documents must be based on a registered XML schema.
The XMLType column or table must be stored using structured (object-relational) storage techniques.
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 conditions under which XPath rewrite takes place, is key to developing Oracle XML DB applications that deliver satisfactory levels of scalability and performance.
However, XPath rewrite on its own cannot guarantee scalable and performant applications. Like any other SQL statement, the performance of SQL statements generated by XPath rewrite is ultimately determined by the way data is stored on disk and available indexes. 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 tools that DBAs typically use with SQL-based applications can be used with XML-based applications using Oracle XML DB functions.
Example 3-38 shows how to use an EXPLAIN PLAN to look at the execution plan for selecting the set of PurchaseOrders created by user SCOTT.
Example 3-38 Using an EXPLAIN Plan to Analyze the Selection of PurchaseOrders
EXPLAIN PLAN FOR
SELECT extractValue(object_value,'/PurchaseOrder/Reference') "Reference"
FROM PURCHASEORDER
WHERE existsNode(object_value,'/PurchaseOrder[User="SBELL"]') = 1;
Explained.
set echo off
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 841749721
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22207 | 4 (0)| 00:00:01|
|* 1 | TABLE ACCESS FULL| PURCHASEORDER | 1 | 22207 | 4 (0)| 00:00:01|
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("PURCHASEORDER"."SYS_NC00022$"='SBELL')
Note
-----
- dynamic sampling used for this statement
17 rows selected.
Oracle XML DB supports the creation of three kinds of index on XML content:
Text-based indexes. These can be created on any XMLType table or column.
Function-based indexes. These can be created on any XMLType table or column.
Conventional B-Tree indexes. When the XMLType table or column is based on structured storage techniques, conventional B-Tree indexes can be created on underlying SQL types.
Indexes are typically created by using the extractValue() function, although it is also possible to create indexes based on other XMLType functions such as existsNode(). During the index creation process Oracle XML DB uses XPath rewrite to determine whether it is possible to map between the nodes referenced in the XPath expression used in the CREATE INDEX statement and the attributes of the underlying SQL types. If the nodes in the XPath expression can be mapped to attributes of the SQL types, then the index is created as a conventional B-Tree index on the underlying SQL objects. If the XPath expression cannot be restated using object-relational SQL then a function-based index is created.
Example 3-39 Creating an Index on a Text Node
This example shows creating an index PURCHASEORDER_USER_INDEX on the value of the text node belonging to the User element.
CREATE INDEX PURCHASEORDER_USER_INDEX
ON PURCHASEORDER
(extractValue(object_value,'/PurchaseOrder/User'));
At first glance the index appears to be a function-based index. However, where the XMLType table or column being indexed is based on object-relational storage , XPath rewrite determines whether the index can be re-stated as an index on the underlying SQL types. In this example, the CREATE INDEX statement results in the index being created on the USERID attribute of the PURCHASEORDER_T object.
The following output shows the EXPLAIN PLAN output generated when the query is executed after the index has been created.
The EXPLAIN PLAN clearly shows that the query plan will make use of the newly created index. The new execution plan is much more scalable.
explain plan for
SELECT extractValue(object_value,'/PurchaseOrder/Reference') "Reference"
FROM PURCHASEORDER
WHERE existsNode(object_value,'/PurchaseOrder[User="SBELL"]') = 1;
Explained.
--
set echo off
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 713050960
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 22207 | 3 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| PURCHASEORDER | 1 | 22207 | 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')
Note
-----
- dynamic sampling used for this statement
18 rows selected.
One key benefit of the relational database is that you do not need to change your application logic when the indexes change. This is also true for XML applications that leverage Oracle XML DB capabilities. Once the index has been created the optimizer automatically uses it when appropriate.
The majority of XML documents contain collections of repeating elements. For Oracle XML DB to be able to efficiently process the collection members it is important that the storage model for managing the collection provides an efficient way of accessing the individual members of the collection. Selecting the correct storage structure makes it possible to index elements within the collection and perform direct operations on individual elements within the collection.
Oracle XML DB offers four ways to manage members of the collection:
When stored as a CLOB value, you cannot directly access members of the collection.
When a VARRAY is stored as a LOB, you cannot directly access members of the collection.
Storing the members as XML Text managed by a CLOB means that any operation on the collection would require parsing the contents of the CLOB and then using functional evaluation to perform the required operation.
Converting the collection into a set of SQL objects that are serialized into a LOB removes the need to parse the documents. However any operations on the members of the collection still require that the collection be loaded from disk into memory before the necessary processing can take place.
VARRAY stored as a nested table, allows direct access to members of the collection.
VARRAY stored as XMLType, allows direct access to members of the collection
In the latter two cases, each member of the VARRAY becomes a row in a table. Since each element is stored as a row in a table it can be access directly though SQL.
The following example shows the execution plan for the query to find the Reference from any document that contains an order for the part with an Id of 717951002372 .
Example 3-40 Generating the EXPLAIN Plan When Selecting a Collection of LineItem Elements from a Nested Table
In this example the collection of LineItem elements has been stored as rows in the Index organized, nested table LINEITEM_TABLE.
explain plan for
SELECT extractValue(object_value,'/PurchaseOrder/Reference') "Reference"
FROM PURCHASEORDER
WHERE existsNode(object_value,
'/PurchaseOrder/LineItems/LineItem/Part[@Id="717951002372"]') = 1;
Explained.
--
set echo off
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 3281623413
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 550K| 822 (1)| 00:00:10 |
|* 1 | HASH JOIN RIGHT SEMI | | 21 | 550K| 822 (1)| 00:00:10 |
|* 2 | INDEX FAST FULL SCAN| LINEITEM_TABLE_IOT | 22 | 99K| 817 (0)| 00:00:10 |
| 3 | TABLE ACCESS FULL | PURCHASEORDER | 132 | 2863K| 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$")
2 - filter("SYS_NC00011$"='717951002372')
Note
-----
- dynamic sampling used for this statement
20 rows selected.
The execution plan shows that the query will be resolved by performing a full scan of the index that contains the contents of the nested table. Each time an entry is found that matches the XPath expression passed to the existsNode() function the parent row is located using the value of the NESTED_TABLE_ID column. Since the nested table is an Indexed Organized Table (IOT) this plan effectively resolves the query by a full scan of LINEITEM_TABLE. This plan may be acceptable when there are only a few hundred documents in the PURCHASEORDER table, but would be unacceptable if there are 1000's or 1,000,000's of documents in the table.
To improve the performance of this query create an index that allows direct access to the NESTED_TABLE_ID column given the value of the Id attribute. Unfortunately, Oracle XML DB does not currently allow indexes on collections to be created using XPath expressions. To create the index you must understand the structure of the SQL object used to manage the LineItem elements. Given this information you can create the required index using conventional object-relational SQL.
Here the LineItem element is stored as an instance of the LINEITEM_T object. The Part element is stored as an instance of the SQL Type PART_T. The Id attribute is mapped to the PART_NUMBER attribute. Given this information, you can create a composite index on the PART_NUMBER attribute and the NESTED_TABLE_ID that will allow direct access to the PURCHASEORDER documents that contain LineItem elements that reference the required part.
Example 3-41 Creating an Index to Improve Query Performance by Allowing Direct Access to the Nested Table
The following example shows how to use object-relational SQL to create the required index:
explain plan for
SELECT extractValue(object_value,'/PurchaseOrder/Reference') "Reference"
FROM PURCHASEORDER
WHERE existsNode(object_value,
'/PurchaseOrder/LineItems/LineItem/Part[@Id="717951002372"]') = 1;
Explained.
--
set echo off
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1699938086
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 21 | 13587 | 11 (10)| 00:00:01 |
|* 1 | HASH JOIN RIGHT SEMI| | 21 | 13587 | 11 (10)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | LINEITEM_TABLE_IOT | 22 | 2640 | 6 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | LINEITEM_PART_INDEX | 17 | | 2 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | PURCHASEORDER | 132 | 69564 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NESTED_TABLE_ID"="PURCHASEORDER"."SYS_NC0003400035$")
2 - access("SYS_NC00011$"='717951002372')
3 - access("SYS_NC00011$"='717951002372')
18 rows selected.
The plan clearly shows that query plan will make use of the newly created index. The query is now resolved by using LINEITEM_PART_INDEX to determine which documents in the PURCHASEORDER table satisfy the condition specified in the XPath expression specified in the existsNode() function. This query is clearly much more scalable.
In both cases the syntax used to define the query has not changed. XPath rewrite has allowed the optimizer to analyze the query and determine that the new indexes 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 DBMS_XMLSCHEMA.REGISTER_SCHEMA() will contain a filter that looks 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 is checking that the user performing the SQL query has read-contents privilege on each of the documents accessed.
Oracle XML DB repository uses an ACL-based security mechanism that allows access to XML content to be controlled on a document by document basis, rather than a table by table basis. When XML content is accessed using a SQL statement, the SYS_CHECKACL() predicate is added to the WHERE clause to ensure that the security defined is enforced at the SQL level.
Enforcing ACL-based security does add overhead to the SQL query. If ACL-based security is not required the procedure DISABLE_HIERARCHY in the DBMS_XDBZ package must be used to turn ACL checking off. After calling this procedure the SYS_CHECKACL() filter should no longer appear in the output generated by EXPLAIN PLAN.
Example 3-42 Generating an EXPLAIN Plan When XPath Rewrite Does Not Occur
This example shows the kind of EXPLAIN PLAN output generated when Oracle XML DB cannot perform XPath rewrite. The key is in line 3. Since the existsNode() function appears in the EXPLAIN output the query was not re-written.
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("NESTED_TABLE_ID"=:B1)
2 - access("NESTED_TABLE_ID"=:B1)
3 - filter(EXISTSNODE(SYS_MAKEXML('C0A5497E8DCF110BE034080020E5
CF39',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 results set based on any other conditions specified in the WHERE clause of the SQL statement. It then filters all the rows in potential results set to determine which rows belong in the actual results 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 actual results set, This can result in poor performance when there are a large number of documents in the potential results set. However when other predicates in the WHERE clause caused a small number of documents in the potential results set, this may be not be a problem.
XMLType and XPath abstractions make it possible for you to develop applications independently 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 SQL/XML standard. The SQL/XML standard defines a set of functions that allow XML to be generated directly from a SQL SELECT statement. These functions make it possible for a SQL statement to generate an XML document, or set of XML documents, rather than a traditional tabular result set. The set of functions defined by the SQL/XML standard are flexible, allowing all most any shape of XML to 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-43 Using SQL/XML Functions to Generate XML
The following SELECT statement generates an XML document containing information from the tables DEPARTMENTS, LOCATIONS, COUNTRIES, EMPLOYEES, and JOBS:
set long 100000
set pages 50
--
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;
This 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>21-SEP-89</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>13-JAN-93</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 or 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 aggregation of Employee Elements. The content of the EmployeeList element is created by a sub-select that returns the set of rows in the EMPLOYEES table that in turn corresponds to the current department. Each Employee element will contain information about the employee. The contents of the elements and attributes for each Employee is taken from the EMPLOYEES and JOBS tables.
By default, the output generated by the SQL/XML functions is not pretty-printed. This allows the SQL/XML functions to avoid creating a full DOM when generating the required output. By avoiding pretty-printing, Oracle XML DB can avoid overheads associated with DOM and reduce the size of the generated document.
The lack of pretty-printing should not matter to most applications. However it can make it difficult to verify the generated output. When pretty-printing is required, the extract() function can force the generation of pretty-printed output. Invoking the extract() method on the generated document forces a DOM to be constructed. Printing the results of the extract() forces the generation of pretty-printed output. Since invoking extract() forces a conventional DOM to be constructed, this technique should not be used when working with queries that create large documents.
Example 3-44 Forcing Pretty-Printing by Invoking extract() on the Result
Example 3-44 shows how to force pretty-printing by invoking the extract() method on the result generated by the xmlElement() SQL/XML function.
set long 100000
set pages 50
--
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 DEPARTMENT_NAME = 'Executive'
and d.LOCATION_ID = l.LOCATION_ID
and l.COUNTRY_ID = c.COUNTRY_ID;
XML
--------------------------------------------------------------------------------
<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>21-SEP-89</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>13-JAN-93</StartDate>
<JobTitle>Administration Vice President</JobTitle>
<Salary>17000</Salary>
<Manager>Steven King</Manager>
<Commission/>
</Employee>
</EmployeeList>
</Department>
1 row selected.
All SQL/XML functions return XMLTypes. This means that you can use the SQL/XML operators to create XMLType views over conventional relational tables. Example 3-45 illustrates this. XMLType views are object views. As such each row in the view has to be identified by an object id. The object id must be specified in the CREATE VIEW statement.
Example 3-45 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 allows relational data to 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 using SQL/XML functions. See Example 3-46.
Example 3-46 Querying XMLType Views
Example 3-46 shows a simple query against an XMLType view. The XPath expression passed to the existsNode() function restricts the resultset to the node that contains the information related to the Executive department.
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>21-SEP-89</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>13-JAN-93</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 was able to correctly XPath rewrite the existsNode() function on the XMLType row in the XMLType view 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.
--
set echo off
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 1218413855
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 80 | 12 (17)| 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 | FILTER | | | | | |
|* 9 | HASH JOIN | | 1 | 80 | 5 (20)| 00:00:01 |
| 10 | NESTED LOOPS | | 23 | 1403 | 2 (0)| 00:00:01 |
| 11 | TABLE ACCESS FULL | LOCATIONS | 23 | 1127 | 2 (0)| 00:00:01 |
|* 12 | INDEX UNIQUE SCAN | COUNTRY_C_ID_PK | 1 | 12 | | 00:00:01 |
| 13 | TABLE ACCESS FULL | DEPARTMENTS | 27 | 513 | 2 (0)| 00:00:01 |
| 14 | SORT AGGREGATE | | 1 | 114 | | |
|* 15 | HASH JOIN | | 10 | 1140 | 7 (15)| 00:00:01 |
|* 16 | HASH JOIN | | 10 | 950 | 5 (20)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 10 | 680 | 2 (0)| 00:00:01 |
|* 18 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 1 (0)| 00:00:01 |
| 19 | TABLE ACCESS FULL | JOBS | 19 | 513 | 2 (0)| 00:00:01 |
| 20 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 2 (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)
8 - filter(EXISTSNODE("XMLTYPE"."EXTRACT"(XMLELEMENT("Department",XMLATTRIBUTES(TO_CHAR("D".
"DEPARTMENT_ID") AS "DepartmentId"),XMLELEMENT("Name","D"."DEPARTMENT_NAME"),XMLELEMENT("Locati
on",CASE WHEN "STREET_ADDRESS" IS NOT NULL THEN XMLELEMENT("Address","STREET_ADDRESS") ELSE
NULL END ,XMLELEMENT("City","CITY"),CASE WHEN "STATE_PROVINCE" IS NOT NULL THEN
XMLELEMENT("State","STATE_PROVINCE") ELSE NULL END ,CASE WHEN "POSTAL_CODE" IS NOT NULL THEN
XMLELEMENT("Zip","POSTAL_CODE") ELSE NULL END ,CASE WHEN "COUNTRY_NAME" IS NOT NULL THEN
XMLELEMENT("Country","COUNTRY_NAME") ELSE NULL END ),XMLELEMENT("EmployeeList", (SELECT
"XMLAGG"(XMLELEMENT("Employee",XMLATTRIBUTES(TO_CHAR("E"."EMPLOYEE_ID") AS
"employeeNumber"),CASE WHEN "E"."FIRST_NAME" IS NOT NULL THEN
XMLELEMENT("FirstName","E"."FIRST_NAME") ELSE NULL END
,XMLELEMENT("LastName","E"."LAST_NAME"),XMLELEMENT("EmailAddress","E"."EMAIL"),CASE WHEN
"E"."PHONE_NUMBER" IS NOT NULL THEN XMLELEMENT("PHONE_NUMBER","E"."PHONE_NUMBER") ELSE NULL
END ,XMLELEMENT("StartDate","E"."HIRE_DATE"),XMLELEMENT("JobTitle","J"."JOB_TITLE"),CASE WHEN
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
"E"."SALARY" IS NOT NULL THEN XMLELEMENT("Salary",TO_CHAR("E"."SALARY")) ELSE NULL END ,CASE
WHEN "M"."FIRST_NAME"||' '||"M"."LAST_NAME" IS NOT NULL THEN
XMLELEMENT("Manager","M"."FIRST_NAME"||' '||"M"."LAST_NAME") ELSE NULL END
,XMLELEMENT("Commission",TO_CHAR("E"."COMMISSION_PCT")))) FROM "HR"."JOBS"
"J","HR"."EMPLOYEES" "M","HR"."EMPLOYEES" "E" WHERE "E"."DEPARTMENT_ID"=:B1 AND
"M"."EMPLOYEE_ID"="E"."MANAGER_ID" AND "J"."JOB_ID"="E"."JOB_ID"))),'/*'),'/Department[Name="Ex
ecutive"]')=1)
9 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
12 - access("L"."COUNTRY_ID"="C"."COUNTRY_ID")
15 - access("M"."EMPLOYEE_ID"="E"."MANAGER_ID")
16 - access("J"."JOB_ID"="E"."JOB_ID")
18 - access("E"."DEPARTMENT_ID"=:B1)
Note
-----
- warning: inconsistencies found in estimated optimizer costs
63 rows selected.
In the current release of Oracle XML DB, XPath rewrites on XML functions that operate on XMLType views are only supported when nodes referenced in the XPath expression are not descendants of an element created using xmlAgg() function.
Another way to generate XML from relational data is with the DBUriType datatype. DBUriType exposes one or more rows in a given table as a single XML document. The name of the root element is derived from the name of the table. The root element contains a set of ROW elements. There will be one ROW element for each row in the table. The sub-elements of each ROW element are derived from the columns in the table or view. Each sub-element will contain a text node that contains the value of the column for the given row.
Example 3-47 shows how to use DBUriType() to access the contents of the DEPTARTMENTS table in the HR schema. The example uses the getXML() method to return the resulting document as an XMLType instance.
Example 3-47 Accessing DEPARTMENTS Table XML Content Using DBUriType() and getXML()
set pagesize 100
set linesize 132
set long 10000
--
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>
...
DBUriType() allows XPath notations to be used to control how much of the data in the table is returned when the table is accessed using the DBUriType(). Predicates in the XPath expression allow control over which of the rows in the table are included in the generated document.
Example 3-48 Using a Predicate in the XPath Expression to Restrict Which Rows Are Included
This example demonstrates how to use a predicate in the XPath expression to restrict which rows are included in the generated document. Here the XPath expression restricts the document to those DEPARTMENT_ID columns containing the values 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 DBUriType() provide a simple way to expose some or all rows in a relational table as an XML document(s). The URL passed to DBUriType() can be extended to return a single column from the view or table, but in this 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 columns contain the 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.
DBUriType() does not provide the flexibility of the SQL/XML operators. Unlike the SQL/XML operators, DBUriType() has no way to control the shape of the generated document. The data can only come from a single table or view. The generated document will consist of a set of ROW elements or single column from a single row. Each ROW element will contain a sub-element for each column in the target table. The names of the sub-elements will be derived from names of the columns.
To control the names of the elements, include columns from more than one table, or control which columns from a table appear in the generated document, it is necessary to create a relational view that exposes the desired set of columns as a single row and then use DBUriType() to generate an XML document from the contents of the 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. Oracle XML DB includes an XSLT processor that allows XSL transformations to be performed inside the database.
XSL transformation is typically expensive in terms of the amount of memory and processing required. Both the source document and style sheet have to be parsed and loaded into in-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.
By performing XSL transformation inside the database, alongside the data, Oracle XML DB can provide XML-specific memory optimizations that significantly reduces 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.
Oracle XML provides three options for invoking the XSL processor.
XMLTransform() SQL function
transform() XMLType datatype method
DBMS_XSLPROCESSOR PL/SQL package
All three options expect the source document and XSL style sheet to be provided as an XMLType. The result of the transformation is also expected to be a valid XML document. This means that any HTML generated by the transformation must be XHTML, that is valid XML and valid HTML
Example 3-49 XSLT Style Sheet Example: PurchaseOrder.xsl
The following example, PurchaseOrder.xsl, is a fragment of an XSLT style sheet:
<?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>
<table border="0" width="98%" BGCOLOR="#000099">
<tbody>
<tr>
<td WIDTH="49%">
<B>
<FONT COLOR="#FFFF00">Actions</FONT>
</B>
</td>
<td WIDTH="51%">
<xsl:for-each select="Actions">
<xsl:for-each select="Action">
<table border="1" WIDTH="143">
<xsl:if test="position()=1">
<thead>
<tr>
<td HEIGHT="21">
<FONT COLOR="#FFFF00">User</FONT>
</td>
<td HEIGHT="21">
<FONT COLOR="#FFFF00">Date</FONT>
</td>
</tr>
</thead>
</xsl:if>
<tbody>
<tr>
<td>
<xsl:for-each select="User">
<xsl:apply-templates/>
</xsl:for-each>
</td>
<td>
<xsl:for-each select="Date">
<xsl:apply-templates/>
</xsl:for-each>
</td>
</tr>
</tbody>
</table>
</xsl:for-each>
</xsl:for-each>
</td>
</tr>
<tr>
<td WIDTH="49%">
<B>
<FONT COLOR="#FFFF00">Requestor</FONT>
</B>
</td>
<td WIDTH="51%">
<xsl:for-each select="Requestor">
<xsl:apply-templates/>
</xsl:for-each>
</td>
</tr>
<tr>
<td WIDTH="49%">
<B>
<FONT COLOR="#FFFF00">User</FONT>
</B>
</td>
<td WIDTH="51%">
<xsl:for-each select="User">
<xsl:apply-templates/>
</xsl:for-each>
</td>
</tr>
<tr>
<td WIDTH="49%">
<B>
<FONT COLOR="#FFFF00">Cost Center</FONT>
</B>
</td>
<td WIDTH="51%">
<xsl:for-each select="CostCenter">
<xsl:apply-templates/>
</xsl:for-each>
</td>
</tr>
</tbody>
</table>
</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:if test="position()=1">
<table border="0" BGCOLOR="#999900">
<tbody>
<tr>
<td WIDTH="126" HEIGHT="24">
<B>Name</B>
</td>
<xsl:for-each select="../ShippingInstructions">
<td WIDTH="218" HEIGHT="24">
<xsl:for-each select="name">
<xsl:apply-templates/>
</xsl:for-each>
</td>
</xsl:for-each>
</tr>
<tr>
<td WIDTH="126" HEIGHT="34">
<B>Address</B>
</td>
<xsl:for-each select="../ShippingInstructions">
<td WIDTH="218" HEIGHT="34">
<xsl:for-each select="address">
<span style="white-space:pre">
<xsl:apply-templates/>
</span>
</xsl:for-each>
</td>
</xsl:for-each>
</tr>
<tr>
<td WIDTH="126" HEIGHT="32">
<B>Telephone</B>
</td>
<xsl:for-each select="../ShippingInstructions">
<td WIDTH="218" HEIGHT="32">
<xsl:for-each select="telephone">
<xsl:apply-templates/>
</xsl:for-each>
</td>
</xsl:for-each>
</tr>
</tbody>
</table>
</xsl:if>
</xsl:for-each>
</td>
</tr>
</tbody>
</table>
<br/>
<B>
<FONT COLOR="#FF0000" SIZE="+1">Items:</FONT>
</B>
<br/>
<br/>
<table border="0">
<xsl:for-each select="LineItems">
<xsl:for-each select="LineItem">
<xsl:if test="position()=1">
<thead>
<tr bgcolor="#C0C0C0">
<td>
<FONT COLOR="#FF0000">
<B>ItemNumber</B>
</FONT>
</td>
<td>
<FONT COLOR="#FF0000">
<B>Description</B>
</FONT>
</td>
<td>
<FONT COLOR="#FF0000">
<B>PartId</B>
</FONT>
</td>
<td>
<FONT COLOR="#FF0000">
<B>Quantity</B>
</FONT>
</td>
<td>
<FONT COLOR="#FF0000">
<B>UnitPrice</B>
</FONT>
</td>
<td>
<FONT COLOR="#FF0000">
<B>Total Price</B>
</FONT>
</td>
</tr>
</thead>
</xsl:if>
<tbody>
<tr bgcolor="#DADADA">
<td>
<FONT COLOR="#000
1 row selected.
The style sheet is a standard XSL style sheet. These is nothing Oracle XML DB- specific about the 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 allows Oracle XML DB to optimize features such as memory usage, I/O operations, and network traffic. These optimizations are particularly effective when the transform 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 as only the parts of the document that are processed are loaded into memory.
Example 3-50 Using transform() to Apply an XSL to an XML Document Stored in an XMLType Table
This example shows how to use XMLType transform() method to apply an XSL style sheet to a document stored in an XMLType table. XDBUriType() reads the XSL style sheet from Oracle XML DB repository:
set long 10000
set pagesize 100
set linesize 132
--
SELECT XMLTRANSFORM(object
_value,xdbUriType('/home/SCOTT/poSource/xsl/purchaseOrder.xsl').getXML())
FROM PURCHASEORDER
WHERE existsNode(object_value,
'/PurchaseOrder[Reference="SBELL-2002100912333601PDT"]') = 1;
XMLTRANSFORM(OBJECT
_VALUE,XDBURITYPE('/HOME/SCOTT/POSOURCE/XSL/PURCHASEORDER.XSL').GETXML())
--------------------------------------------------------------------------------
<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">
<tbody>
<tr>
<td WIDTH="49%">
<B>
<FONT COLOR="#FFFF00">Actions</FONT>
</B>
</td>
<td WIDTH="51%">
<table border="1" WIDTH="143">
<thead>
<tr>
<td HEIGHT="21">
<FONT COLOR="#FFFF00">User</FONT>
</td>
<td HEIGHT="21">
<FONT COLOR="#FFFF00">Date</FONT>
</td>
</tr>
</thead>
<tbody>
<tr>
<td>SVOLLMAN</td>
<td/>
</tr>
</tbody>
</table>
</td>
</tr>
<tr>
<td WIDTH="49%">
<B>
<FONT COLOR="#FFFF00">Requestor</FONT>
</B>
</td>
<td WIDTH="51%">Sarah J. Bell</td>
</tr>
<tr>
<td WIDTH="49%">
<B>
<FONT COLOR="#FFFF00">User</FONT>
</B>
</td>
<td WIDTH="51%">SBELL</td>
</tr>
<tr>
<td WIDTH="49%">
<B>
<FONT COLOR="#FFFF00">Cost Center</FONT>
</B>
</td>
<td WIDTH="51%">B40</td>
</tr>
</tbody>
</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">
<tbody>
XMLTRANSFORM(OBJECT
_VALUE,XDBURITYPE('/HOME/SCOTT/POSOURCE/XSL/PURCHASEORDER.XSL').GETXML())
----------------------------------------------------------------------------------
<tr>
<td WIDTH="126" HEIGHT="24">
<B>Name</B>
</td>
<td WIDTH="218" HEIGHT="24">Sarah J. Bell</td>
</tr>
<tr>
<td WIDTH="126" HEIGHT="34">
<B>Address</B>
</td>
<td WIDTH="218" HEIGHT="34">
<span style="white-space:pre">400 Oracle Parkway
Redwood Shores
CA
94065
USA</span>
</td>
</tr>
<tr>
<td WIDTH="126" HEIGHT="32">
<B>Telephone</B>
</td>
<td WIDTH="218" HEIGHT="32">650 506 7400</td>
</tr>
</tbody>
</table>
</td>
</tr>
</tbody>
</table>
<br/>
<B>
<FONT COLOR="#FF0000" SIZE="+1">Items:</FONT>
</B>
<br/>
<br/>
<table border="0">
<thead>
<tr bgcolor="#C0C0C0">
<td>
<FONT COLOR="#FF0000">
<B>ItemNumber</B>
</FONT>
</td>
<td>
<FONT COLOR="#FF0000">
<B>Description</B>
</FONT>
</td>
<td>
<FONT COLOR="#FF0000">
<B>PartId</B>
</FONT>
</td>
<td>
<FONT COLOR="#FF0000">
<B>Quantity</B>
</FONT>
</td>
<td>
<FONT COLOR="#FF0000">
<B>UnitPrice</B>
</FONT>
</td>
<td>
<FONT COLOR="#FF0000">
<B>Total Price</B>
</FONT>
</td>
</tr>
</thead>
<tbody>
<tr bgcolor="#DADADA">
<td>
<FONT COLOR="#000000">1</FONT>
</td>
<td>
<FONT COLOR="#000000">A Night to Remember</FONT>
</td>
<td>
<FONT COLOR="#000000">715515009058</FONT>
</td>
<td>
<FONT COLOR="#000000">2</FONT>
</td>
<td>
<FONT COLOR="#000000">39.95</FONT>
</td>
<td>
<FONT FACE="Arial, Helvetica, sans-serif"
COLOR="#000000">79.900000000000006</FONT>
</td>
</tr>
</tbody>
<tbody>
<tr bgcolor="#DADADA">
<td>
XMLTRANSFORM(OBJECT
_VALUE,XDBURITYPE('/HOME/SCOTT/POSOURCE/XSL/PURCHASEORDER.XSL').GETXML())
-----------------------------------------------------------------------------
<FONT COLOR="#000000">2</FONT>
</td>
<td>
<FONT COLOR="#000000">The Unbearable Lightness Of Being</FONT>
</td>
<td>
<FONT COLOR="#000000">37429140222</FONT>
</td>
<td>
<FONT COLOR="#000000">2</FONT>
</td>
<td>
<FONT COLOR="#000000">29.95</FONT>
</td>
<td>
<FONT FACE="Arial, Helvetica, sans-serif"
COLOR="#000000">59.899999999999999</FONT>
</td>
</tr>
</tbody>
<tbody>
<tr bgcolor="#DADADA">
<td>
<FONT COLOR="#000000">3</FONT>
</td>
<td>
<FONT COLOR="#000000">The Wizard of Oz</FONT>
</td>
<td>
<FONT COLOR="#000000">715515011020</FONT>
</td>
<td>
<FONT COLOR="#000000">4</FONT>
</td>
<td>
<FONT COLOR="#000000">29.95</FONT>
</td>
<td>
<FONT FACE="Arial, Helvetica, sans-serif"
COLOR="#000000">119.799999999999997</FONT>
</td>
</tr>
</tbody>
</table>
</FONT>
</body>
</html>
1 row selected.
|
See Also: Chapter 8, "Transforming and Validating XMLType Data" |
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 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 the metadata and content managed by the Oracle XML DB repository is stored using a set of tables in the database schema owned by database user XDB. User XDB is a locked account installed with DBCA or by running the 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 the 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: Concurrency: 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 the 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.
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 allows multiple applications to 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.
There are two ways to work with content stored in Oracle XML DB repository:
Using industry standard protocols such as HTTP, WebDAV, or 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.
Oracle XML DB supports industry-standard internet protocols such as HTTP, 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-51 Uploading Content into Oracle XML DB 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): SCOTT 331 pass required for SCOTT Password: 230 SCOTT logged in ftp> cd /home/SCOTT 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 SCOTT oracle 0 JUN 24 15:41 AMCEWEN-20021009123336171PDT.xml -rw-r--r1 SCOTT oracle 0 JUN 24 15:41 AMCEWEN-20021009123336271PDT.xml -rw-r--r1 SCOTT oracle 0 JUN 24 15:41 EABEL-20021009123336251PDT.xml -rw-r--r1 SCOTT oracle 0 JUN 24 15:41 PTUCKER-20021009123336191PDT.xml -rw-r--r1 SCOTT oracle 0 JUN 24 15:41 PTUCKER-20021009123336291PDT.xml -rw-r--r1 SCOTT oracle 0 JUN 24 15:41 SBELL-20021009123336231PDT.xml -rw-r--r1 SCOTT oracle 0 JUN 24 15:41 SBELL-20021009123336331PDT.xml -rw-r--r1 SCOTT oracle 0 JUN 24 15:41 SKING-20021009123336321PDT.xml -rw-r--r1 SCOTT oracle 0 JUN 24 15:41 SMCCAIN-20021009123336151PDT.xml -rw-r--r1 SCOTT oracle 0 JUN 24 15:41 SMCCAIN-20021009123336341PDT.xml -rw-r--r1 SCOTT 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 these examples is that neither Windows Explorer nor the FTP tool are aware that they are working with Oracle XML DB. Since the tools and Oracle XML DB both support open Internet protocols they simply 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 0 bytes. This was designed as such for two reasons:
Firstly, it is not clear what the size of the document should be. Is it the size of the CLOB generated by printing the document, or the number of bytes required to store the objects used to persist the document inside the database?
Secondly, 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.
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 Oracle XML DB repository. Oracle XML DB includes PL/SQL package, DBMS_XDB, that provides methods that allow resources to be created, modified, and deleted in a programmatically.
Example 3-52 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/SCOTT/NurseryRhyme.txt',
bfilename('XMLDIR','DocExample01.txt'),
nls_charset_id('AL32UTF8'));
end;
/
PL/SQL procedure successfully completed.
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 XML DB repository. XDBUriType provides methods getBLOB(), getCLOB(), and getXML() to access the different kinds of content that can be associated with a resource.
Example 3-53 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/SCOTT/NurseryRhyme.txt').getClob()
FROM dual;
XDBURITYPE('/HOME/SCOTT/NURSERYRHYME.TXT').GETCLOB()
--------------------------------------------------------------------------------
Mary had a little lamb
It's fleece was white as snow
and every where that Mary went
that lamb was sure to go
1 row selected.
Example 3-54 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/SCOTT/NurseryRhyme.txt') = 1;
DBMS_XMLGEN.CONVERT(EXTRACT(RES,'/RESOURCE/CONTENTS/TEXT/TEXT()','XMLNS="HTTP://
--------------------------------------------------------------------------------
Mary had a little lamb
It's fleece was white as snow
and every where that Mary went
that lamb was sure to go
1 row selected.
extract() rather than extractValue() is used to access the text() node. This returns the content of the text() node as an XMLType, which makes it possible to access the content of the node using 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-55 Accessing Schema-Based XML Documents Using the 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 extractValue(value(l),'/Description')
FROM RESOURCE_VIEW r,
table (
xmlsequence
(
extract
(
res,
'/r:Resource/r:Contents/PurchaseOrder/LineItems/LineItem/Description',
'xmlns:r="http://xmlns.oracle.com/xdb/XDBResource.xsd"'
)
)
) l
WHERE equals_path(res,
'/home/SCOTT/PurchaseOrders/2002/Mar/SBELL-2002100912333601PDT.xml') = 1;
EXTRACTVALUE(VALUE(L),'/DESCRIPTION')
--------------------------------------------
A Night to Remember
The Unbearable Lightness Of Being
The Wizard of Oz
3 rows selected.
In this case a namespace prefix was used to identify which nodes in the XPath expression are members of the resource namespace. This was necessary as the PurchaseOrder XML schema does not define a namespace and it was not possible to apply a namespace prefix to nodes in the PurchaseOrder document.
The 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 allows the RESOURCE_VIEW to 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-56 Querying Repository Resource Data Using Ref() and the XMLRef Element
This example locates a row in the defaultTable based on a path in Oracle XML DB repository. SQL ref() function locates the target row in the default table based on value of the XMLRef element contained in the resource document.
SELECT extractValue(value(l),'/Description')
FROM RESOURCE_VIEW r, PURCHASEORDER p,
TABLE (
xmlsequence
(
extract
(
object_value,
'/PurchaseOrder/LineItems/LineItem/Description'
)
)
) l
WHERE equals_path(res,
'/home/SCOTT/PurchaseOrders/2002/Mar/SBELL-2002100912333601PDT.xml') = 1
AND ref(p) = extractValue(res,'/Resource/XMLRef');
EXTRACTVALUE(VALUE(L),'/DESCRIPTION')
--------------------------------------------------------------------------------
A Night to Remember
The Unbearable Lightness Of Being
The Wizard of Oz
3 rows selected.
Example 3-57 Selecting XML Document Fragments Based on Metadata, Path, and Content
This example shows how this technique makes it possible to select fragments from XML documents based on metadata, path, and content. The statement returns the value of the Reference element for documents foldered under the path /home/SCOTT/PurchaseOrders/2002/Mar and contain orders for part 715515009058.
SELECT extractValue(object_value,'/PurchaseOrder/Reference')
FROM RESOURCE_VIEW r, PURCHASEORDER p
WHERE under_path(res,'/home/SCOTT/PurchaseOrders/2002/Mar') = 1
AND ref(p) = extractValue(res,'/Resource/XMLRef')
AND existsNode(object_value,
'/PurchaseOrder/LineItems/LineItem/Part[@Id="715515009058"]') = 1;
EXTRACTVALUE(OBJECT_VALUE,'/PU
------------------------------
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 the RESOURCE_VIEW or PATH_VIEW on their own. The 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 allows XPath rewrite to be used to optimize the operation on the default table as well as the operation on the resource.
You can also update the content of documents stored in the 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 get verb to access the content of a document, and the HTTP put verb 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 editor like 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 what 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.
The updatexml() function can be used to update the content of any document stored in Oracle XML DB repository. The content of the document can be updated by updating the resource document, or in the case of schema-based XML documents, by updating the default table that contains the content of the document.
Example 3-58 Updating the Contents of a Text 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 updateXML() on the resource document. XPath expression is passed to updateXML() to identify the text node belonging to the element text contained in element /Resource/Contents as the target of the update operation.
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','DocExample02.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/SCOTT/NurseryRhyme.txt') = 1;
dbms_lob.freeTemporary(contents);
end;
/
PL/SQL procedure successfully completed.
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 XML DB repository.
Example 3-59 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 updateXML() changes the value of the text node associated with the User element.
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/SCOTT/PurchaseOrders/2002/Mar/SBELL-2002100912333601PDT.xml') = 1;
1 row updated.
You can update XML schema-based XML documents by performing the update operation directly on the default table 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 their own. The 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 allows a partial-update to be used on the default table and resource.
Example 3-60 Updating XML Schema-Based Documents in the Repository
Here 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 value of the row is obtained from the resource document identified by the equals_path() function. This effectively limits the update to the row corresponding to the resource identified by the specified path.
UPDATE PURCHASEORDER p
SET object_value = updateXML
(
object_value,
'/PurchaseOrder/User/text()','SBELL')
WHERE ref(p) =
(
SELECT extractValue(res,'/Resource/XMLRef')
FROM RESOURCE_VIEW
WHERE equals_path(res,
'/home/SCOTT/PurchaseOrders/2002/Mar/SBELL-2002100912333601PDT.xml') = 1
);
1 row updated.
You can control access to the resources in the XML DB repository by using Access Control Lists (ACLs). An ACL is a list of access control entries, 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' that 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 the DBMS_XDB.setACL() procedure. For more details on XML DB resource security, see Chapter 23, " Oracle XML DB Resource Security".
In the following example, the current user is SCOTT. The query gives the number of resources in the folder /public. Assume that there are only 2 resources in this folder: f1 and f2. Also assume that the ACL on f1 grants the read-properties privilege to SCOTT while the ACL on f2 does not grant SCOTT 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 since only f1 is visible to SCOTT.
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 updatexml() statements can be used within a single logical unit of work. Changes made through 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 metadata for each resource is preserved as an XML document. The structure of these 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 allows 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 XML DB repository. The view consists of two columns. The RES column contains the resource document that manages the metadata properties associated with the document. The ANY_PATH column contains a valid URL that the current user can pass to XDBUriType in order to access the content the document. In the cases of non-binary content the resource document will also contain the content of the document.
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 link, 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 it PATH_VIEW for each possible path to a document. The PATH_VIEW consists of three columns. The RES columns contains the resource document that this link points at. The PATH column contains the Path that the link allows to be used to access the resource. The LINK column contains the link-properties document for this PATH.
Example 3-61 Viewing RESOURCE_VIEW and PATH_VIEW Structures
The following example shows the description of the public views RESOURCE_VIEW and PATH_VIEW:
desc RESOURCE_VIEW
Name Null? Type
----------------------------------------------------------------------
RES SYS.XMLTYPE(XMLSchema
"http://xmlns.oracle.com/xd
b/XDBResource.xsd" Element "Resource")
ANY_PATH VARCHAR2(4000)
RESID RAW(16)
--
desc PATH_VIEW
Name Null? Type
-------------------------------------------------------------
PATH VARCHAR2(1024)
RES SYS.XMLTYPE(XMLSchema
"http://xmlns.oracle.com/xd
b/XDBResource.xsd" Element "Resource")
LINK SYS.XMLTYPE
RESID RAW(16)
Oracle XML DB provides two new functions, equals_path() and under_path(), that can be used to perform folder-restricted queries. Folder-restricted 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. equals_path() restricts the statement to a single document identified by the specified path. under_path() restricts the statement to those documents that exist beneath a certain point in the hierarchy.
Example 3-62 Accessing Resources Using equals_path() and RESOURCE_VIEW
The following query uses the equals_path() function and RESOURCE_VIEW to access the resource created in Example 3-61.
SELECT r.res.getClobVal()
FROM RESOURCE_VIEW r
WHERE equals_path(res,'/home/SCOTT/NurseryRhyme.txt') = 1;
R.RES.GETCLOBVAL()
--------------------------------------------------------------------------------
<Resource xmlns="http://xmlns.oracle.com/xdb/XDBResource.xsd" Hidden="false" Inv
alid="false" Container="false" CustomRslv="false" VersionHistory="false" StickyR
ef="true">
<CreationDate>2003-12-08T19:03:06.584000</CreationDate>
<ModificationDate>2003-12-08T19:03:07.456000</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.ora
cle.com/xdb/acl.xsd http://xmlns.oracle.com/xdb/acl.xs
d">
<ace>
<principal>dav:owner</principal>
<grant>true</grant>
<privilege>
<all/>
</privilege>
</ace>
</acl>
</ACL>
<Owner>SCOTT</Owner>
<Creator>SCOTT</Creator>
<LastModifier>SCOTT</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 this example 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 Creation Date, Creator, Owner, Last Modification Date, and Display Name. The content of the resource document can be queried and updated just like any other XML document, using functions such as extract(), extractValue(), existsNode(), and updateXML().
The following examples demonstrate simple folder-restricted queries against resource documents stored in the RESOURCE_VIEW and PATH_VIEW.
Example 3-63 Determining the Path to XSL Style Sheets Stored in the Repository
The first query finds a path to each of XSL style sheet stored in Oracle XML DB repository. It performs a search based on the DisplayName ending in .xsl. Unlike a conventional file system, Oracle XML DB can use the power of Oracle Database to resolve this query.
SELECT any_path FROM RESOURCE_VIEW WHERE extractValue(RES,'/Resource/DisplayName') like '%.xsl'; ANY_PATH -------------------------------------------------------------------------------- /home/SCOTT/poSource/xsl/empdept.xsl /home/SCOTT/poSource/xsl/purchaseOrder.xsl 2 rows selected.
Example 3-64 Counting Resources Under a Path
This example counts the number of resources (files and folders) under the path /home/SCOTT/PurchaseOrders. Using RESOURCE_VIEW rather than PATH_VIEW ensures that resources that any resources that are the target of multiple links are only counted once. The under_path() function restricts the resultset to documents that can be accessed using a path where the path starts from /home/SCOTT/PurchaseOrders.
SELECT count(*)
FROM RESOURCE_VIEW
WHERE under_path (RES,'/home/SCOTT/PurchaseOrders') = 1;
COUNT(*)
----------
145
1 row selected.
Example 3-65 Listing the Folder Contents in a Path
This query lists the contents of the folder identified by path /home/SCOTT/PurchaseOrders/2002/Apr. This is effectively a directory listing of the folder.
SELECT PATH FROM PATH_VIEW WHERE under_path(RES,'/home/SCOTT/PurchaseOrders/2002/Apr') = 1; PATH -------------------------------------------------------------------------------- /home/SCOTT/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336171PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336271PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/EABEL-20021009123336251PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/PTUCKER-20021009123336191PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/PTUCKER-20021009123336291PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/SBELL-20021009123336231PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/SBELL-20021009123336331PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/SKING-20021009123336321PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/SMCCAIN-20021009123336151PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/SMCCAIN-20021009123336341PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/VJONES-20021009123336301PDT.xml 11 rows selected.
Example 3-66 Listing the Links Contained in a Folder
This query lists the set of links contained in the folder identified by the path /home/SCOTT/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/SCOTT/PurchaseOrders/2002/Apr') = 1; PATH -------------------------------------------------------------------------------- /home/SCOTT/PurchaseOrders/2002/Apr/SBELL-20021009123336231PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/SBELL-20021009123336331PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/SKING-20021009123336321PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/SMCCAIN-20021009123336151PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/SMCCAIN-20021009123336341PDT.xml 5 rows selected.
Example 3-67 Finding the Path to Resources in the Repository Containing a PO XML Document
This query finds a path to each of the resources in the repository that contain a PurchaseOrder XML document. The documents are identified based on the metadata property SchemaElement that identifies the XML schema URL and global element for schema-based XML stored in Oracle XML DB repository.
SELECT ANY_PATH
FROM RESOURCE_VIEW
WHERE existsNode(RES,
'/Resource[SchemaElement="http://localhost:8080/home/SCOTT/poSource/xsd/purchaseOrder.xsd#PurchaseOrder"]') = 1;
This returns the following paths each of which contain a PurchaseOrder XML document:
ANY_PATH -------------------------------------------------------------------------------- /home/SCOTT/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336171PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/AMCEWEN-20021009123336271PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/EABEL-20021009123336251PDT.xml /home/SCOTT/PurchaseOrders/2002/Apr/PTUCKER-20021009123336191PDT.xml
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 and path-based access and folder-restricted queries would become very inefficient as the number of documents and depth of the folder hierarchy increases.
To address this issue, Oracle XML DB introduces a new index, the hierarchical index. The hierarchical index allows the database to resolve folder-restricted queries without relying on a CONNECT BY operation. Hence Oracle XML DB can execute path-based and folder-restricted queries efficiently. The hierarchical 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-68 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 index (XDBHI_IDX) will be 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/SCOTT/PurchaseOrders/2002/Apr') = 1;
Explained.
--
set echo off
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------
Plan hash value: 2568289845
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 300 | 62100 | 28 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 300 | 62100 | 28 (0)| 00:00:01 |
| 2 | NESTED LOOPS | | 300 | 57000 | 28 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 300 | 44400 | 28 (0)| 00:00:01 |
|* 4 | TABLE ACCESS BY INDEX ROWID | XDB$RESOURCE | 1 | 146 | 4 (0)| 00:00:01 |
| 5 | DOMAIN INDEX | XDBHI_IDX | | | | |
| 6 | COLLECTION ITERATOR PICKLER FETCH| | | | | |
|* 7 | INDEX UNIQUE SCAN | XDB_PK_H_LINK | 1 | 42 | | 00:00:01 |
|* 8 | INDEX UNIQUE SCAN | SYS_C002901 | 1 | 17 | | 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - filter("P"."SYS_NC00011$" LIKE 'S%')
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))
Note
-----
- warning: inconsistencies found in estimated optimizer costs
27 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 the content of non-XML files, 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 BLOBs or CLOBs. The contents of non-schema-based XML documents are stored as a CLOB in the repository.
There is one resource and one link-properties document for every file or folder in Oracle XML DB 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 Oracle XML DB repository.
When an XML file is loaded into Oracle XML DB repository the following sequence of events that 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 the 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 allows the content of the document to be accessed using Oracle XML DB 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 the 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 path-based access to access this type of content.
Oracle XML DB includes the DBUri servlet that 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- 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 simply extended with the address and port number of the Oracle XML DB HTTP server and the virtual root that directs HTTP requests to the DBUri servlet. The default configuration for this is /oradb.
This means that the URL: http://localhost:8080/oradb/HR/DEPTARTMENTS,
would return an XML document containing the contents of the DEPARTMENTS table in the HR database schema, assuming 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 allows XML generated by DBUri servlet to 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 the 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 schema-based XMLType, then Oracle XML DB can reduce the overhead associated with XSL transformation by leveraging the capabilities of the lazily loaded virtual DOM.
Example 3-68 shows how DBUri can access a row in the PURCHASEORDER table.
Figure 3-7 Using DBUri Servlet to Access XML Content
Note that the root of the URL is /oradb. This means that the URL will be 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-8 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 the XMLTransform() function 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-8 Database XSL Transformation of a PurchaseOrder Using DBUri Servlet
Figure 3-9 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-9 Database XSL Transformation of Departments Table Using DBUri Servlet
