Sample queries against indexes over XML data

Sample queries that can use an index over XML data

Queries with a wide variety of different predicates can exploit an index over XML data. Some examples of XQuery predicates matched with indexes they can use are shown in this section. Queries are followed by matching indexes.

Example 1. Issue a query for equality: Find the employee with ID 42366:

XQUERY for $i in db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')/company/emp[@id='42366']
       return $i

CREATE INDEX empindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '/company/emp/@id' AS SQL VARCHAR(5)

Example 2. Query for a range: Find employees with a salary greater than 35000:

XQUERY 
   for $i in db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')/company/emp[@salary > 35000]
   return $i

CREATE INDEX empindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '//@salary' AS SQL DECIMAL(10,2)

Example 3. Issue a query which includes a disjunction (OR): Find employees that are in the Finance department or in the Marketing department:

XQUERY 
   for $i in 
   db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')/company/emp[dept/text()='Finance' 
   or dept/text()='Marketing']
   return $i

CREATE INDEX empindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '/company/emp/dept/text()' AS SQL  
      VARCHAR(30)

Example 4. Different queries can be satisfied by the same index:

Find the employee with the ID 31201:

XQUERY for $i in db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')/company/emp[@id='31201']
        return $i

Find departments with the ID K55

XQUERY for $i in db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')/company/emp/dept[@id='K55']
        return $i

CREATE INDEX empindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '//@id' AS SQL VARCHAR(25)

Example 5. Query predicates can contain paths: Find employees with last name Murphy who are in the Sales department:

XQUERY 
   for $i in db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')/company/emp[name/last='Murphy'
     and dept/text()='Sales']
   return $i

CREATE INDEX empindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '/company/emp/name/last' AS SQL 
      VARCHAR(100)

CREATE INDEX deptindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '/company/emp/dept/text()' AS SQL 
      VARCHAR(30)

Example 6. Exercise hierarchical containment during queries: A query can use indexes to perform ANDing at different levels in the document hierarchy. A query can also use the indexes to determine what children nodes belong to the same ancestor to do appropriate filtering.

Find companies with employees that have a salary equal to 60000 and find companies with female employees. In the sample XML fragments of the XML data indexing overview topic (see the Related concepts section), both Company1 and Company2 would qualify.

XQUERY for $i in 
   db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')/company[emp/@salary=60000 and 
   emp/@gender='Female']
   return $i

Find employees who have a salary equal to 60000 and who are female. Only Laura Brown from Company1 would qualify.

XQUERY for $i in 
   db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')/company/emp[@salary=60000 
   and @gender='Female']
   return $i

CREATE INDEX empindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '/company/emp/@salary' AS DECIMAL(10,2)

CREATE INDEX genderindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '/company/emp/@gender' AS SQL 
      VARCHAR(10)

Example 7. A query can use the descendant-or-self axis (//) and make use of indexes, provided that the query predicate is at least as restrictive as, or more restrictive than, the index pattern.

Find employees with department ID K55:

XQUERY 
   for $i in 
   db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')/company//emp[.//dept//@id='K55' ]
   return $i

CREATE INDEX empindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '//emp//@id' AS SQL VARCHAR(25)

Example 8. For multidimensional clustering (MDC) tables, a query can use MDC block indexes and indexes over XML data. Assume an MDC table with an XML column is created and uses the WORKDEPT as a dimension.

CREATE TABLE employee (empno char(6), workdept char(3), doc xml) 
    ORGANIZE BY DIMENSIONS (workdept)

Assume that there is an index over XML data on the column DOC as defined with the following CREATE INDEX statement:

CREATE INDEX hdate on employee(doc) 
      GENERATE KEY USING XMLPATTERN '//hirdate'AS SQL DATE COLLECT STATISTICS

The following query can use the block index on WORKDEPT and the index over XML data (hdate) on DOC in its access plan:

SELECT COUNT (*) FROM y.employee y 
      WHERE workdept='A00' 
      AND XMLEXISTS('$p/employee[hiredata > "1964-01-01"] 
      PASSING y.doc as "p")

Sample queries that cannot use an index over XML data

There are some conditions when a query cannot use an index over XML data. Some examples of XQuery predicates that cannot make use of their intended indexes as shown are listed in this section.

Example 1. The data type requested by the query must match the indexed data type, before the query can use the index. In this example, the query requests the employee ID as a string, but the ID is indexed as a number:

XQUERY for $i in db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')/company/emp[@id='31664']
        return $i

CREATE INDEX empindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '/company/emp/@id' AS SQL DOUBLE

Example 2. The XML pattern expression used to create the index may be more restrictive than the query predicate. In this example, the query cannot use the index, because the query retrieves both department ID and employee IDs but the index contains only employee IDs:

XQUERY for $i in db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')//@id
        return $i

CREATE INDEX empindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '/company/emp/@id' AS SQL VARCHAR(5)

The following query retrieves the employees who have the employee ID 31201 or the department ID K55. Since the ID may either be an employee ID or a department ID, but the index contains only department IDs, the index cannot be used as created.

XQUERY 
   for $i in 
   db2-fn:xmlcolumn('COMPANY.COMPANYDOCS')//emp[.//@id='31201' or .//@id='K55']
   return $i

CREATE INDEX empindex on company(companydocs)
      GENERATE KEY USING XMLPATTERN '//dept//@id' AS SQL VARCHAR(5)