DB2 Scalar functions - XMLVALIDATE

XMLVALIDATE

The XMLVALIDATE function returns a copy of the input XML value augmented with information obtained from XML schema validation, including default values.

XML-validate-according-to-clause

XML-valid-element-clause

The schema is SYSIBM.
The function name cannot be specified as a qualified name.

DOCUMENT

Specifies that the XML value resulting from XML-expression must be a well-formed XML document that conforms to XML Version 1.0 (SQLSTATE 2200M).

XML-expression

An expression that returns a value of data type XML.
If XML-expression is an XML host variable or an implicitly or explicitly typed parameter marker, the function performs a validating parse that strips ignorable whitespace and the CURRENT IMPLICIT XMLPARSE OPTION setting is not considered.

XML-validate-according-to-clause

Specifies the information that is to be used when validating the input XML value.

ACCORDING TO XMLSCHEMA

Indicates that the XML schema information for validation is explicitly specified.
If this clause is not included, the XML schema information must be provided in the content of the XML-expression value.

ID XML-schema-name: Specifies an SQL identifier for the XML schema that is to be used for validation.
The name, including the implicit or explicit SQL schema qualifier, must uniquely identify an existing XML schema in the XML schema repository at the current server.
If no XML schema by this name exists in the implicitly or explicitly specified SQL schema, an error is returned (SQLSTATE 42704).
URI XML-uri1: Specifies the target namespace URI of the XML schema that is to be used for validation.
The value of XML-uri1 specifies a URI as a character string constant that is not empty.
The URI must be the target namespace of a registered XML schema (SQLSTATE 4274A) and, if no LOCATION clause is specified, it must uniquely identify the registered XML schema (SQLSTATE 4274B).
NO NAMESPACE: Specifies that the XML schema for validation has no target namespace.
The target namespace URI is equivalent to an empty character string that cannot be specified as an explicit target namespace URI.
LOCATION XML-uri2: Specifies the XML schema location URI of the XML schema that is to be used for validation.
The value of XML-uri2 specifies a URI as a character string constant that is not empty.
The XML schema location URI, combined with the target namespace URI, must identify a registered XML schema (SQLSTATE 4274A), and there must be only one such XML schema registered (SQLSTATE 4274B).

XML-valid-element-clause

Specifies that the XML value in XML-expression must have the specified element name as the root element of the XML document.

NAMESPACE XML-uri3 or NO NAMESPACE

Specifies the target namespace for the element that is to be validated.
If neither clause is specified, the specified element is assumed to be in the same namespace as the target namespace of the registered XML schema that is to be used for validation.

NAMESPACE XML-uri3: Specifies the namespace URI for the element that is to be validated.
The value of XML-uri3 specifies a URI as a character string constant that is not empty.
This can be used when the registered XML schema that is to be used for validation has more than one namespace.
NO NAMESPACE: Specifies that the element for validation has no target namespace.
The target namespace URI is equivalent to an empty character string which cannot be specified as an explicit target namespace URI.
ELEMENT xml-element-name: Specifies the name of a global element in the XML schema that is to be used for validation.
The specified element, with implicit or explicit namespace, must match the root element of the value of XML-expression (SQLSTATE 22535 or 22536).

The data type of the result is XML.
If the value of XML-expression can be null, the result can be null; if the value of XML-expression is null, the result is the null value.

The XML validation process is performed on a serialized XML value.
Because XMLVALIDATE is invoked with an argument of type XML, this value is automatically serialized before validation processing with the follow two exceptions.

If the argument to XMLVALIDATE is an XML host variable or an implicitly or explicitly typed parameter marker, then a validating parse operation is performed on the input value (no implicit non-validating parse is performed and CURRENT IMPLICIT XMLPARSE OPTION setting is not considered).
If the argument to XMLVALIDATE is an XMLPARSE invocation using the option PRESERVE WHITESPACE, then the XML parsing and XML validation of the document may be combined into a single validating parse operation.

If an XML value has previously been validated, the annotated type information from the previous validation is removed by the serialization process.
However, any default values and entity expansions from the previous validation remain unchanged.
If validation is successful, all ignorable whitespace characters are stripped from the result.

To validate a document whose root element does not have a namespace, an xsi:noNamespaceSchemaLocation attribute must be present on the root element.

Notes

Determining the XML schema:
The XML schema can be either specified explicitly with the ACCORDING TO XMLSCHEMA clause as part of the XMLVALIDATE invocation, or determined implicitly from the XML schema location information in the input XML value.
The explicit or implicit XML schema information must identify an XML schema registered in the XML schema repository (SQLSTATE 42704, 4274A, or 22532), and there must be only one such registered XML schema (SQLSTATE 4274B or 22533).
If an XML schema for validation is explicitly specified with the ACCORDING TO XMLSCHEMA clause, the schema location information specified in the input XML value is ignored.

