• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 243
  • Last Modified:

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
0
j055
Asked:
j055
  • 2
  • 2
1 Solution
 
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
 
j055Author Commented:
Can't see why <root>plain text</root> can't work. I shall give it a go.

Cheers
Andrew
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now