Data types associated with index XML pattern expressions

Every XML pattern expression specified in the CREATE INDEX statement must be associated with a data type. The following SQL data types are supported: VARCHAR, DATE, TIMESTAMP, INTEGER, DECIMAL, and DOUBLE.

You can choose to interpret the result of the expression as multiple data types. For example, the value 123 has a character representation but it can also be interpreted as the number 123. If you would like to index the path /company/emp/@id as both a character string and as a numeric value, then two indexes must be created, one for the VARCHAR data type and one for the DOUBLE data type. The values in the document are converted to the specified data type for each index.

The following example shows how to create two indexes with different data types on the same XML column deptdocs:

CREATE INDEX empindex1 on department(deptdocs)
          GENERATE KEY USING XMLPATTERN '/company/emp/@id' AS SQL VARCHAR(10)

CREATE INDEX empindex2 on department(deptdocs)
        GENERATE KEY USING XMLPATTERN '/company/emp/@id' AS SQL DOUBLE

Description of supported SQL data types:

VARCHAR(integer)

VARCHAR data will be stored in the index on an XML column in the UTF-8 code page. If the data type VARCHAR is used with the specified length integer (in bytes), the specified length is treated as a constraint. If documents are inserted into the table or exist in the table at the same time the index is created, then the document insertion or index creation will fail if there are nodes to be indexed with values longer than the specified length. If the insertion or creation succeeds, the index is guaranteed to store all character string values in their entirety and it can support both range scans and equality lookups. The length integer is a value in the range from 1 to the page size dependent maximum. See the CREATE INDEX statement for the list of maximum allowed lengths. XQuery semantics are used for string comparisons, where trailing blanks are significant. This differs from SQL semantics, where trailing blanks are insignificant during comparisons.

     CREATE INDEX empindex1 on department(deptdocs)
          GENERATE KEY USING XMLPATTERN '/company/emp/@id' AS SQL VARCHAR(50)

VARCHAR HASHED

VARCHAR HASHED may be specified to handle indexing of character strings with arbitrary lengths. If documents contain character strings to be indexed that exceed the maximum length integer allowed for the index based on the page size-dependent maximum, then you can specify VARCHAR HASHED instead. In this case, the system generates an 8 byte hash code over the entire string and there is no limit on the length of the indexed string. Range scans cannot be performed if you specify VARCHAR HASHED, since the index contains hash codes instead of the actual character data. Indexes using these hashed character strings may be used only for equality lookups. XQuery semantics are used for string equality comparisons, where trailing blanks are significant. This differs from SQL semantics, where trailing blanks are insignificant during comparisons. The hash on the string preserves XQuery semantics for equality, and not SQL semantics for equality.

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

DOUBLE

All numeric values will be converted and stored in the index as the DOUBLE data type. Unbounded decimal types and 64 bit integers may lose precision when they are stored as a DOUBLE. The values for the index SQL data type DOUBLE may include the special numeric values NaN, INF, -INF, +0, and -0, even though the SQL data type DOUBLE itself does not support these values.

INTEGER

All numeric values that conform to the XML Schema type xs:int will be converted and stored in the index as the INTEGER data type. Note that XML Schema defines multiple integer data types, including xs:int and xs:integer. The boundaries of xs:int correspond to the boundaries of the SQL type INTEGER, whereas xs:integer defines no boundaries. If a value is encountered that exceeds the boundaries of xs:int, an error is returned.

CREATE INDEX intidx on favorite_cds(cdinfo) 
   GENERATE KEYS USING XMLPATTERN '/favoritecds/cd/year' 
   AS SQL INTEGER 

The word INT can be used as a synonym for the SQL data type INTEGER.

DECIMAL(integer, integer)

All numeric values will be converted and stored in the index as the DECIMAL data type. The first integer is the precision of the number; that is, the total number of digits; it may range from 1 to 31. The second integer is the scale of the number; that is, the number of digits to the right of the decimal point; it may range from 0 to the precision of the number. If precision and scale are not specified, the default values of 5,0 are used.

CREATE INDEX decidx on favorite_cds(cdinfo) GENERATE KEYS USING XMLPATTERN 
   '//price' AS SQL DECIMAL(5,2)

The words DEC, NUMERIC, and NUM can be used as synonyms for DECIMAL.

DATE

DATE data type values will be normalized to UTC (Coordinated Universal Time) or Zulu time before being stored in the index. Note that the XML schema data type for DATE allows greater precision than the SQL data type. If an out-of-range value is encountered, an error is returned.

CREATE INDEX BirthdateIndex ON personnel(xmlDoc)
   GENERATE KEY USING XMLPATTERN '/Person/Confidential/Birthdate' AS SQL DATE

TIMESTAMP

TIMESTAMP data type values will be normalized to UTC (Coordinated Universal Time) or Zulu time before being stored in the index. Note that the XML schema data type for timestamps allows greater precision than the SQL data type. If an out-of range value is encountered, an error is returned.

CREATE INDEX LastLogonIndex ON machines(xmlDoc)
   GENERATE KEY USING XMLPATTERN '/Machine/Employee/LastLogon' AS SQL TIMESTAMP