Quiz: Fulltext and XML in SQL Server 2005

  • Article

Using SQL Server's fulltext engine is a great way to optimize your XQuery statements which use XQuery's contains function in a predicate.  However, a bit of care has to be taken, because sometimes you might get results which you don't quite expect.

Let's assume that I have a fulltext catalog on the table [t1], and that the index is fully populated.  Are these queries always going to produce the same result?

 select * from t1
where contains(xmlcol, '"John Doe"') and 
xmlcol.exist('/Document/Author[contains(., "John Doe")]') = 1

select * from t1
where xmlcol.exist('/Document/Author[contains(., "John Doe")]') = 1

Unfortunately, the answer is no.  Can you provide an example document which will be returned by one query, and not the other?