1 Introduction to Oracle XML DB

This chapter introduces the features and architecture of Oracle XML DB. It contains these topics:

Features of Oracle XML DB

Oracle XML DB is the name for a set of Oracle Database technologies related to high-performance XML storage and retrieval. It provides native XML support by encompassing both SQL and XML data models in an interoperable manner.

Oracle XML DB includes the following features:

  • Support for the World Wide Web Consortium (W3C) XML and XML Schema data models and standard access methods for navigating and querying XML. The data models are incorporated into Oracle Database.

  • Ways to store, query, update, and transform XML data while accessing it using SQL.

  • Ways to perform XML operations on SQL data.

  • A simple, lightweight XML repository where you can organize and manage database content, including XML, using a file/folder/URL metaphor.

  • A storage-independent, content-independent and programming language-independent infrastructure for storing and managing XML data. This provides new ways of navigating and querying XML content stored in the database. For example, Oracle XML DB Repository facilitates this by managing XML document hierarchies.

  • Industry-standard ways to access and update XML. The standards include the W3C XPath recommendation and the ISO-ANSI SQL/XML standard. FTP, HTTP(S), and WebDAV can be used to move XML content into and out of Oracle Database. Industry-standard APIs provide programmatic access and manipulation of XML content using Java, C, and PL/SQL.

  • XML-specific memory management and optimizations.

  • Enterprise-level Oracle Database features for XML content: reliability, availability, scalability, and security.

Oracle XML DB can be used in conjunction with Oracle XML Developer's Kit (XDK) to build applications that run in the middle tier in either Oracle Application Server or Oracle Database.

Oracle XML DB Architecture

Figure 1-1 and Figure 1-2 show the software architecture of Oracle XML DB. The two main features are:

  • Storage of XMLType tables and views

  • Oracle XML DB Repository

Figure 1-1 Oracle XML DB Architecture: XMLType Storage and Repository

Description of Figure 1-1 follows
Description of "Figure 1-1 Oracle XML DB Architecture: XMLType Storage and Repository"

Figure 1-2 Oracle XML DB Architecture: XMLType Storage

Description of Figure 1-2 follows
Description of "Figure 1-2 Oracle XML DB Architecture: XMLType Storage"

XMLType Storage

Figure 1-2 shows XMLType storage in Oracle XML DB.

When XML schemas are registered with Oracle XML DB, a set of default tables are created and used to store XML instance documents associated with the schema. These documents can be viewed and accessed in Oracle XML DB Repository.

XMLType tables and columns can be stored as Character Large Object (CLOB) values or as a set of objects. When stored as a set of objects, we refer to structured, or shredded storage.

natively, using structured XML, or in Character Large Object (CLOB) values.

Data in XMLType views can be stored in local or remote tables. Remote tables can be accessed through database links.

Both XMLType tables and views can be indexed using B*Tree, Oracle Text, function-based indexes, or bitmap indexes.

You can access data in Oracle XML DB Repository using any of the following:

  • HTTP(S), through the HTTP protocol handler.

  • WebDAV and FTP, through the WebDAV and FTP protocol server.

  • SQL, through Oracle Net Services, including Java Database Connectivity (JDBC).

Oracle XML DB supports XML data messaging using Oracle Streams Advanced Queuing (AQ) and Web Services.

APIs for XML

Table 1-1 lists the reference documentation for the PL/SQL, C, and C++ Application Programming Interfaces (APIs) that you can use to manipulate XML documents and data. The main reference for PL/SQL, C, and C++ APIs is Oracle Database PL/SQL Packages and Types Reference.

See Also:

Oracle Database XML Java API Reference for information on Java APIs for XML

Table 1-1 APIs Related to XML

API Documentation Description


Oracle Database PL/SQL Packages and Types Reference, Chapter "XMLType", Oracle Database XML C API Reference, and Oracle Database XML C++ API Reference

PL/SQL, C, and C++ APIs with XML operations on XMLType data – validation, transformation.

Database URI types

Oracle Database PL/SQL Packages and Types Reference, Chapter "Database URI TYPEs"

Functions used for various URI types.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XDB"

PL/SQL API for managing Oracle XML DB Repository resources, ACL-based security, and configuration sessions.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XDB_VERSION"

PL/SQL API for version management of repository resources.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XDBT"

PL/SQL API for creation of text indexes onrepository resources.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XDBZ"

Oracle XML DB Repository ACL-based security.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XMLDOM"

PL/SQL implementation of the DOM API for XMLType.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XMLGEN"

PL/SQL API for transformation of SQL query results into canonical XML format.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XMLPARSER"

PL/SQL implementation of the DOM Parser API for XMLType.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XMLQUERY"

PL/SQL API providing database-to-XMLType functionality. (Where possible, use DBMS_XMLGEN instead.)


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XMLSAVE"

PL/SQL API providing XML- to-database type functionality.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XMLSCHEMA"

PL/SQL API for managing XML schemas within Oracle Database – schema registration, deletion.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XMLSTORE"

PL/SQL API for storing XML data in relational tables.


Oracle Database PL/SQL Packages and Types Reference, Chapter "DBMS_XSLPROCESSOR"

PL/SQL implementation of an XSLT processor.

XML Schema Catalog Views

