CODEWITHSUNDEEP
sql-serverxmlsql-server-2008-r2full-text-search
scutch
scutch

Reputation: 128

SQL Server Full Text not indexing XML data

SQL Server 2008 R2 - Full Text Index is working on an nvarchar column, but not on an xml column. Its like it can't index the xml values. I put the below example together to test on multiple servers and on all but one it returns data for both freetext searches. On the server in question, I don't get results for the second select statement (which is looking in the xml column). I've gone through all the settings I can find related to FullText, but I don't see anything that enables/disables XML.

Note: the two select statements at the end sometimes need to be run after a delay. It appears the indexing is in progress when you run the entire block at once. 

-- Create Table
CREATE TABLE [dbo].[a_TestFullText](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [otherdata] [varchar](250) NOT NULL,
    [xmldata] [xml] NOT NULL,
    CONSTRAINT [pk_TestFullText] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

-- Populate Data
Insert Into dbo.a_TestFullText(xmldata, otherdata) Values
('<nodename>Ham Cheese Sandwich</nodename>', 'Strange'),
('<nodename>Potatoes</nodename>', 'Tony')
GO

-- Create Catalog 
Create FULLTEXT CATALOG TestFullText
GO

-- Create Index
Create FULLTEXT INDEX ON dbo.a_TestFullText(otherdata,xmldata) KEY INDEX [pk_TestFullText] ON [TestFullText]
GO

-- Populate Index
Alter FULLTEXT INDEX on dbo.a_TestFullText START FULL POPULATION;
GO

-- Check for results
Select *
From dbo.a_TestFullText
Where FreeText(otherdata, 'Strange')

Select *
From dbo.a_TestFullText
Where FreeText(xmldata, 'Potatoes')
GO

Upvotes: 2

Views: 506

Answers (1)

scutch
scutch

Reputation: 128

This resolved itself after a restart of the SQL Server Service. When initially looking into the problem we couldn't restart the service, but we did the following day and the problem went away. Still not sure what happened or why.

Upvotes: 1

Related Questions