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
j055Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anthony PerkinsCommented:
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 '<%'
0
j055Author Commented:
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
0
Anthony PerkinsCommented:
Right, but then you would have to split the values into 2 columns, Xml data in one, non-Xml data in the other.  You may be able to place one big text in a single Xml tag, but I have never tried that.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
j055Author Commented:
Can't see why <root>plain text</root> can't work. I shall give it a go.

Cheers
Andrew
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.