Table 1-2 lists the XML schema catalog views for Oracle XML DB, which provide access to metadata about XML schemas that are registered with Oracle XML DB. Information about a given view can be obtained by using the SQL command DESCRIBE. Example:


Table 1-2 XML Schema Catalog Views

Schema Description

Registered XML schemas owned by the current user


Registered XML schemas usable by the current user


Registered XML schemas in Oracle XML DB


XMLType tables owned by the current user


XMLType tables usable by the current user


XMLType tables in Oracle XML DB


XMLType table columns owned by the current user


XMLType table columns usable by the current user


XMLType table columns in Oracle XML DB


XMLType views owned by the current user


XMLType views usable by the current user


XMLType views in Oracle XML DB


XMLType view columns owned by the current user


XMLType view columns usable by the current user


XMLType view columns in Oracle XML DB


Oracle XML DB views RESOURCE_VIEW and PATH_VIEW provide SQL access to data in Oracle XML DB Repository through protocols such as FTP and WebDAV. View PATH_VIEW has one row for each unique path in the repository; view RESOURCE_VIEW has one row for each resource in the repository.

The Oracle XML DB resource API for PL/SQL, DBMS_XDB, provides query and DML functions. It is based on RESOURCE_VIEW and PATH_VIEW.

Overview of Oracle XML DB Repository

Oracle XML DB Repository is a component of Oracle Database that is optimized for handling XML data. The Oracle XML DB repository contains resources, which can be either folders (directories, containers) or files. Each resource has these properties:

  • It is identified by a path and name.

  • It has content (data), which can be XML data but need not be.

  • It has a set of system-defined metadata (properties), such as Owner and CreationDate, in addition to its content. Oracle XML DB uses this information to manage the resource.

  • It might also have user-defined metadata: information that is not part of the content, but is associated with it.

  • It has an associated access control list that determines who can access the resource, and for what operations.

Although Oracle XML DB Repository treats XML content specially, you can use Oracle XML DB Repository to store other kinds of data, besides XML; in fact, you can use the repository to access any data that is stored in Oracle Database.

Accessing and Manipulating XML in the Oracle XML DB Repository

You can access data in Oracle XML DB Repository in the following ways (see Figure 1-1):

  • Using SQL, through views RESOURCE_VIEW and PATH_VIEW

  • Using PL/SQL, through the DBML_XDB API

  • Using Java, through the Oracle XML DB resource API for Java

XML Services

Besides supporting APIs that access and manipulate data, Oracle XML DB Repository provides APIs for the following services:

  • Versioning. Oracle XML DB uses the DBMS_XDB_VERSION PL/SQL package for versioning resources in Oracle XML DB Repository. Subsequent updates to a resource create a new version (the data corresponding to previous versions is retained). Versioning support is based on the IETF WebDAV standard.

  • ACL Security. Oracle XML DB resource security is based on Access Control Lists (ACLs). Every resource in Oracle XML DB has an associated ACL that lists its privileges. Whenever resources are accessed or manipulated, the ACLs determine if the operation is legal. An ACL is an XML document that contains a set of Access Control Entries (ACE). Each ACE grants or revokes a set of permissions to a particular user or group (database role). This access control mechanism is based on the WebDAV specification.

  • Foldering. Oracle XML DB Repository manages a persistent hierarchy of folder (directory) resources that contain other resources (files or folders). Oracle XML DB modules, such as protocol servers, the schema manager, and the Oracle XML DB RESOURCE_VIEW API, use foldering to map path names to resources.

Oracle XML DB Repository Architecture

Figure 1-3 describes the Oracle XML DB Repository architecture. You can access the repository in SQL, for example, using the RESOURCE_VIEW API. In addition to the resource information, the RESOURCE_VIEW also contains a Path column, which holds the paths to each resource.

Figure 1-3 Oracle XML DB Repository Architecture

Description of Figure 1-3 follows
Description of "Figure 1-3 Oracle XML DB Repository Architecture"

How Does Oracle XML DB Repository Work?

The relational model table-row-column metaphor, is accepted as an effective mechanism for managing structured data. The model is not as effective for managing semistructured and unstructured data, such as document- or content-oriented XML. For example, a book is not easily represented as a set of rows in a table. It is more natural to represent a book as a hierarchy, book:chapter:section:paragraph, and to represent the hierarchy as a set of folders and subfolders.

  • A hierarchical metaphor manages document-centric XML content. Relational databases are traditionally poor at managing hierarchical structures and traversing a path or URL. Oracle XML DB provides a hierarchically organized repository that can be queried and through which document-centric XML content can be managed.

  • A hierarchical index speeds up folder and path traversals. Oracle XML DB includes a new, patented hierarchical index that speeds up folder and path traversals in Oracle XML DB Repository. The hierarchical index is transparent to end users, and allows Oracle XML DB to perform folder and path traversals at speeds comparable to or faster than conventional file systems.

  • You can access XML documents in Oracle XML DB Repository using standard connect-access protocols such as FTP, HTTP(S), and WebDAV, in addition to languages SQL, PL/SQL, Java, and C. The repository provides content authors and editors direct access to XML content stored in Oracle Database.

  • A resource in this context is a file or folder, identified by a URL. WebDAV is an IETF standard that defines a set of extensions to the HTTP protocol. It allows an HTTP server to act as a file server for a DAV-enabled client. For example, a WebDAV-enabled editor can interact with an HTTP/WebDAV server as if it were a file system. The WebDAV standard uses the term resource to describe a file or a folder. Each resource managed by a WebDAV server is identified by a URL. Oracle XML DB adds native support to Oracle Database for these protocols. The protocols were designed for document-centric operations. By providing support for these protocols, Oracle XML DB allows Windows Explorer, Microsoft Office, and products from vendors such as Altova, Macromedia, and Adobe to work directly with XML content stored in Oracle XML DB Repository. Figure 1-4 shows the root-level directory of the repository as seen from Microsoft Web Folder.

