fulltext index does not index xml columns

Question

We installed a new SQL Server 2019 Standard Edition Cluster (15.0.4298.1) on a Windows Server 2022 Datacenter-Cluster (20348.1547)

In some databases we use fulltext catalogs on xml columns. But the fulltext service is not able to index the xml column. Other columns with other datatypes (int, nvarchar) work fine. So the fulltext service itself is working. According to the documentation indexing of xml columns should just work. https://learn.microsoft.com/en-us/sql/relational-databases/xml/use-full-text-search-with-xml-columns?view=sql-server-ver16 On the previous SQL 2017 cluster it worked. On an other SQL 2019 Developer Edition instance it also works as documented. Changing the compat level to 140/2017 doesn't help. In the SQLFT-Logfiles SQLFTxxxxx.LOG it says

The result from `EXEC sp_help_fulltext_system_components 'filter';` for .xml is. 

filter .xml 41B9BE05-B3AF-460C-BF0B-2CDD44A093B1 C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Binn\xmlfilt.dll 12.0.9735.0 Microsoft Corporation

The path to the xmlfilt.dll file is correct and the file exists.
The SQL Full-text Filter Daemon Launcher Service is running under this account: "NT Service\MSSQLFDLauncher"
There are no error or messages in the SQL Server error log or the windows application log.
here is my test-script

```tsql
drop table if exists _test
go
create table _test (
  id int identity(1,1),
  XmlText xml,
  VCText  nvarchar(max),

  constraint [pk_test] primary key clustered([ID] asc)
)

insert into _test (XmlText,VCText) values('<weight>172</weight>','69')
insert into _test (XmlText,VCText) values('<t>test</t>','abc')

select * from _test

create fulltext catalog [test] with accent_sensitivity = off as default
go

create fulltext index on [dbo].[_test] key index pk_test on ([test]) with (change_tracking auto)
go
alter fulltext index on [dbo].[_test] add ([vctext])
go
alter fulltext index on [dbo].[_test] add ([xmltext])
go
alter fulltext index on [dbo].[_test] enable
GO

alter fulltext catalog [test] rebuild
go

-- query xml column -> no result
select * from _test
  where contains(XmlText,' "test" ')
  
-- query nvarchar(max) column -> result
select * from _test
  where contains(VCText,' "abc" ')

-- query the fulltext system catalogue
select * from sys.dm_fts_index_keywords(db_id(), object_id('_test'))  
/*
| keyword            | display_term | column_id | document_count |
|----------------------------------------------------------------|
| 0x00360039         | 69           | 3         | 1              |
| 0x006100620063     | abc          | 3         | 1              |
| 0x006E006E00360039 | nn69         | 3         | 1              |
| 0xFF               | END OF FILE  | 3         | 2              |
*/

I expect that there also should be the values "172" and "test" for column 2. Here are some other statements I have tried so far, but with no luck.

EXEC sp_fulltext_service @action='load_os_resources', @value=1;
EXEC sp_fulltext_service 'verify_signature', 0;
EXEC sp_fulltext_service 'update_languages';
Exec sp_fulltext_service 'ft_timeout', 600000;
Exec sp_fulltext_service 'ism_size',@value=16;
EXEC sp_fulltext_service 'restart_all_fdhosts';
EXEC sp_help_fulltext_system_components 'filter';
reconfigure with override

Answer 1

Hi @vom Hau, Marco

Refer to this doc: CREATE FULLTEXT INDEX

Only one full-text index is allowed per table or indexed view, and each full-text index applies to a single table or indexed view.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

I don't know what is going on, but when I ran your script, I did get a hit for the data in the XML document. And that on the exact same build as you have, see below.

In sys.fulltext_catalog_indexes, what do you have for the language__id column?

Microsoft SQL Server 2019 (RTM-CU19) (KB5023049) - 15.0.4298.1 (X64) 
	Jan 27 2023 16:44:09 
	Copyright (C) 2019 Microsoft Corporation
	Standard Edition (64-bit) on Windows Server 2016 Standard 10.0 <X64> (Build 14393: ) (Hypervisor)