Example of XML index usage by queries with XMLTABLE

If the FROM clause of a query contains an XMLTABLE function with a row-XQuery-expression, and Db2 transforms the query to contain an XMLEXISTS predicate, after transformation, the query can use an XML index.

The original query might have an XMLTABLE function with a row-XQuery-expression, or the query might be the result of transformation of an SQL predicate to an XPath predicate in a row-XQuery-expression.

Suppose that the CUSTOMER table contains this document in the INFO column:

<customerinfo xmlns="http://posample.org" Cid="1010">
  <name>Elaine Decker</name>
  <addr zip="95999">
    <street>100 Oak</street>
    <city>Mountain Valley</city>
    <state>CA</state>
    <country>USA</country>
  </addr>
  <phone type="work">408-555-2310</phone>
</customerinfo>

Example: The following query uses the XMLTABLE function to retrieve the zip, street, city, and state elements from customerinfo documents as columns in a result table.

SELECT X.* 
 FROM CUSTOMER,
  XMLTABLE (XMLNAMESPACES(DEFAULT 'http://posample.org'),
   '$x/customerinfo/address[@zip=95999]'
   PASSING INFO as "x"
	 COLUMNS 
	 	ZIP INT PATH '@zip',
		STREET VARCHAR(50) PATH 'street',
		CITY	VARCHAR(30) PATH 'city',
   STATE VARCHAR(2) PATH 'state') AS X

The original query cannot use an XML index. However, the original query has a row-XQuery-expression that Db2 can transform into an XMLEXISTS predicate. After transformation, the query looks like this:

SELECT X.* 
 FROM CUSTOMER,
  XMLTABLE (XMLNAMESPACES(DEFAULT 'http://posample.org'),
   '$x/customerinfo/address[@zip=95999]'
   PASSING INFO as "x"
	 COLUMNS 
	 	ZIP INT PATH '@zip',
		STREET VARCHAR(50) PATH 'street',
		CITY	VARCHAR(30) PATH 'city',
   STATE VARCHAR(2) PATH 'state') AS X
 WHERE XMLEXISTS('$x/customerinfo/address[@zip=95999]'
 PASSING INFO AS "x")

The transformed query can use this index:

CREATE INDEX ORDER_ZIP_NUM ON CUSTOMER(INFO)
 GENERATE KEYS USING XMLPATTERN
 'declare default element namespace "http://posample.org/";
 /customerinfo/address/@zip'
 AS SQL DECFLOAT

The XML index needs to be defined on the INFO column of the CUSTOMER table because the XMLEXISTS predicate in the transformed query uses the INFO column of the CUSTOMER table.

Example: The following query retrieves the same information as the query in the previous example, but an SQL predicate determines which rows are returned.

SELECT X.* 
 FROM CUSTOMER,
  XMLTABLE (XMLNAMESPACES(DEFAULT 'http://posample.org'),
   '$x/customerinfo/address'
   PASSING INFO AS “x”
   COLUMNS 
	 	ZIP INT PATH '@zip',
		STREET VARCHAR(50) PATH 'street',
		CITY	VARCHAR(30) PATH 'city',
   STATE VARCHAR(2) PATH 'state') AS X
	WHERE X.ZIP = 95999

Db2 can transform the query so that the SQL predicate becomes an XPath predicate in the row-XQuery-expression of the XMLTABLE function. The transformed query looks like this:

SELECT X.* 
 FROM CUSTOMER,
  XMLTABLE (XMLNAMESPACES(DEFAULT 'http://posample.org'),
   '$x/customerinfo/address/[@zip=95999]'
   PASSING INFO AS “x”
   COLUMNS 
	 	ZIP INT PATH '@zip',
		STREET VARCHAR(50) PATH 'street',
		CITY	VARCHAR(30) PATH 'city',
   STATE VARCHAR(2) PATH 'state') AS X

Db2 can then transform the query again so that the row-XQuery-expression becomes an XMLEXISTS predicate. After transformation, the query looks like this:

SELECT X.* 
 FROM CUSTOMER,
  XMLTABLE (XMLNAMESPACES(DEFAULT 'http://posample.org'),
   '$x/customerinfo/address[@zip=95999]'
   PASSING INFO as "x"
	 COLUMNS 
	 	ZIP INT PATH '@zip',
		STREET VARCHAR(50) PATH 'street',
		CITY	VARCHAR(30) PATH 'city',
   STATE VARCHAR(2) PATH 'state') AS X
 WHERE XMLEXISTS('$x/customerinfo/address[@zip=95999]'
 PASSING INFO AS "x")

The transformed query can use this index:

CREATE INDEX ORDER_ZIP_NUM ON CUSTOMER(INFO)
 GENERATE KEYS USING XMLPATTERN
 'declare default element namespace "http://posample.org/";
 /customerinfo/address/@zip'
 AS SQL DECFLOAT