Data type of literals

The data types of literals need to match the data type of the index in order for the query to be able to make use of the index.

Matching data types of literals

The following query retrieves company information for employees with the ID 31201.

SELECT companydocs FROM companyinfo 
   WHERE XMLEXISTS('$x/company/emp[@id="31201"]' 
   PASSING companydocs AS "x")

To be compatible, the index over XML data needs to include the employee ID attribute nodes among the indexed nodes, and to store values in the index as a VARCHAR type.

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

If a similar index were defined AS SQL DOUBLE it could not be used by the query, since the query predicate includes a string comparison. The double quotation marks used in the predicate @id="31201" make it a string comparison, which can be evaluated only with a string index (VARCHAR), not with a numeric index (DOUBLE).

To highlight the difference between numeric predicates and string predicates, consider the following inequality predicates:

@id > 3 
@id > "3"

The numeric predicate @id > 3 is different from the string predicate @id > "3". The numeric predicate @id > 3 would be fulfilled by an @id value of 10, but the string predicate @id > "3" would not be, because in a string comparison "3" is greater than "10".