Using Oracle Database for Storing, Modifying, and Retrieving XML Data

Using Oracle Database for Storing, Modifying, and Retrieving XML Data

With Oracle XML DB, you have various XML storage and XML processing options allowing you to achieve the required level of performance and scalability. One of the most interesting things about Oracle XML DB is that it allows you to perform SQL operations on XML data as well as XML operations on relational data, thus bridging the gap between the SQL and XML worlds.

Database Storage Options for XML Data in Oracle Database

When storing XML in Oracle database, you can choose between several storage options. The general XML storage options available in Oracle database are outlined in the following table:

The following figure will help you understand better the ideas behind the storage methods outlined in the table.

As you can see from the previous figure, when using CLOB storage for XMLType data, an XML document is stored in an XMLType column or table as a complete text document. Hence, updating an XML document stored as an XMLType CLOB is a very expensive operation that involves DOM parsing the document, performing the update operation on the DOM representation of the document, serializing the updated document back into text, and finally replacing it. Storing XML in CLOBs can be efficient when, for example, you're dealing with large XML documents, which are not updated frequently, and which you are going to retrieve as a whole.

In the preceding sample, you use XMLType CLOB storage for the employees XSL stylesheet, storing it in the stylesheet XMLType column of the XSLTstylesheets table, as discussed in the Storing XML Data in the Database section earlier. The XMLType CLOB storage is the best choice in that example because the only operation you are supposed to perform on the employees XSL stylesheet frequently is retrieving it as a whole when it comes to transforming an employees XML document into HTML.

In contrast, native XMLType storage, also known as structured or shredded storage, can be very efficient when you perform update operations on XML data frequently. This type of storage is created automatically by Oracle when registering an XML schema against the database.

NOTE: XML schemas are discussed in the Using XML Schemas subsection later in this section.

Based on the information in an XML schema, Oracle creates a set of SQL object types and XMLType tables to be used for managing and storing the contents of XML documents conforming to that XML schema. Before storing, a document is broken up, and its contents are stored as an instance of the appropriate object type generated during the XML schema registration process.

This approach makes it possible for Oracle XML DB to rewrite SQL statements issued to access or manipulate XML schema-based XML data to purely relational SQL statements, thus allowing for efficient processing of XML data.

XMLType views can be useful when you need to wrap existing relational data in XML format without physically migrating it into XML. In fact, you can define an XMLType view not only on relational tables and views but also on XMLType ones. For examples on using XMLType views, see the Using XMLType Views section later in this chapter.

As you can see, all the XML storage options presented in the table are based on XMLType. The following section discusses this native Oracle datatype in detail