Understanding XML Indexes in Relational Databases

Storing Extensible Markup Language (XML) data in relational database management systems (RDBMS) allows developers to handle semi-structured data alongside traditional tabular formats. However, querying raw XML stored as large objects is resource-intensive because the database must parse the entire document at runtime. This article explains what XML indexes are and explores how specific secondary indexes—specifically Path and Value indexes—accelerate query execution by bypassing costly document parsing and enabling direct B-tree lookups.

What Are XML Indexes?

In relational databases like Microsoft SQL Server, Oracle, and IBM Db2, XML data is typically stored in a dedicated XML or LOB (Large Object) data type. By default, evaluating an XPath or XQuery expression requires the database engine to perform a full scan of the column, deserialize the XML binary/text, and traverse the document tree in memory for every row.

An XML index addresses this performance bottleneck by “shredding” the XML data. When an XML index is created, the database parses the XML documents into an internal relational node table. This hidden table stores individual rows for every node, attribute, element name, path, and value contained in the XML documents, structured within standard B-tree index pages.

The Primary XML Index

Before creating specialized secondary indexes, a table must have a Primary XML Index. The primary XML index indexes all nodes (elements, attributes, values, namespaces) of the XML instances in the column.

While the primary index significantly speeds up basic document traversal compared to an unindexed column, queries can still be slow if they must scan the large primary index table. To solve this, database engines use specialized secondary XML indexes: Path, Value, and Property indexes.

Path Indexes

A Path secondary XML index is optimized for queries based on structural path expressions.

How Path Indexes Work

A Path index is built primarily on the path identifier and the node value columns of the primary XML index. It enables the query engine to quickly look up rows based on the specific hierarchy or path of an XML node (for example, /BookStore/Book/Title).

How They Accelerate Queries

Path indexes accelerate queries when: * Path expressions are known: The query specifies explicit paths to locate nodes (e.g., using functions like .exist() or .nodes() with XPath). * Top-down navigation occurs: The engine navigates from the root node down through specified child elements. * Wildcards are avoided at the beginning of paths: Instead of scanning every node in the primary index, the query optimizer performs a seek on the known path ID in the Path index, immediately retrieving matching rows.

Value Indexes

A Value secondary XML index is designed for queries where the search criteria focus on node values rather than precise structural paths.

How Value Indexes Work

A Value index organizes the shredded XML data with the node value as the leading key, followed by the path identifier. This reverses the structural priority used in a Path index.

How They Accelerate Queries

Value indexes accelerate queries when: * Paths contain wildcards: Queries use descendant-or-self axes (e.g., //Title or //price) where the full, exact path to the element is unknown or variable. * Search criteria target specific values: The query looks for rows where an attribute or element matches a specific scalar value (e.g., [text() = 'Database Systems']), regardless of where it appears in the document. * Point lookups on values occur: The database performs a rapid index seek on the value itself to identify matching rows without traversing parent-child node relationships.

Summary of Query Optimization

By eliminating runtime XML parsing and enabling standard B-tree seek operations, Path and Value indexes allow relational databases to execute complex XML queries at speeds comparable to native relational queries.