Answer: Fulltext and XML in SQL Server 2005

  • Article

A little while ago I threw out this quiz regarding XML and SQL Server 2005's full text search support.  As I stated in the quiz, there is a subtle difference between what you can get with contains() and the fulltext support.  Specifically, the difference lies in how our full text engine injects whitespace between element content.  In XQuery, when we atomize a list we concatenate the strings together without any whitespace in between.  For example, the following query:

declare @x xml

set @x = '<foo><bar>baz</bar><boo>hello</boo></foo>'

select @x.query('data(/foo)')

Will return "bazhello".  However, our full text indexer will actually put a whitespace character between the two pieces of text and index "baz hello".  Thus, fulltext queries for "bazhello" will not actually return any results.  When we use XQuery contains(), we atomize the data, resulting in the string "bazhello" which we then perform the substring check on.  As such, the following query returns true. 

declare @x xml

set @x = '<foo><bar>baz</bar><boo>hello</boo></foo>'

select @x.query('contains(/foo[1], "bazhello")')