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
A basic_string_expression is a sql_query_expression whose datatype is character, varchar, or java.lang.String.
A general_string_expression is a sql_query_expression whose datatype is character, varchar, 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 an xmltest call is null, then the result of the xmltest call is unknown.
The value of the xml_data_expression parameter is the runtime context for execution of the XPath expression.
An xmltest call evaluates to boolean true or false, as follows:
The xml_query_expression of an xmltest call is an XPath expression whose result is empty (not empty), then the xmltest call returns false (true).
If the xml_query_expression of an xmltest call is an XPath expression whose result is a Boolean false (true), then the xmltest call returns false (true).
Otherwise, an exception is raised:
invalid xml expression for xmltest
See Chapter 3, “XML Language and XML Query Language,”for the following topics:
Restrictions on external URL 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 whitspace.
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:
invalid XML data
If the explicit or default options specifies xmlerror=null a null value is returned.
These examples use the sample_docs table described in Appendix B, “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.
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.
If xmlerror=null or xmlerror=message, a null (unknown) value is returned.
select name_doc from sample_docs where '//price<10/*' xmltest text_doc option 'xmlerror=null' (0 rows affected)
This command restores the sample_docs table to its original state:
delete from sample_docs where name_doc='invalid doc'