Figure 1-4 Microsoft Web Folder View of Oracle XML DB Repository

Description of Figure 1-4 follows
Description of "Figure 1-4 Microsoft Web Folder View of Oracle XML DB Repository"

Hence, WebDAV clients such as Microsoft Windows Explorer can connect directly to Oracle XML DB Repository. No additional Oracle Database or Microsoft-specific software or other complex middleware is needed. End users can work directly with Oracle XML DB Repository using familiar tools and interfaces.

Oracle XML DB Protocol Architecture

One key feature of the Oracle XML DB architecture is that HTTP(S), WebDAV, and FTP protocols are supported using the same architecture used to support Oracle Data Provider for .NET (ODP.NET) in a shared server configuration. The Listener listens for HTTP(S) and FTP requests in the same way that it listens for ODP .NET service requests. When the Listener receives an HTTP(S) or FTP request, it hands it off to an Oracle Database shared server process which services it and sends the appropriate response back to the client.You can use the TNS Listener command lsnrctl status to verify that HTTP(S) and FTP support has been enabled – see Figure 1-5.

Figure 1-5 Listener Status with FTP and HTTP(S) Protocol Support Enabled

Description of Figure 1-5 follows
Description of "Figure 1-5 Listener Status with FTP and HTTP(S) Protocol Support Enabled"

Programmatic Access to Oracle XML DB (Java, PL/SQL, and C)

All Oracle XML DB functionality is accessible from C, PL/SQL, and Java. The most popular ways to build web-based applications are these:

  • Using servlets and JSPs (Java Server Pages). A typical API accesses data using Java Database Connectivity (JDBC).

  • Using XSL and XSPs (XML Style Sheets plus XML Server Pages). A typical API accesses data in the form of XML documents that are processed using a Document Object Model (DOM) API implementation.

Oracle XML DB supports both of these styles of application development. It provides Java, PL/SQL, and C implementations of the DOM API.Applications that use JDBC, such as those based on servlets, need prior knowledge of the data structure they are processing. Oracle JDBC drivers allow you to access and update XMLType tables and columns, and call PL/SQL procedures that access Oracle XML DB Repository.Applications that use DOM, such as those based on XSLT transformations, typically require less knowledge of the data structure. DOM-based applications use string names to identify pieces of content, and must dynamically walk through the DOM tree to find the required information. For this Oracle XML DB supports the use of the DOM API to access and update XMLType columns and tables. Programming to a DOM API is more flexible than programming through JDBC, but it may require more resources at run time.

Oracle XML DB Features

Any database used for managing XML must be able to persist XML documents. Oracle XML DB is capable of much more than this. It provides standard database features such as transaction control, data integrity, replication, reliability, availability, security, and scalability., while also allowing for efficient indexing, querying, updating, and searching of XML documents in an XML-centric manner.

The hierarchical nature of XML presents the traditional relational database with a number of challenges:

  • In a relational database the table-row metaphor locates content. Primary-Key Foreign-Key relationships help define the relationships between content. Content is accessed and updated using the table-row-column metaphor. XML on the other hand uses hierarchical techniques to achieve the same functionality. A URL is used to locate an XML document. URL-based standards such as XLink are used to defined the relationships between XML documents. W3C Recommendations like XPath are used to access and update content contained within XML documents. Both URLs and XPath expressions are based on hierarchical metaphors. A URL uses a path through a folder hierarchy to identify a document, whereas XPath uses a path through the node hierarchy of an XML document to access part of an XML document.

Oracle XML DB addresses these challenges by introducing new SQL functions and methods that allow the use of XML-centric metaphors, such as XPath expressions for querying and updating XML Documents. The major features of Oracle XML DB are these:

XMLType Datatype

XMLType is a native server datatype that lets the database understand that a column or table contains XML. This is similar to the way that date and timestamp datatypes let the database understand that a column contains a date. Datatype XMLType also provides methods that allow common operations such as XML schema validation and XSL transformations on XML content.You can use XMLType like any other datatype. For example, you can use XMLType when:

  • Creating a column in a relational table

  • Declaring PL/SQL variables

  • Defining and calling PL/SQL procedures and functions

Since XMLType is an object type, you can also create a table of XMLType. By default, an XMLType table or column can contain any well-formed XML document.

The following example shows creating a simple table with an XMLType column.

Oracle XML DB Stores XML Text in CLOBs

Oracle XML DB stores the content of the document as XML text using the Character Large Object (CLOB) datatype. This allows for maximum flexibility in terms of the shape of the XML structures that can be stored in a single table or column and the highest rates of ingestion and retrieval.

