none
XML Attribute Values in XML Columns Not Indexed for Fulltext Search RRS feed

  • Question

  • Hello,

    I'm using SQL Server 2016 Developer (13.0.5101.9). I've created Fulltext Catalog for Table with XML Column. According to the documentation Attribute values are full-text indexed unless they are numeric values.

    The problem is that attribute values are indexed only in some circumstancies.

    • If there are multiple elements with the same name and only one element has attribute, the attribute is indexed.
    • If there are multiple elements with the same name and all attributes has distinct names, all attributes are indexed.
    • If there are multiple elements with distinct names and attributes have the same name, all attributes are indexed.
    • If there are multiple elements with the same name and two or more elements have an attribute with the same name, none attribute value is indexed.

    Behavior of the indexing XML attribute values is very strange and the results do not match the documentation.

    Is it a bug of the SQL Server 2016? Or is it fixed in SQL Server 2017?

    For testing purposes fulltext catalog uses empty stop list and I use this script for inserting XML document:

    TRUNCATE TABLE [dbo].[FulltextTable]
    INSERT INTO [dbo].[FulltextTable] ([XmlColumn])
         VALUES ('<entry><item full="AAA">A</item><item>B</item></entry>')
    GO


    To check the indexed values this script is used, which shows display terms:

    SELECT display_term FROM sys.dm_fts_index_keywords_by_document
    ( 
        DB_ID('FulltextTest'),     
        OBJECT_ID('FulltextTable') 
    )

    I'm getting following values for the following XML documents:

    XML Indexed values
    <entry><item full="AAA">A</item><item>B</item></entry>
    a, aaaa, b
    <entry><item full="CCC">C</item><item full="DDD">D</item></entry>
    c, d
    <entry><item>E</item><item full="FFF">F</item></entry>
    e, f, fff
    <entry><item>G</item><item full="HHH">H</item><item>I</item></entry>
    g, h, hhh, i
    <entry><item full="JJJ">J</item><q full="KKK">K</q><item>L</item></entry>
    j, jjj, k, kkk, l
    <entry><item full="MMM">M</item><item short="NNN">N</item><item tiny="OOO">O</item></entry>
    m, mmm, n, nnn, o, ooo


    Boris Lehečka (Word, Access, VBA, XML, C#, SQL)

    Wednesday, September 4, 2019 8:48 PM