select convert(xml, '').query('contains("foo", "oo")')
This query will return the value “true” because “oo” is a substring of “foo”. There is one gotcha here, remember that we perform comparisons in our “XML Collation” for all XQuery operations, which means that comparisons are essentially code-point comparisons and are thus case sensitive. So the following query will return false:
select convert(xml, '').query('contains("xquery is an xml query language", "XQuery")')
This would all be well and good, except T-SQL also defines a CONTAINS function which uses SQL Server’s fulltext search capabilities. As such, people from the T-SQL world who are just starting to play with XQuery are somewhat confused when they realize that T-SQL CONTAINS and XQuery contains are really two completely different things. Now, T-SQL CONTAINS will of course work with the new XML Datatype, and can be used quite effectively to augment our XQuery features for finding data within XML instances. However, it is important to point out that the two functions are different by design, and the results that each get are not meant to overlap (although they occasionally do) and cannot be used as replacements for each other.
In short: XQuery contains implements simple substring matching. T-SQL contains implements fulltext matching and thus does all of the cool fulltext things such as noise word removal and ranking.
Update: I am copying this out of the comments. Kent talked about this very same issue about a month ago. He also gives some examples of using fulltext search over XML data. I recommend checking it out.