XMLType Tables and Columns Can Conform to an XML Schema

XMLType tables or columns can be constrained and conform to an XML schema. This has several advantages:

  • The database will ensure that only XML documents that validate against the XML schema can be stored in the column or table.

  • Since the contents of the table or column conform to a known XML structure, Oracle XML DB can use the information contained in the XML schema to provide more intelligent query and update processing of the XML.

  • Constraining the XMLType to an XML schema provides the option of storing the content of the document using structured-storage techniques. Structured-storage decomposes or shreds the content of the XML document and stores it as a set of SQL objects rather than simply storing the document as text in a CLOB. The object-model used to store the document is automatically derived from the contents of the XML schema.


Datatype XMLType provides the following:

  • Constructors. These allow an XMLType value to be created from a VARCHAR, CLOB, BLOB, or BFILE value.

  • Methods. A number of XML-specific methods that operate on XMLType instances. XMLType methods provide support for the following common operations:

    • Extract a subset of nodes contained in the XMLType – method extract().

    • Check whether or not a particular node exists in the XMLType – method existsNode().

    • Validate the contents of the XMLType against an XML schema – method schemaValidate().

    • Perform an XSL Transformation – method transform().

XML Schema Support

Support for the Worldwide Web Consortium (W3C) XML Schema Recommendation is a key feature in Oracle XML DB. XML Schema specifies the structure, content, and certain semantics of a set of XML documents. It is described in detail at http://www.w3.org/TR/xmlschema-0/.

XML Schema Unifies Document and Data Modeling

XML Schema unifies both document and data modeling. In Oracle XML DB, you can create tables and types automatically using XML Schema. In short, this means that you can develop and use a standard data model for all your data, structured, unstructured, and semistructured. You can use Oracle XML DB to enforce this data model for all your data.

You Can Create XMLType Tables and Columns, Ensure DOM Fidelity

You can create XML schema-based XMLType tables and columns and optionally specify, for example, that they:

  • Conform to pre-registered XML schemas

  • Are stored in structured storage format specified by the XML schema, maintaining DOM fidelity

Use XMLType Views to Wrap Relational Data

You can also choose to wrap existing relational and object-relational data into XML format using XMLType views.

You can store an XMLType object as an XML object that is based on an XML schema or not based on an XML schema:

  • schema-based objects. These are stored in Oracle XML DB as Large Objects (LOBs) or in structured storage (object-relationally) in tables, columns, or views.

  • Non-schema-based objects. These are stored in Oracle XML DB as LOBs.

You can map from XML instances to structured or LOB storage. The mapping can be specified in an XML schema, and the schema must be registered in Oracle XML DB. This is a required step before storing XML schema-based instance documents. Once registered, the XML schema can be referenced using its URL.

W3C Schema for Schemas

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. An XML schema definition (XSD) is an XML document, that is compliant with the vocabulary defined by the "Schema for Schemas". An XML schema uses vocabulary defined by W3C XML Schema Working Group to create a collection of type definitions and element declarations that declare a shared vocabulary for describing the contents and structure of a new class of XML documents.

XML Schema Base Set of Data Types Can be Extended

The XML Schema language provides strong typing of elements and attributes. It defines 47 scalar data types. The base set of data types can be extended using object-oriented techniques like inheritance and extension to define more complex types. W3C XML Schema vocabulary also includes constructs that allow the definition of complex types, substitution groups, repeating sets, nesting, ordering, and so on. Oracle XML DB supports all of constructs defined by the XML Schema Recommendation, except for redefines.

XML schemas are most commonly used as a mechanism for checking whether instance documents conform with their specifications (validation). Oracle XML DB includes methods and SQL functions that allow an XML schema to be used for this.


This manual uses the term XML schema (lower-case "s") to reference any XML schema that conforms to the W3C XML Schema (upper-case "S") Recommendation. Since an XML schema is used to define a class of XML documents, the term instance document is often used to describe an XML document that conforms to a particular XML schema.

See Also:

Appendix A, "XML Schema Primer" and Chapter 5, "XML Schema Storage and Query: Basic" for more information about using XML schemas with Oracle XML DB

Structured Versus Unstructured Storage

One key decision to make when using Oracle XML DB for persisting XML documents is when to use structured storage and when to use unstructured storage.

  • Unstructured storage provides for the highest possible throughput when inserting and retrieving entire XML documents. It also provides the greatest degree of flexibility in terms of the structure of the XML that can be stored in a XMLType table or column. These throughput and flexibility benefits come at the expense of certain aspects of intelligent processing. There is little the database can do to optimize queries or updates on XML stored using a CLOB datatype.

  • Structured storage has a number of advantages for managing XML, including optimized memory management, reduced storage requirements, b-tree indexing and in-place updates. These advantages are at a cost of somewhat increased processing overhead during ingestion and retrieval and reduced flexibility in terms of the structure of the XML that can be managed by a given XMLType table or column.

Table 1-3 outlines the merits of structured and unstructured storage.

Table 1-3 XML Storage Options: Structured or Unstructured

Unstructured Storage Structured Storage


Highest possible throughput when ingesting and retrieving the entire content of an XML document.

