1 Introducing Oracle XML DB

XMLType Storage

Figure 1-2 describes the XMLType tables and views storage architecture.

When XML Schema are registered with Oracle XML DB, XML elements for XMLType tables, tables with XMLType columns, and XMLType views, are mapped to database tables. These can be viewed and accessed in XML repository.

Data in XMLType tables and tables with XMLType columns can be stored in Character Large Objects (CLOB) or natively using structured XML.

Data in XMLType views can be stored in local tables or remote tables. The latter can be accessed through DBLinks.

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

Options for accessing data in XML repository include:

• HTTP, through the HTTP protocol handler.

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

• SQL, through Oracle Net Services including JDBC. Oracle XML DB also supports XML data messaging using Oracle Streams Advanced Queuing (AQ) and Web Services.

  
  

  See Also: Part II. " Storing and Retrieving XML Data in Oracle XML DB" Chapter 24, " FTP, HTTP, and WebDAV Access to Repository Data" Chapter 26, " Managing Oracle XML DB Using Oracle Enterprise Manager" Chapter 29, " Exchanging XML Data With Oracle Streams AQ "

  
  

Oracle XML DB Repository

Oracle XML DB repository (XML repository or repository) is an XML data repository in Oracle Database optimized for handling XML data. At the heart of Oracle XML DB repository is the Oracle XML DB foldering module.

The contents of Oracle XML DB repository are referred to as resources. These can be either containers (directories or folders) or files. All resources are identified by a path name and have a (extensible) set of (metadata) properties such as Owner, CreationDate, and so on, in addition to the actual contents defined by the user.

XML Services

Besides supporting APIs that access and manipulate data Oracle XML DB repository provides API 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 the resource results in new versions being created while 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 the ACL (Access Control Lists) mechanism. Every resource or document 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 foldering module manages a persistent hierarchy of containers (folders or directories) and resources. Other Oracle XML DB modules, such as protocol servers, the schema manager, and the Oracle XML DB RESOURCE_VIEW API use the foldering module to map path names to resources.

Figure 1-2 Oracle XML DB: XMLType Storage and Retrieval Architecture

Description of the illustration adxdb028.gif

XML Repository Architecture

Figure 1-3 describes the Oracle XML DB repository architecture. A resource is any piece of content managed by Oracle XML DB. Each resource has a name, an associated access control list that determines who can see the resource, certain static properties, and additional properties that are extensible by the application. Applications using the repository obtain a logical view of parent-child folders. You can access this Oracle Database repository, for example, in SQL, 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 18, " Accessing Oracle XML DB Repository Data" Chapter 20, " SQL Access Using RESOURCE_VIEW and PATH_VIEW "

Figure 1-3 Oracle XML DB Repository Architecture

Description of the illustration adxdb030.gif

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 semi-structured 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 not good at managing hierarchical structures and traversing a path or URL. Oracle XML DB provides a hierarchically organized XML 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.

• Access XML documents using FTP, HTTP, and WebDAV protocols; SQL, PL/SQL, Java, and C languages. You can access XML documents in the repository using standard connect-access protocols such as FTP, HTTP, and WebDAV, in addition to languages SQL, PL/SQL, Java, and C. Oracle XML DB 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. The WebDAV standard uses the term resource to describe a file or a folder. Every 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 Oracle XML DB repository as seen from Microsoft Web Folder.

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

Description of the illustration 3_b_webfldr.gif

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

Hence, WebDAV clients such as Microsoft Windows Explorer can connect directly to 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 features of the Oracle XML DB architecture is that HTTP, 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 and FTP requests in the same way that it listens for ODP .NET service requests. When the listener receives an HTTP 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.As can be seen from Figure 1-5, you can use the TNS Listener command lsnrctl status to verify that HTTP and FTP support has been enabled.

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

Description of the illustration 1_protocols.jpg

See Also: Chapter 24, " FTP, HTTP, 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. Today, the most popular methods for building web-based applications are servlets plus JSPs (Java Server Pages) and XSL plus XSPs (XML Style Sheets plus XML Server Pages). Typical API implementation includes:

• Servlets and JSPs. These APIs access data using JDBC.

• XSL/XSPs. These APIs expect data in the form of XML documents that are processed using a Document Object Model (DOM) API implementation.

Oracle XML DB supports both 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

XMLType is a native server datatype that allows the database to understand that a column or table contains XML. This is similar to the way that the DATE datatype allows the database to understand that a column contains a date. XMLType also provides methods that allow common operations such as XML schema validation and XSL transformations on XML content.You can use the XMLType data-type 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 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 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 datatype provides the following structures:

