Every XML pattern expression specified in the CREATE INDEX
statement must be associated with a data type. Four SQL data types
are supported: VARCHAR, DATE, TIMESTAMP, 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.
- 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.
- 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.