The decomposition process results in slightly reduced throughput when ingesting retrieving the entire content of an XML document.


Provides the maximum amount of flexibility in terms of the structure of the XML documents that can be stored in an XMLType column or table.

Limited Flexibility. Only documents that conform to the XML schema can be stored in the XMLType table or column. Changes to the XML schema may require data to be unloaded and re-loaded.

XML Fidelity

Delivers Document Fidelity: Maintains the original XML byte for byte, which may be important to some applications.

DOM Fidelity: A DOM created from an XML document that has been stored in the database will be identical to a DOM created from the original document. However trailing new lines, white space characters between tags and some data formatting may be lost.

Update Operations

When any part of the document is updated the entire document must be written back to disk.

The majority of update operations can be performed using XPath rewrite. This allows in-place, piece-wise update, leading to significantly reduced response times and greater throughput.

XPath based queries

XPath operations evaluated by constructing DOM from CLOB and using functional evaluations. This can be very expensive when performing operations on large collections of documents.

XPath operations may be evaluated using XPath rewrite, leading to significantly improved performance, particularly with large collections of documents.

SQL Constraint Support

SQL constraints are not currently available.

SQL constraints are supported.

Indexing Support

Text and function-based indexes.

B-Tree, text and function-based indexes.

Optimized Memory Management

XML operations on the document require creating a DOM from the document.

XML operations can be optimized to reduce memory requirements.

Much valuable information in an organization is in the form of semistructured and unstructured data. Typically this data is in files stored on a file server or in a CLOB column inside a database. The information in these files is in proprietary- or application-specific formats. It can only be accessed through specialist tools, such as word processors or spreadsheets, or programmatically using complex, proprietary APIs. Searching across this information is limited to facilities provided by a crawler or full-text indexing.

Major reasons for the rapid adoption of XML are that it allows for:

  • Stronger data management

  • More open access to semistructured and unstructured content.

Replacing proprietary file formats with XML allows organizations to achieve much higher levels of reuse of their semistructured and unstructured data. The content can be accurately described using XML schemas. The content can be easily accessed and updated using standard APIs based on DOM and XPath.

For example, information contained in an Excel spreadsheet is only accessible to the Excel program, or to a program that uses Microsoft COM APIs. The same information, stored in an XML document is accessible to any tool that can leverage the XML programming model. Structured data on the other hand does not suffer from these limitations. Structured data is typically stored as rows in tables within a relational database. These tables are accessed and searched using the relational model and the power and openness of SQL from a variety of tools and processing engines.

XML/SQL Duality

A key objective of Oracle XML DB is to provide XML/ SQL duality. This means that the XML programmer can leverage the power of the relational model when working with XML content and the SQL programmer can leverage the flexibility of XML when working with relational content. This provides application developers with maximum flexibility, allowing them to use the most appropriate tools for a particular business problem.

Relational and XML Metaphors are Interchangeable: Oracle XML DB erases the traditional boundary between applications that work with structured data and those that work with semistructured and unstructured content. With Oracle XML DB the relational and XML metaphors become interchangeable.

XML/SQL duality means that the same data can be exposed as rows in a table and manipulated using SQL or exposed as nodes in an XML document and manipulated using techniques such as DOM or XSL transformation. Access and processing techniques are totally independent of the underlying storage format.

These features provide new, simple solutions to common business problems. For example:

  • Relational data can quickly and easily be converted into HTML pages. Oracle XML DB provides new SQL functions that make it possible to generate XML directly from a SQL query. The XML can be transformed into other formats, such as HTML using the database-resident XSLT processor.

  • You can easily leverage all of the information contained in their XML documents without the overhead of converting back and forth between different formats. With Oracle XML DB you can access XML content using SQL queries, On-line Analytical Processing (OLAP), and Business-Intelligence/Data Warehousing operations.

  • Text, spatial data, and multimedia operations can be performed on XML Content.

SQL/XML INCITS Standard SQL Functions

Oracle XML DB provides the SQL functions defined in the SQL/XML standard. The SQL/XML standard is defined by specifications prepared by the International Committee for Information Technology Standards (INCITS) Technical Committee H2. INCITS is the main standards body for developing standards for the syntax and semantics of database languages, including SQL.

The SQL/XML standard is an evolving standard, so the syntax and semantics of its functions are subject to change in the future. The Oracle XML DB implementation of SQL/XML functions will evolve accordingly.

SQL/XML functions fall into two categories:

  • Functions that make it possible to query and access XML content as part of normal SQL operations.

  • Functions that provide a standard way of generating XML from the result of a SQL SELECT statement.