• 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 can operate on XMLType objects. The methods provided by XMLType provide support for common operations such as:

  • Extracting a subset of nodes contained in the XMLType, using extract()

  • Checking whether or not a particular node exists in the XMLType, using existsNode()

  • Validating the contents of the XMLType against an XML schema, using schemaValidate()

  • Performing an XSL Transformation, using transform()

XML Schema

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 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 pseudo/semi-structured. You can use Oracle XML DB to enforce this data model for all your data.

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

• Conform to pre-registered XML schema

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

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 schema-based object or a non-XML schema-based object:

• XML 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-XML 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 XML schema and the XML 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.

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.

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 schema are most commonly used as a mechanism for validating that instance documents conform with their specifications. Oracle XML DB includes methods and SQL operators 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- 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-1 outlines the merits of structured and unstructured storage.

Much valuable information in an organization is in the form of semi-structured 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 semi-structured and unstructured content.

Replacing proprietary file formats with XML allows organizations to achieve much higher levels of reuse of their semi-structured and unstructured data. The content can be accurately described using XML Schema. 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's 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 to solving 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 semi-structured 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 operators 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 ICITS Standard Operators

Oracle XML DB provides an implementation of the majority of operators incorporated into the forthcoming SQL/XML standard. SQL/XML is defined by specifications prepared by the International Committee for Information Technology Standards (Technical Committee H2), the main standards body for developing standards for the syntax and semantics of database languages, including SQL.

See http://www.ncits.org/tc_home/h2.htm for more information. SQL/XML operators fall into two categories:

• The first category consists of a set of operators that make it possible to query and access XML content as part of normal SQL operations.

• The second category consists of a set of operators that provide an industry standard method for generating XML from the result of a SQL SELECT statement.

With these SQL/XML operators 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 XPath Recommendation is described in detail at http://www.w3.org/TR/xpath. The ability to embed XPath expressions in SQL statements greatly simplifies XML access. The following describes briefly the provided SQL/XML operators:

• existsNode(). This is used in the WHERE clause of a SQL statement to restrict the set of documents returned by a query. The existsNode() operator takes an XPath expression and applies it an XML document. The operator and returns true (1) or false (0) depending on whether or not the document contains a node which 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, the result is a well-formed XML document. If multiple nodes match the XPath expression, the result is a document fragment.

• extractValue(). This takes an XPath expression and returns the corresponding leaf level node. The XPath expression passed to extractValue() should identify a single attribute, or an element which has precisely one text node child. The result is returned in the appropriate SQL data type.

• 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. The updateXML() operator 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

Detailed examples of the way in which these functions are used are provided in the PurchaseOrder examples in Chapter 3, " Using Oracle XML DB".

XPath and XQuery Rewrite

The SQL/XML operators, and 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

Oracle XML DB has two methods of evaluating XPath expressions that operate on XMLType columns and tables. For XML:

• Stored using structured storage techniques, Oracle XML DB attempts to translate the XPath expression in a SQL/XML operator into an equivalent SQL query. The SQL query references the object-relational data structures that underpin a schema-based XMLType. While this process is referred to as query-rewrite, it can also occur when performing UPDATE operations.

• Stored using unstructured storage, Oracle XML DB will evaluate the XPath 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.

Query-rewrites allow the database to efficiently process SQL statements containing one or more XPath expressions using conventional relational SQL. By translating the XPath expression into a conventional SQL statement, Oracle XML DB insulates the database optimizer from having to understand XPath notation and the XML data model. The database optimizer simply processes the re-written 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 query-rewrites and Oracle XML DB can execute XPath-based queries at near-relational speed, while preserving the XML abstraction.

Query-rewrites are possible when:

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

• The XMLType column or table containing the XML documents is associated with a registered XML Schema.

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

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

The query-rewrite process is described as follows:

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. Re-write 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 query-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 query-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), functional evaluation is necessary any time the extract(), extractvalue(), updatexml() operators are used. The existsNode() operator 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 query-re-write, and the conditions under which query re-write 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 applications' data and Web content is stored in a relational database or a file system, or a combination of both. XML is used mostly for transport and 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. See Figure 1-7. Oracle XML DB is effective at accommodating XML content. It provides enhanced native support for XML.

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

Description of the illustration adxdb006.gif

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 both structured, unstructured, and pseudo or semi-structured data, using a standard data model, and standard SQL and XML.

Oracle XML DB provides complete transparency and interchangeability between XML and SQL. You can perform both the following:

• XML operations on object-relational (such as table) data

• SQL operations on XML documents

This makes the database much more accessible to XML-shaped data content.

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 10, " 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 8, " Transforming and Validating XMLType Data".

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

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

• The hierarchical index over the repository. See Chapter 18, " 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.