CODEWITHSUNDEEP
sql-serverxmlazurefull-text-searchazure-sql-database
Eoin Campbell
Eoin Campbell

Reputation: 44268

Full Text Search not working on XML Columns on SQL Azure

So SQL Azure recently released a new feature as part of the V12 Upgrades. You can now implement Full Text Search catalogs on your database.

http://azure.microsoft.com/blog/2015/04/30/full-text-search-is-now-available-for-preview-in-azure-sql-database/

I've gone through the Setup instructions, and created an FTI pn a table with an XML Data column.

CREATE FULLTEXT CATALOG recordDataCatalog AS DEFAULT;
CREATE FULLTEXT INDEX ON RecordData(Data) KEY INDEX PK_RecordData ON recordDataCatalog; 
ALTER FULLTEXT INDEX ON RecordData ENABLE; 
GO 
ALTER FULLTEXT INDEX ON RecordData START FULL POPULATION;

Setup went fine; it's quite a big table (~0.5m rows) so I left it over night and came back the next day.

It looks like it's completed. But searching for known strings in the XML is returning no results.

SELECT * FROM RecordData WHERE Contains(Data, 'formsof(freetext, john)')
(0 row(s) affected)
SELECT * FROM RecordData WHERE Contains(Data, 'john')
(0 row(s) affected)

The FULLTEXTCATALOGPROPERTY for PopulateStatus is reporting it's Idle.

The row count for the table, is the same as the Item Count in the Catalog.

select count(id) from recorddata
------------------------------------------
(No column name)
539726
------------------------------------------
SELECT FULLTEXTCATALOGPROPERTY('recordDataCatalog','ItemCount')
----------------------------------------- 
(No column name)
539726

One strange thing I've noticed is that the index keywords seem to be missing/not populated properly.

SELECT * FROM sys.dm_fts_index_keywords(
    DB_ID('TransomTest'), 
    OBJECT_ID('RecordData'))
------------------------------------------------------
keyword display_term    column_id   document_count
0xFF    END OF FILE     2           539726

Anyone got any idea where to head next. Most of the SQL Docs regarding FTS & XML say that this should just work, and word lists should be parsed based on XML tag boundaries. Documentation specific to Azure is non-existant given how new this feature is.

Upvotes: 3

Views: 237

Answers (1)

Satya_MSFT
Satya_MSFT

Reputation: 1022

xml document types indexing is not supported yet and there is work in progress to support this. The list of current document types supported can be queried using : 

select * from sys.fulltext_document_types where version !=''

Upvotes: 4

Related Questions