With the SQL/XML functions you can address XML content in any part of a SQL statement. They use XPath notation to traverse the XML structure and identify the node or nodes on which to operate. The ability to embed XPath expressions in SQL statements greatly simplifies XML access. The following describes briefly some of the more important SQL/XML functions:

  • existsNode – This is used in the WHERE clause of a SQL statement to restrict the set of documents returned by a query. The existsNode SQL function takes an XPath expression and applies it to an XML document. The function returns true (1) or false (0), depending on whether or not the document contains a node that matches the XPath expression.

  • extract – This takes an XPath expression and returns the nodes that match the expression, as an XML document or fragment. If only a single node matches the XPath expression, then the result is a well-formed XML document. If multiple nodes match the XPath expression, then the result is a document fragment.

  • extractValue – This takes an XPath expression and returns the corresponding leaf node. The XPath expression passed to extractValue should identify a single attribute or an element that has precisely one text node child. The result is returned in the appropriate SQL data type. Function extractValue is essentially a shortcut for extract plus either getStringVal() or getNumberVal().

  • updateXML – This allows partial updates to be made to an XML document, based on a set of XPath expressions. Each XPath expression identifies a target node in the document, and a new value for that node. SQL function updateXML allows multiple updates to be specified for a single XML document.

  • XMLSequence – This makes it possible to expose the members of a collection as a virtual table

See Also:

Rewriting of XPath Expressions: XPath Rewrite

The SQL/XML SQL functions and their corresponding XMLType methods allow XPath expressions to be used to search collections of XML documents and to access a subset of the nodes contained within an XML document.

See Also:

"Generating XML Using SQL Functions" for information on SQL/XML functions

How XPath Expressions are Evaluated by Oracle XML DB

Oracle XML DB provides two ways of evaluating XPath expressions that operate on XMLType columns and tables, depending on the XML storage method used:

  • Structured-storage XML data: Oracle XML DB attempts to translate the XPath expression in a SQL/XML function into an equivalent SQL query. The SQL query references the object-relational data structures that underpin a schema-based XMLType. This process is referred to as XPath rewrite. It can occur when performing queries and UPDATE operations.

  • Unstructured-storage XML data: Oracle XML DB evaluates the XPath expression using functional evaluation. Functional evaluation builds a DOM tree for each XML document, and then resolves the XPath programmatically using the methods provided by the DOM API. If the operation involves updating the DOM tree, the entire XML document has to be written back to disc when the operation is completed.

Efficient Processing of SQL That Contains XPath Expressions

Oracle XML DB can rewrite SQL statements that contain XPath expressions to purely relational SQL statements, which can be processed efficiently. In this way, Oracle XML DB insulates the database optimizer from having to understand XPath notation and the XML data model. The database optimizer simply processes the rewritten SQL statement in the same manner as other SQL statements.

This means that the database optimizer can derive an execution plan based on conventional relational algebra. This allows Oracle XML DB to leverage all the features of the database and ensure that SQL statements containing XPath expressions are executed in a highly performant and efficient manner. To sum up, there is little overhead with XPath rewrites, and Oracle XML DB can execute XPath-based queries at near-relational speed, while preserving the XML abstraction.

When Can XPath Rewrite Occur?

XPath rewrite is possible when:

  • A SQL statement contains SQL/XML SQL functions or XMLType methods that use XPath expressions to refer to one or more nodes within a set of XML documents.

  • An XMLType column or table containing the XML documents is associated with a registered XML schema.

  • An XMLType column or table uses structured storage techniques to provide the underlying storage model.

  • The nodes referenced by an XPath expression can be mapped, using the XML schema, to attributes of the underlying SQL object model.

What is the XPath-Rewrite Process?

XPath rewrite performs the following tasks:

  1. Identify the set of XPath expressions included in the SQL statement.

  2. Translate each XPath expression into an object relational SQL expression that references the tables, types, and attributes of the underlying SQL: 1999 object model.

  3. Rewrite the original SQL statement into an equivalent object relational SQL statement.

  4. Pass the new SQL statement to the database optimizer for plan generation and query execution.

In certain cases, XPath rewrite is not possible. This normally occurs when there is no SQL equivalent of the XPath expression. In this situation Oracle XML DB performs a functional evaluation of the XPath expressions. In general, functional evaluation of a SQL statement is more expensive than XPath rewrite, particularly if the number of documents that needs to be processed is large. However the major advantage of functional evaluation is that it is always possible, regardless of whether or not the XMLType is stored using structured storage and regardless of the complexity of the XPath expression. When documents are stored using unstructured storage (in a CLOB value), functional evaluation is necessary any time SQL functions except existsNode are used. Function existsNode will also result in functional evaluation unless a CTXXPATH index or function-based index can be used to resolve the query.Understanding the concept of XPath rewrite, and the conditions under which it can take place, is a key step in developing Oracle XML DB applications that will deliver the required levels of scalability and performance.

Oracle XML DB Benefits

The following sections describe several benefits for using Oracle XML DB advantages including:

Figure 1-6 summarizes the Oracle XML DB benefits.

Figure 1-6 Oracle XML DB Benefits

Description of Figure 1-6 follows
Description of "Figure 1-6 Oracle XML DB Benefits"

Unifying Data and Content with Oracle XML DB

Most application data and Web content is stored in a relational database or a file system, or both. XML is often used for transport, and it is generated from a database or a file system. As the volume of XML transported grows, the cost of regenerating these XML documents grows, and these storage methods become less effective at accommodating XML content.

Figure 1-7 Unifying Data and Content: Some Common XML Architectures

Description of Figure 1-7 follows
Description of "Figure 1-7 Unifying Data and Content: Some Common XML Architectures"

Organizations today typically manage their structured data and unstructured data differently:

  • Unstructured data, in tables, makes document access transparent and table access complex

  • Structured data, often in binary large objects (such as in BLOBs), makes access more complex and table access transparent.

