A predicate that evaluates the XML query expression, which can reference the XML document parameter, and returns a Boolean result. Similar to a SQL like predicate.
xmltest_predicate ::= xml_query_expression [not] xmltest xml_data [option option_string] xml_data ::= xml_data_expression | (xml_data_expression) xml_query_expression::= basic_string_expression xml_data_expression ::= general_string_expression option_string ::= basic_string_expression
For information on processing I18N data, see Chapter 6, “XML Support for I18N.”
A basic_string_expression is a sql_query_expression whose datatype is character, varchar, unichar, univarchar, or java.lang.String.
A general_string_expression is a sql_query_expression whose datatype is character, varchar, unichar, univarchar, text, unitext, or java.lang.String.
An xmltest predicate can be used in SQL language wherever a SQL predicate is allowed.
An xmltest call specifying that:
X not xmltest Y options Z
is equivalent to:
not X xmltest Y options Z
If the xml_query_expression or xml_data_expression of xmltest() is null, then the result of xmltest() is unknown.
If the value of the xml_query_expression, or the document argument of xmlextract() is null, the result of xmlextract() is null.
The value of the xml_data_expression parameter is the runtime context for execution of the XPath expression.
xmltest() evaluates to boolean true or false, as follows:
The xml_query_expression of xmltest() is an XPath expression whose result is empty (not empty), then xmltest() returns false (true).
If the xml_query_expression of xmltest() is an XPath expression whose result is a Boolean false (true), then xmltest() returns false (true).
If the XPath expression is invalid, xmltest raises an exception.
See Chapter 3, “XML Language and XML Query Language,” for the following topics:
Restrictions on external URI references, XML namespaces, and XML schemas.
Treatment of predefined entities and their corresponding characters: & (&), < (<), > (>), "e; (“), and ' (’). Be careful to include the semicolon as part of the entity.
Treatment of whitespace.
Treatment of empty elements.
The general format of the option_string is described in “option_strings: general format”.
The option supported for the xmltest predicate is xmlerror = {exception | null}.
The message alternative, which is supported for xmlextract and xmlparse, is not valid for xmltest. See the Exceptions section.
If the value of the xml_data_expression is not valid XML, or is an all blank or empty string:
If the explicit or default option specifies xmlerror=exception, an exception is raised.
If the explicit or default options specifies xmlerror=null a null value is returned.
If you specify xmlerror=message, a null value is returned.
These examples use the sample_docs table described in Appendix A, “The sample_docs Example Table.”.
This example selects the name_doc of each row whose text_doc contains a row/city element equal to “Boston”.
select name_doc from sample_docs where '//row[city="Boston"]' xmltest text_doc name_doc ------------------------ publishers (1 row affected)
In the following example the xmltest predicate returns false/true, for a Boolean false/true result and for an empty/not-empty result.
-- A boolean true is 'true': select case when '/a="A"' xmltest '<a>A</a>' then 'true' else 'false' end2> ----- true -- A boolean false is 'false' select case when '/a="B"' xmltest '<a>A</a>' then 'true' else 'false' end ----- false -- A non-empty result is 'true' select case when '/a' xmltest '<a>A</a>' then 'true' else 'false' end ----- true -- An empty result is 'false' select case when '/b' xmltest '<a>A</a>' then 'true' else 'false' end
----- false -- An empty result is 'false' (second example) select case when '/b="A"' xmltest '<a>A</a>' then 'true' else 'false' end ----- false
To illustrate the xmlerror options, the following command inserts an invalid document into the sample_docs table:
insert into sample_docs (name_doc, text_doc)
values ('invalid doc', '<a>unclosed element<a>)
(1 row affected)
In the following examples, the xmlerror options determine the treatment of invalid XML documents by the xmltest predicate.
If xmlerror=exception (the default result), an exception is raised, and global variable @@error contains error message 14702.
select name_doc from sample_docs where '//price<10/*' xmltest text_doc option 'xmlerror=exception' Msg 14702, Level 16, State 0: Line 2: XMLPARSE(): XML parser fatal error <<The input ended before all started tags were ended. Last tag started was 'a'>> at line 1, offset 23.
To display the contents of @@error, enter:
select @@error ------------ 14702 (1 row affected)
If xmlerror=null or xmlerror=message, a null (unknown) value is returned, and global variable @@error contains error message 14701.
select name_doc from sample_docs where '//price<10/*' xmltest text_doc option 'xmlerror=null' (0 rows affected)
To display the contents of @@error, enter:
select @@error --------- 14701 (1 row affected)
This command restores the sample_docs table to its original state:
delete from sample_docs where name_doc='invalid doc'