If the XML schema information is not specified with the ACCORDING TO XMLSCHEMA clause, the input XML value must contain XML schema location information (SQLSTATE 2200M).
The schema location information in the input XML value, a namespace name, and a schema location specifies the XML schema document in the XML schema repository used for validation.
XML schema authorization:
The XML schema used for validation must be registered in the XML schema repository before use.
The privileges held by the authorization ID of the statement must include at least one of the following authorities:
- USAGE privilege on the XML schema that is to be used during validation
- DBADM authority
Using a maxOccurs attribute value that is greater than 5000 in XML schemas:
In Db2® Version 9.7 Fix Pack 1 and later, if an XML schema that is registered in the XSR uses the maxOccurs attribute where the value is greater than 5000, the maxOccurs attribute value is treated as if you specified "unbounded". Because document elements that have a maxOccurs attribute value that is greater than 5000 are processed as if you specified "unbounded", an XML document might pass validation when you use the XMLVALIDATE function even if the number of occurrences of an element exceeds the maximum according to the XML schema that you used to validate the document.
If you use an XML schema that defines an element that has a maxOccurs attribute value that is greater than 5000 and you want to reject XML documents that have a maxOccurs attribute value greater than 5000, you can define a trigger or procedure to check for that condition.
In the trigger or procedure, use an XPath expression to count the number of occurrences of the element and return an error if the number of elements exceeds the maxOccurs attribute value.

For example, the following trigger ensures that a document never has more than 6500 phone elements:
```
   CREATE TRIGGER CUST_INSERT
     AFTER INSERT ON CUSTOMER
     REFERENCING NEW AS NEWROW
     FOR EACH ROW MODE DB2SQL
   BEGIN ATOMIC
    SELECT CASE WHEN X <= 6500 THEN 'OK - Do Nothing'
           ELSE RAISE_ERROR('75000', 'TooManyPhones')  END

    FROM (
         SELECT XMLCAST(XMLQUERY('$INFO/customerinfo/count(phone)') AS INTEGER) AS X
         FROM   CUSTOMER
         WHERE  CUSTOMER.CID = NEWROW.CID );
   END 
```

Examples

Validate using the XML schema identified by the XML schema hint in the XML instance document.
```
   INSERT INTO T1(XMLCOL)
   VALUES (XMLVALIDATE(?))
```
Assume that the input parameter marker is bound to an XML value that contains the XML schema information.
```
   <po:order 
      xmlns:po="http://my.world.com"
      xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
      xsi:schemaLocation="http://my.world.com http://my.world.com/world.xsd" >
   ...
   </po:order>
```
Further, assume that the XML schema that is associated with the target namespace "http://my.world.com" and by schemaLocation hint "http://my.world.com/world.xsd" is found in the XML schema repository.

Based on these assumptions, the input XML value will be validated according to that XML schema.
Validate using the XML schema identified by the SQL name PODOCS.WORLDPO.
```
   INSERT INTO T1(XMLCOL)
   VALUES (
       XMLVALIDATE(
         ? ACCORDING TO XMLSCHEMA ID PODOCS.WORLDPO
       )
     )
```
Assuming that the XML schema that is associated with SQL name FOO.WORLDPO is found in the XML repository, the input XML value will be validated according to that XML schema.
Validate a specified element of the XML value.
```
   INSERT INTO T1(XMLCOL)
     VALUES (
       XMLVALIDATE(
         ? ACCORDING TO XMLSCHEMA ID FOO.WORLDPO
         NAMESPACE 'http://my.world.com/Mary'
         ELEMENT "po"
       )
     )
```
Assuming that the XML schema that is associated with SQL name FOO.WORLDPO is found in the XML repository, the XML schema will be validated against the element "po", whose namespace is 'http://my.world.com/Mary'.
XML schema is identified by target namespace and schema location.
```
   INSERT INTO T1(XMLCOL)
   VALUES (
       XMLVALIDATE(
         ? ACCORDING TO XMLSCHEMA URI 'http://my.world.com'
         LOCATION 'http://my.world.com/world.xsd'
       )
     )
```
Assuming that an XML schema associated with the target namespace "http://my.world.com" and by schemaLocation hint "http://my.world.com/world.xsd" is found in the XML schema repository, the input XML value will be validated according to that schema.

Notes

Examples

Related reference