With Oracle XML DB, you can store and manage data that is structured, unstructured, and semistructured using a standard data model and standard SQL and XML. You can perform SQL operations on XML documents and XML operations on object-relational (such as table) data.

Exploiting Database Capabilities

Oracle Database has strong XML support with the following key capabilities:

  • Indexing and Search: Applications use queries such as "find all the product definitions created between March and April 2002", a query that is typically supported by a B*Tree index on a date column. Oracle XML DB can enable efficient structured searches on XML data, saving content-management vendors the need to build proprietary query APIs to handle such queries. See Chapter 4, "XMLType Operations", Chapter 10, "Full-Text Search Over XML", and Chapter 16, "Generating XML Data from the Database".

  • Updates and Transaction Processing: Commercial relational databases use fast updates of subparts of records, with minimal contention between users trying to update. As traditionally document-centric data participate in collaborative environments through XML, this requirement becomes more important. File or CLOB storage cannot provide the granular concurrency control that Oracle XML DB does. See Chapter 4, "XMLType Operations".

  • Managing Relationships: Data with any structure typically has foreign key constraints. Currently, XML data-stores lack this feature, so you must implement any constraints in application code. Oracle XML DB enables you to constrain XML data according to XML schema definitions and hence achieve control over relationships that structured data has always enjoyed. See Chapter 5, "XML Schema Storage and Query: Basic" and the purchase-order examples in Chapter 4, "XMLType Operations".

  • Multiple Views of Data: Most enterprise applications need to group data together in different ways for different modules. This is why relational views are necessary—to allow for these multiple ways to combine data. By allowing views on XML, Oracle XML DB creates different logical abstractions on XML for, say, consumption by different types of applications. See Chapter 18, "XMLType Views".

  • Performance and Scalability: Users expect data storage, retrieval, and query to be fast. Loading a file or CLOB value, and parsing, are typically slower than relational data access. Oracle XML DB dramatically speeds up XML storage and retrieval. See Chapter 2, "Getting Started with Oracle XML DB" and Chapter 3, "Using Oracle XML DB".

  • Ease of Development: Databases are foremost an application platform that provides standard, easy ways to manipulate, transform, and modify individual data elements. While typical XML parsers give standard read access to XML data they do not provide an easy way to modify and store individual XML elements. Oracle XML DB supports a number of standard ways to store, modify, and retrieve data: using XML Schema, XPath, DOM, and Java.

Exploiting XML Capabilities

If the drawbacks of XML file storage force you to break down XML into database tables and columns, there are several XML advantages you have left:

  • Structure Independence: The open content model of XML cannot be captured easily in the pure tables-and-columns world. XML schemas allow global element declarations, not just scoped to a container. Hence you can find a particular data item regardless of where in the XML document it moves to as your application evolves. See Chapter 5, "XML Schema Storage and Query: Basic".

  • Storage Independence: When you use relational design, your client programs must know where your data is stored, in what format, what table, and what the relationships are among those tables. XMLType enables you to write applications without that knowledge and allows DBAs to map structured data to physical table and column storage. See Chapter 5, "XML Schema Storage and Query: Basic" and Chapter 20, "Accessing Oracle XML DB Repository Data".

  • Ease of Presentation: XML is understood natively by Web browsers, many popular desktop applications, and most Internet applications. Relational data is not generally accessible directly from applications; programming is required to make relational data accessible to standard clients. Oracle XML DB stores data as XML and makes it available as XML outside the database; no extra programming is required to display database content. See:

  • Ease of Interchange: XML is the language of choice in business-to-business (B2B) data exchange. If you are forced to store XML in an arbitrary table structure, you are using some kind of proprietary translation. Whenever you translate a language, information is lost and interchange suffers. By natively understanding XML and providing DOM fidelity in the storage/retrieval process, Oracle XML DB enables a clean interchange. See:

Oracle XML DB Offers Faster Storage and Retrieval of Complex XML Documents

Users today face a performance barrier when storing and retrieving complex, large, or many XML documents. Oracle XML DB provides very high performance and scalability for XML operations. The major performance features are:

Oracle XML DB Helps You Integrate Applications

Oracle XML DB enables data from disparate systems to be accessed through gateways and combined into one common data model. This reduces the complexity of developing applications that must deal with data from different stores.

When Your Data Is Not XML You Can Use XMLType Views

XMLType views provide a way for you wrap existing relational and object-relational data in XML format. This is especially useful if, for example, your legacy data is not in XML but you need to migrate to an XML format. Using XMLType views you do not need to alter your application code.

To use XMLType views, you must first register an XML schema with annotations that represent the bi-directional mapping from XML to SQL object types and back to XML. An XMLType view conforming to this schema (mapping) can then be created by providing an underlying query that constructs instances of the appropriate SQL object type. Figure 1-6 summarizes the Oracle XML DB advantages.

Searching XML Data Stored in CLOBs Using Oracle Text

Oracle enables special indexing on XML, including Oracle Text indexes for section searching, special SQL functions to process XML, aggregation of XML, and special optimization of queries involving XML.

XML data stored in Character Large Objects (CLOB datatype) or stored in XMLType columns in structured storage (object-relationally), can be indexed using Oracle Text. SQL functions hasPath and inPath are designed to optimize XML data searches where you can search within XML text for substring matches.

