XMLType is an abstract SQL data type that provides different storage and indexing models to best fit your XML data and your use of it. Because it is an abstract data type, your applications and database queries gain in flexibility: the same interface is available for all XMLType operations.

Different applications use XML data in different ways. Sometimes it is constructed from relational data sources, so it is relatively structured. Sometimes it is used for extraction, transformation, and loading (ETL) operations, in which case it is also quite structured. Sometimes it is used for free-form documents (unstructured or semi-structured) such as books and articles.

Retrieval approaches can also be different for different kinds of data. Data-centric use cases often involve a fixed set of queries, whereas document-centric use cases often involve arbitrary (ad-hoc) queries.

Because there is a broad spectrum of XML usage, there is no one-size-fits-all storage model that offers optimal performance and flexibility for every use case. Oracle XML DB offers two storage models for XMLType, and several indexing methods appropriate to these different storage models. You can tailor performance and functionality to best fit the kind of XML data you have and the ways you use it.

Therefore, one key decision to make is which XMLType storage model to use for which XML data. This chapter helps you choose the best storage option for a given use case.

XMLType tables and columns can be stored in the following ways:

• Binary XML storage – This is also referred to as post-parse persistence. It is the default storage model for Oracle XML DB. It is a post-parse, binary format designed specifically for XML data. Binary XML is compact and XML schema-aware. The biggest advantage of Binary XML storage is flexibility: you can use it for XML schema-based documents or for documents that are not based on an XML schema. You can use it with an XML schema that allows for high data variability or that evolves considerably or unexpectedly. This storage model also provides efficient partial updating and streamable query evaluation.

• Object-relational storage – This is also referred to as structured storage and object-based persistence. This storage model represents an entity-relationship (ER) decomposition of the XML data. It provides the best performance for highly structured data with a known and more or less fixed set of queries. Query performance matches that of relational data, and updates can be performed in place.

Oracle XML DB supports the following kinds of indexes on XMLType data.

• B-tree functional indexes on object-relational storage

• XML search index on binary XML storage

• XMLIndex with structured and unstructured components on binary XML storage

• B-tree indexes on the secondary tables created automatically for XMLIndex (both structured and unstructured components) on binary XML storage

Different use cases call for different combinations of XMLType storage model and indexes.

When choosing an XMLType storage model, consider the nature of your XML data and the ways you use it. There is a spectrum of use cases, ranging from most data-centric to most document-centric.

This is illustrated in Figure 16-1 , which shows the most data-centric cases at the left and the most document-centric cases at the right.

Data-centric data is highly structured, with relatively static and predictable structure, and your applications take advantage of this structure. The data conforms to an XML schema.

Document-centric data can be divided into two cases:

• The data is generally without structure or is of variable structure. This includes the case of documents that have both structured and unstructured parts. Document structure can vary over time (evolution), and the content can be mixed (semi-structured), with many elements containing both text nodes and child elements. Many XML elements can be absent or can appear in different orders. Documents might or might not conform to an XML schema.

• The data is relatively structured, but your applications do not take advantage of that structure: they treat the data as if it were without structure.

Recommendations are provided for application use cases that correspond to common use cases for XML data stored as XMLType.

If your use case is not a common one, so that it is not covered here, then refer to the rest of this chapter for information about special cases.

• XMLType Use Case: No XML Fragment Updating or Querying
  In this use case there is no requirement to update or query fragments of XML data that is stored in the database.
• XMLType Use Case: Data Integration from Diverse Sources with Different XML Schemas
  If your XML data comes from multiple data sources that use different XML schemas then use binary XML storage.
• XMLType Use Case: Staged XML Data for ETL
  In this use case, data is extracted from outside sources, transformed to fit operational needs (typically relational), and then loaded into the database: extract, transform, load (ETL). In particular, transformation distinguishes this use case.
• XMLType Use Case: Semi-Structured XML Data
  In this use case, either your XML data is of variable form or large portions of it are not well defined. There might not be an associated XML schema, or the XML schema might allow for high data variability or evolve considerably or in unexpected ways.
• XMLType Use Case: Business Intelligence Queries
  To enable business-intelligence (BI) queries over XML data, you can use SQL/XML function XMLTable to project values contained in the data as columns of a virtual table. Then use analytic-function windows, together with SQL ORDER BYand GROUP BY, to operate on columns of the virtual table.
• XMLType Use Case: XML Queries Involving Full-Text Search
  If your application needs to perform full-text searches on XML data then use binary XML storage and create XML search indexes that correspond to your queries.

For most use cases, Oracle recommends that you use binary XML storage of XMLType. Object-relational storage is appropriate in special cases.

Object-relational storage is not appropriate unless all of the following are true:

• You have an XML schema that rigorously specifies the detailed data format of all XML documents that you intend to store in a given XMLType column or table. Your applications are data-centric.

• You do not expect your XML schema to evolve frequently in ways that do not allow in-place schema evolution.

• Your data is not especially sparse (does not include many elements that are empty or missing).

• You do not necessarily insert and select whole XML documents at a time. Partial updates and selections are common.

• You do not need document fidelity (DOM fidelity is sufficient).

Table 16-1 provides more detail about this. The guidelines it presents for choosing an XMLType storage model are not independent: follow them in the order presented, row by row, until a requirement in column If... is satisfied.

For XMLType data stored object-relationally, create B-tree and bitmap indexes just as you would for relational data. Use XMLIndex indexing with XMLType data that is stored as binary XML.

For general indexing of document-centric XML data, use XMLIndex with an unstructured component. This is appropriate for queries that are ad hoc (arbitrary).

For data that contains predictable, fixed parts that you query frequently, use XMLIndex with structured components for those parts. An example of this use case is a specification that is generally free-form but that has fixed fields for the author, date, and title.

To handle islands of structure within generally unstructured content, create an XMLIndex index that has both structured and unstructured components. A use case where you might use both components would be to support queries that extract an XML fragment from a document whenever some structured data is present. The structured component of the index would be used for a query WHERE clause condition that checks for the structured data. The unstructured component would be used for the fragment extraction.

Table 16-2 provides simple guidelines for indexing XMLType data that is stored as binary XML. These guidelines are independent: you can use a combination of indexing approaches if their If... conditions are satisfied.

Each XMLType storage model has particular advantages and disadvantages.

Table 16-3 summarizes the advantages and disadvantages of each XMLType storage model. Symbols + and – provide a rough indication of strength and weakness, respectively.