SQL 2005 XML Search

Question

Hello, I have a large XML document in Xml column within SQL Server. I need to basically perform a free text search across the elements in the document.

Would you use A) SQL Free Text Search B) A stored procedure that traverses the XML and checks each value of each element C) Use Lucene.NET to build an Index on the fly and search the index?

Users understand this will be slow to some degree. If the stored procedure wasn't a monster to write I'd lean toward that because its the least to maintain and decreases overall complexity.

Answer 1

The book "Pro SQL Server 2008 XML" has a section on Full-Text indexing of XML data that may be of interest to you. It mentions that when XML data is indexed a special "XML Word Breaker" is used to separate text content from the markup. Essentially this means is that only the content is indexed, not the markup. Full text indexes also support stemming and thesaurus matching.

Just noticed that you are using SQL Server 2005, so you'll have to check if this functionality is supported. I suspect that it is.