English

FAQ: Why does an XPATH expression with a CONTAINS function return different results than a similar SQL CONTAINS expression?

Question

Why does an XPATH expression with a CONTAINS function return different results than a similar SQL CONTAINS expression?

Answer

An XPATH expression with a CONTAINS function does not allow for Boolean operators. This is different from the SQL CONTAINS clause, which does allow Boolean operators.

For example, if a search is performed to search for all XML documents that contain the word dog or the word cat, this XPATH expression would be incorrect:

Code:
//*[contains(.,'dog OR cat')]

Although this is a valid XPATH expression, this query will pass the string "dog OR cat" to the underlying DBMS as a single value. The results of the query will be dependent on the underlying DBMS; some will return the XML documents that contain both dog and cat next to each other or separated by words such as and, the, and or, while others will return the XML documents that contain "dog" or "cat".

To ensure that all XML documents returned have either word in them, the correct XPATH expression to use is:

Code:
//*[contains(.,'dog') OR contains(.,'cat')]