1 Introduction to Oracle XML DB

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.

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:

DESCRIBE USER_XML_SCHEMAS

USER_XML_SCHEMAS

ALL_XML_SCHEMAS

DBA_XML_SCHEMAS

USER_XML_TABLES

ALL_XML_TABLES

DBA_XML_TABLES

USER_XML_TAB_COLS

ALL_XML_TAB_COLS

DBA_XML_TAB_COLS

USER_XML_VIEWS

ALL_XML_VIEWS

DBA_XML_VIEWS

USER_XML_VIEW_COLS

ALL_XML_VIEW_COLS

DBA_XML_VIEW_COLS

Views RESOURCE_VIEW and PATH_VIEW

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.

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.

See Also: Chapter 20, "Accessing Oracle XML DB Repository Data" Chapter 22, "SQL Access Using RESOURCE_VIEW and PATH_VIEW"

Figure 1-3 Oracle XML DB Repository Architecture

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 Microsoft Web Folder View of Oracle XML DB Repository"

See Also: Chapter 3, "Using Oracle XML DB"

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 Listener Status with FTP and HTTP(S) Protocol Support Enabled"

See Also: Chapter 25, "FTP, HTTP(S), and WebDAV Access to Repository Data"

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.

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.

The XMLType API

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.

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.

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

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.

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.

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 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.

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:

• Native XMLType. See Chapter 4, "XMLType Operations".

• The lazily evaluated virtual DOM support. See Chapter 11, "PL/SQL API for XMLType".

• Database-integrated XPath and XSLT support. This support is described in several chapters, including Chapter 4, "XMLType Operations" and Chapter 9, "Transforming and Validating XMLType Data".

• XML schema-caching support. See Chapter 5, "XML Schema Storage and Query: Basic".

• CTXPATH Text indexing. See Chapter 10, "Full-Text Search Over XML".

• The hierarchical index over Oracle XML DB Repository. See Chapter 20, "Accessing Oracle XML DB Repository Data".

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.