XPath expressions can be used in SQL statements to decompose and match XML data stored in columns. The xpath_contains SQL predicate can be used either to test for an XML value matching a path expression or to extract one or more entities from the XML value. These values can then be used later in the query as contexts for other XPath expressions.

xpath_contains (xml_column, xp_expression[, query_variable]);

The first argument, xml_column is the name of the column on which to perform the XPath search. The second argument, xp_expression , takes an XPath expression.

The third argument is an optional query variable that gets bound to each result entity value of the xpath expression. If this variable is omitted the xpath_contains predicate will qualify the query by returning true for matches. In this case the result will only return one row per match. If the variable is present, the result set could contain multiple rows per result set row of the base table, one row for each match.

Consider the example:

select xt_file, t from xml_text
  where xpath_contains (xt_text, '//chapter/title[position () = 1]', t);

This SQL statement will select the first title child of any chapter entities in the XML documents in the xt_text column of the table xml_text . There can be several matching entities per row of xml_text. The result set will contain a row for each matching entity.

In XPath terms the path expression of xpath_contains is evaluated with the context node set to the root node of the XML tree represented by the value of the column that is the first argument of xpath_contains. This node is the only element of the context node set.

The XPATH expression can have a list of options in the beginning. The list of options is surrounded by square brackets. Options in the list are delimited by spaces. The most popular option is __quiet that allows to process a set of rows if not all stored documents are valid XMLs; if an error is signalled by the XML parser when it prepares a content document for the XPATH in question and the XPATH contains __quiet then the error is suppressed and the row is silently ignored as if XPATH found nothing. One can configure the DTD validator of the parser by placing its configuration parameters in the list of XPATH options.

The following example is almost identical to the previous one but it works even if not all values of xt_text are valid XMLs, and the resulting values of the 't' variable are standalone entities even if source documents in xt_text contain external generic entities.

select xt_file, t from xml_text
  where xpath_contains (xt_text, '[__quiet BuildStandalone=ENABLE]//chapter/title[position () = 1]', t);