Invalid XML values

For indexes using the data types DOUBLE, INTEGER, DECIMAL, DATE, and TIMESTAMP, an XML pattern value is converted to the index XML data type using the XQuery cast expression. XML values that do not have a valid lexical form for the target index XML data type are considered to be invalid XML values.

For example, ABC is an invalid XML value for the xs:double data type. How the index handles the invalid XML values depends whether the REJECT INVALID VALUES option or the IGNORE INVALID VALUES option is specified in the xmltype-clause of the CREATE INDEX statement.

REJECT INVALID VALUES

Specifies that all XML pattern values must be valid in the context of the lexical definition of the index XML data type. In addition the value must be in the range of the value space of the index XML data type. See the Related reference section, later, for links to details on the lexical definition and value space for each data type. For example, when you specify the REJECT INVALID VALUES clause, if you create an index of INTEGER type, XML pattern values such as 3.5, 3.0, 3e0, 'A123' and 'hello' will return an error (SQLSTATE 23525). XML data is not inserted or updated in the table if the index already exists (SQLSTATE 23525). If the index does not exist, the index is not created (SQLSTATE 23526).

For example, suppose the user creates the index EMPID, which indexes the numeric employee IDs as a DOUBLE data type. Numeric values like 31201 are indexed. However, if one of the documents uses the department ID value M55 as one of the employee ID attribute values by mistake, then the insert of the document fails with an error message because M55 is an invalid DOUBLE value.

CREATE INDEX EMPID ON DEPARTMENT(DEPTDOCS)
   GENERATE KEY USING XMLPATTERN '//@id' AS SQL DOUBLE 
   REJECT INVALID VALUES

IGNORE INVALID VALUES

Specifies that XML pattern values that are invalid lexical forms for the target index XML data type are ignored and that the corresponding values in the stored XML documents are not indexed by the CREATE INDEX statement. By default, invalid values are ignored. During insert and update operations, the invalid XML pattern values are not indexed, but XML documents are still inserted into the table. No error or warning is raised, because specifying these data types is not a constraint on the XML pattern values (XQuery expressions that search for the specific XML index data type will not consider these values).

The rules for what XML pattern values can be ignored are determined by the specified SQL data type.

• If the SQL data type is a character string data type, XML pattern values are never ignored since any sequence of characters is valid.
• If the SQL data type is a numeric data type, any XML pattern value that does not conform to the lexical format of the XML data type xs:double is ignored. If the XML pattern value does not conform to the more specific lexical formats of the XML data type corresponding to the numeric SQL data type of the index, an error is returned. For example, if the SQL data type is INTEGER, the XML pattern values of 3.5, 3.0, and 3e0 conform to the lexical format of xs:double but return an error (SQLSTATE 23525) because they do not conform to the lexical format of xs:int. XML pattern values such as 'A123' or 'hello' are ignored for the same index.
• If the SQL data type is a datetime data type, any XML pattern value that does not conform to the lexical format of the corresponding XML data type (xs:date or xs:dateTime) is ignored.

If an XML pattern value does conform to the appropriate lexical format, an error is returned if the value is outside the value space for the data type or exceeds the maximum length or precision and scale of the specified SQL data type. If the index does not exist, the index is not created (SQLSTATE 23526).

When invalid XML pattern values for the data type are ignored, the target index XML data type acts like a filter and is not a constraint since the user may have multiple indexes with different data types on the same XML column. For example, suppose the user creates two indexes on the same pattern but with different data types. The index ALLID uses the VARCHAR data type and indexes on all the IDs in the document (both department IDs and employee IDs). The index EMPID indexes only on the numeric employee IDs and uses the DOUBLE data type as a filter:

Using the explicit IGNORE INVALID VALUES option

CREATE INDEX ALLID ON DEPARTMENT(DEPTDOCS)
   GENERATE KEY USING XMLPATTERN '//@id' AS SQL VARCHAR(10) 
   IGNORE INVALID VALUES

CREATE INDEX EMPID ON DEPARTMENT(DEPTDOCS)
   GENERATE KEY USING XMLPATTERN '//@id' AS SQL DOUBLE
   IGNORE INVALID VALUES

Logically equivalent statements using the default

CREATE INDEX ALLID ON DEPARTMENT(DEPTDOCS)
   GENERATE KEY USING XMLPATTERN '//@id' AS SQL VARCHAR(10) 

CREATE INDEX EMPID ON DEPARTMENT(DEPTDOCS)
   GENERATE KEY USING XMLPATTERN '//@id' AS SQL DOUBLE

The department ID value M25 is a valid VARCHAR data type value and will be inserted into the index ALLID. However, M25 cannot be converted to the DOUBLE data type so that the value will not be inserted into EMPID and no error or warning is raised. The value is inserted for the document stored in the table.

Although the value M25 does not exist in the DOUBLE index EMPID, queries may still use the DOUBLE index to retrieve all the matching numeric values and no conversion errors will occur, because the document that contains M25 will not be accessed.

However, if the query does not use the DOUBLE index EMPID and scans the document using the //@id=25 predicate, then a conversion error will occur because the value M25 matches the pattern and still exists in the document but is not a numeric value.

Note that all values in the document are valid for the xs:string (SQL VARCHAR) data type.