XMLVALIDATE scalar function
The XMLVALIDATE function returns a copy of the input XML value augmented with information obtained from XML schema validation, including default values.
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.
- 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.
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
- Example 1: 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.
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.<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>
Based on these assumptions, the input XML value will be validated according to that XML schema.
- Example 2: 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.
- Example 3: 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'.
- Example 4: 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.