CODEWITHSUNDEEP
.netsql-serverxmlfull-text-searchxml-encoding
krzycho2
krzycho2

Reputation: 1

How to query utf-16 encoded XML files stored as varbinary?

I want to run Full-Text Search on XML files that are stored as varbinary in SQL Server 2016 to find phrases (id, names, phones etc.) in those files. The problem is that the query doesn't find UTF-16 encoded files.

I have the following tables

Table Files:

Id Filename
1 utf8-encoded.xml
2 utf16-encoded.xml

Table FileContent:

Id Content Type
1 0x444ae9b... XML
2 0x422de56... XML

and a view:

CREATE VIEW Search 
WITH SCHEMABINDING 
AS 
    SELECT 
        e.Id, e.Filename, c.[Content], c.[Type]
    FROM 
        Files AS f 
    INNER JOIN 
        FileContent AS c ON c.Id = f.Id

Then I execute a full-text search query in C# in .NET Framework 4.6.1 using Dapper:

var searchTerm = "xxxxx"; // from user
var query = "SELECT * FROM Search s WHERE CONTAINS(s.Content, @searchTerm)";
var data = connection.Query<File>(query, new {searchTerm});
...

Changing Dapper parameter to sql-injectable string concatenation like this: WHERE CONTAINS(s.Content, " + searchTerm" + ")" doesn't change anything.

The query finds UTF-8 encoded XML files but not UTF-16 encoded. I believe that this is not Dapper-related issue.

How can I fix this to be able to find UTF-16 encoded XML files too? Does SQL Server's full-text search even work with UTF-16 encoded XML files?

Upvotes: 0

Views: 236

Answers (0)

Related Questions