Creating a full text search on xml column - SSMS

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Express Edition and Compact Edition (2005)

Creating a full text search on xml column - SSMS

Author

Topic

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-29 : 09:48:21

I originally posted this question here:
http://forums.asp.net/p/1340070/2710527.aspx#2710527

Hi,

I've created a fulltext search on an xml column on the following:

table name: tbl_manuals

column [xml] name: xmlcontent

As follows:

/* Enable full text search */
sp_fulltext_database  'enable'
go
/* Create catalog [table name]_Cat */
sp_fulltext_catalog 'tbl_manuals_Cat', 'create'
go
/* make sure PK key is exactly whats under the Keys folder */
sp_fulltext_table  'tbl_manuals',  'create',
        'tbl_manuals_Cat', 'PK_tbl_manuals'
go
/* Choose column to index */
sp_fulltext_column  'tbl_manuals', 'xmlcontent',  'add'
go
/* Activate table */
sp_fulltext_table  'tbl_manuals',  'activate'
go
/* Index your catalog */
sp_fulltext_catalog  'tbl_manuals_Cat', 'start_full'

Everything gets created, but when I run the following sql, I get an empty set returned:

SELECT * FROM tbl_manuals WHERE FREETEXT(xmlcontent, 'Destroy')

The word "Destroy" is in the xml content. I am not sure if I missed anything when I created the fulltext search.

Any thoughts?

Thanks

bonekrusher
Starting Member

44 Posts

Posted - 2008-10-30 : 06:30:02

Solution:

/* Enable full text search */
sp_fulltext_database  'enable'
go
CREATE FULLTEXT CATALOG FTCmanuals as DEFAULT 
GO

CREATE FULLTEXT INDEX ON tbl_manuals(xmlcontent)



/* make sure PK key is exactly whats under the Keys folder */
KEY INDEX PK_tbl_manuals ON FTCmanuals 

GO
/* usage 
 SELECT * FROM tbl_manuals WHERE FREETEXT(xmlcontent, 'Harness') 
*/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources