asked on

How can I stop XML elements being indexed when using SQL Server Full Text Search

Hi

I have Full Text Search enabled on a varchar(max) column. Some but not all rows contain XML in this column. I'd like to search for text using FT but ignore any XML elements. e.g.

SELECT Title, Text_1
FROM CmsContent
WHERE CONTAINS (Text_1, '"table" AND "tbody"' );
GO

should not return any results. The ideal solution would be to not index these elements in the first place. What are my options? I'm using SQL Server 2008

Many thanks
Andrew

Anthony Perkins

Unfortunately you cannot stop Full-Text Search from indexing that column. The best you can do is move the Xml to a different column with a more appropriate data type and index the Xml elements or add a column indicating whether the column has Xml or not. Alternatively you can use something cheesy like this:
SELECT Title, Text_1
FROM CmsContent
WHERE CONTAINS (Text_1, '"table" AND "tbody"' );
and Text_1 LIKE '<%'

j055

ASKER

Actually I want to exclude xml elements from any FT search and only search the inner text of any elements. I just read this:

http://msdn.microsoft.com/en-us/library/bb522491.aspx

which says 'You can create a full-text index on XML columns that indexes the content of the XML values, but ignores the XML markup' so I guess your suggestion to move the content to it's own xml column type is the best way to go.

Thanks
Andrew

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

j055

ASKER

Can't see why <root>plain text</root> can't work. I shall give it a go.

Cheers
Andrew