Oracle XML DB also provides:

Building Messaging Applications using Oracle Streams Advanced Queuing

Oracle Streams Advanced Queuing supports the use of:

Requirements for Running Oracle XML DB

Oracle XML DB is available with Oracle9i release 2 (9.2) and higher.

See Also:

Standards Supported by Oracle XML DB

Oracle XML DB supports all major XML, SQL, Java, and Internet standards:

  • W3C XML Schema 1.0 Recommendation. You can register XML schemas, validate stored XML content against XML schemas, or constrain XML stored in the server to XML schemas.

  • W3C XPath 1.0 Recommendation. You can search or traverse XML stored inside the database using XPath, either from HTTP(S) requests or from SQL.

  • ISO-ANSI Working Draft for XML-Related Specifications (SQL/XML) [ISO/IEC 9075 Part 14 and ANSI]. You can use the emerging ANSI SQL/XML functions to query XML from SQL. The task force defining these specifications falls under the auspices of the International Committee for Information Technology Standards (INCITS). The SQL/XML specification will be fully aligned with SQL:2003. SQL/XML functions are sometimes referred to as SQLX functions.

  • Java Database Connectivity (JDBC) API. JDBC access to XML is available for Java programmers.

  • W3C XSL 1.0 Recommendation. You can transform XML documents at the server using XSLT.

  • W3C DOM Recommendation Levels 1.0 and 2.0 Core. You can retrieve XML stored in the server as an XML DOM, for dynamic access.

  • Protocol support. You can store or retrieve XML data from Oracle XML DB using standard protocols such as HTTP(S), FTP, and IETF WebDAV, as well as Oracle Net.

  • Java Servlet version 2.2, (except that the servlet WAR file, web.xml is not supported in its entirety, and only one ServletContext and one web-app are currently supported, and stateful servlets are not supported).

  • Simple Object Access Protocol (SOAP). You can access XML stored in the server from SOAP requests. You can build, publish, or find Web Services using Oracle XML DB and Oracle9iAS, using WSDL and UDDI. You can use Oracle Streams Advanced Queuing IDAP, the SOAP specification for queuing operations, on XML stored in Oracle Database.

See Also:

Oracle XML DB Technical Support

Besides your regular channels of support through your customer representative or consultant, technical support for Oracle Database XML-enabled technologies is available free through the Discussions option on Oracle Technology Network (OTN):


Oracle XML DB Examples Used in This Manual

This manual contains examples that illustrate the use of Oracle XML DB and XMLType. The examples are based on a number of database schema, sample XML documents, and sample XML schema.

Further Oracle XML DB Case Studies and Demonstrations

Visit OTN to view Oracle XML DB examples, white papers, case studies, and demonstrations.

Oracle XML DB Examples and Tutorials

You can peruse more Oracle XML DB examples on OTN:


Comprehensive XML classes on how to use Oracle XML DB are also available. See the Oracle University link on OTN.

Oracle XML DB Case Studies and Demonstrations

Several detailed Oracle XML DB case studies are available on OTN and include the following:

  • Oracle XML DB Downloadable Demonstration. This detailed demonstration illustrates how to use many Oracle XML DB features. Parts of this demonstration are also included in Chapter 3, "Using Oracle XML DB".

  • Content Management System (CMS) application. This illustrates how you can store files on the database using Oracle XML DB Repository in hierarchically organized folders, place the files under version control, provide security using ACLs, transform XML content to a desired format, search content using Oracle Text, and exchange XML messages using Oracle Streams Advanced Queueing (to request privileges on files or for sending externalization requests). See http://www.oracle.com/technology/sample_code/tech/xml/xmldb/cmsxdb/content.html.

  • XML Dynamic News. This is a complete J2EE 1.3 based application that demonstrates Java and Oracle XML DB features for an online news portal. News feeds are stored and managed persistently in Oracle XML DB. Various other news portals can customize this application to provide static or dynamic news services to end users. End users can personalize their news pages by setting their preferences. The application also demonstrates the use of Model View Controller (MVC) architecture and various J2EE design patterns. See http://www.oracle.com/technology/sample_code/tech/xml/xmlnews/content.html

  • SAX Loader Application. This demonstrates an efficient way to break up large files containing multiple XML documents outside the database and insert them into the database as a set of separate documents. This is provided as a standalone and a web-based application. Oracle XML DB Utilities package. This highlights the subprograms provided with the XDB_Utilities package. These subprograms operate on BFILE values, CLOB values, DOM, and Oracle XML DB Resource APIs. With this package, you can perform basic Oracle XML DB foldering operations, read and load XML files into a database, and perform basic DOM operations through PL/SQL.Card Payment Gateway Application. This application uses Oracle XML DB to store all your data in XML format and enables access to the resulting XML data using SQL. It illustrates how a credit card company can store its account and transaction data in the database and also maintain XML fidelity. Survey Application. This application determines what members want from Oracle products. OTN posts the online surveys and studies the responses. This Oracle XML DB application demonstrates how a company can create dynamic, interactive HTML forms, deploy them to the Internet, store the responses as XML, and analyze them using the XML enabled Oracle Database.