SQL Server 2008 full-text index on XML column do not index attribute values for the inner nodes

Article ID: 2513181 - View products that this article applies to.
Expand all | Collapse all

SYMPTOMS

In Microsoft SQL Server 2008 or Microsoft SQL Server 2008 R2 when indexing a XML column using full-text only node values and attribute values for the top node are indexed. Attribute values for any of the inner nodes are not indexed.

CAUSE

This behavior happens because the XML word breaker that is shipped with SQL Server 2008 and SQL Server 2008 R2 does not return attribute values for any of the inner nodes (xmlfilt.dll - Version: 12.0.9735.0).

RESOLUTION

This issue can be resolved using the XML word breaker that ships with Windows operating system when SQL Server is running on either Windows 7 or Windows Server 2008 R2 (version shipped with Windows use different file name - xmlfilter.dll). If you are running SQL Server on a lower version of Windows, you first need to upgrade to these operating systems and then use the following procedure to resolve this issue.

Procedure to resolve the problem on SQL Servers running on Windows Server 2008 R2 and Windows 7 environments:


Important This section, method, or task contains steps that tell you how to modify the registry. However, serious problems might occur if you modify the registry incorrectly. Therefore, make sure that you follow these steps carefully. For added protection, back up the registry before you modify it. Then, you can restore the registry if a problem occurs. For more information about how to back up and restore the registry, click the following article number to view the article in the Microsoft Knowledge Base: 322756 (http://support.microsoft.com/kb/322756/ ) How to back up and restore the registry in Windows

Note: You need to restart SQL Server Service after going through the following procedure for the changes to come into effect.
  1. Navigate to the following registry hive on your SQL Server machine and save it as SQLMSSearch.reg.

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<instance>\MSSearch\CLSID
    Note: Replace the "<instance>" in the path above with the relevant instance ID of SQL Server 2008/SQL Server 2008 R2.
    E.g. "MSQL10.MSSQLSERVER" for a default instance or "MSSQL10.KATMAI" for a named instance "Katmai"
    For further information refer to the following topic in SQL Server Books Online:
    File Locations for Default and Named Instances of SQL Server (http://msdn.microsoft.com/en-us/library/ms143547.aspx)
  2. Edit SQLMSSearch.reg file with notepad and replace all occurrences of xmlfilt.dll
    by C:\\Windows\\system32\\xmlfilter.dll and then save the changes.
    Notes:
    - This assumes your windows folder is located at C:\Windows.
    - You need to enter each backslash in the new path twice!
  3. Click the file SQLMSSearch.reg to import the content into the registry.
  4. Execute the following T-SQL commands to enable the new setting in SQL Server
    exec sp_fulltext_service 'load_os_resources', 1
    exec sp_fulltext_service 'verify_signature ', 0 exec sp_fulltext_service 'update_languages'exec sp_fulltext_service 'restart_all_fdhosts'
  5. Check the availability of the new settings with this T-SQL command:
    EXEC sp_help_fulltext_system_components 'fullpath','c:\windows\system32\xmlfilter.dll'
    Notes: You need to adapt the above command to suit the correct Windows system path on your system. Also ensure that all the languages (respectively their lcid), that should show the new behaviour are listed in the column "componentname" in the output

MORE INFORMATION

Steps to repro:

  1. Create a simple table as follows:
    CREATE TABLE [dbo].[tesFTS](
    [idField] [int] PRIMARY KEY,
    [xmlField] [xml] NOT NULL)
  2. Enable FTS on the xmlField field (the word breaking language that you select here is not relevant to the repro)
  3. Insert one record on that table, as follows:
    insert into testFTS (idField, xmlField) values
    (1, '<TopNode TopFirstAtt="TopAttValue" TopSecondAtt="Second Value">
    <MiddleNode FirstAtt="ValueOne" SecondAtt="ValueTwo" ThirdAtt="ValueThree">
    <BottomNode BottomAtt="BottomValue">
    <InnerMostNode>Innermost Text</InnerMostNode>
    </BottomNode>
    </MiddleNode>
    <MiddleNode FirstAtt="ValueFour" SecondAtt="ValueFive" ThirdAtt="ValueSix">
    <BottomNode BottomAtt="OtherBottomValue" />
    </MiddleNode>
    <MiddleNode FirstAtt="ValueSeven" SecondAtt="ValueEight" ThirdAtt="ValueNine">
    <BottomNode BottomAtt="LastValue" />
    </MiddleNode>
    <NodeWithValue>Testing whether this value will be indexed</NodeWithValue>
    </TopNode>')

  4. By querying fts_index_keywords for this particular table - select * from sys.dm_fts_index_keywords(db_id(),object_id('testFTS')), the following results are obtained:
    Collapse this tableExpand this table
    keyworddisplay_termcolumn_iddocument_count
    0x0069006E00640065007800650064indexed21
    0x0069006E006E00650072006D006F00730074innermost21
    0x007300650063006F006E0064second21
    0x00740065007300740069006E0067testing21
    0x0074006500780074text21
    0x0074006F007000610074007400760061006C00750065topattvalue21
    0x00760061006C00750065value21
    0x0077006800650074006800650072whether21
    0xFFEND OF FILE21
     These display terms appear to be restrict to:
    • Node values, regardless of how deeply they are nested within the XML document.
    • Attribute values only for the top node and not from any of the inner nodes that would be expected.
       
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Properties

Article ID: 2513181 - Last Review: July 13, 2011 - Revision: 3.0
APPLIES TO
  • Microsoft SQL Server 2008 Developer
  • Microsoft SQL Server 2008 Enterprise
  • Microsoft SQL Server 2008 Enterprise Evaluation
  • Microsoft SQL Server 2008 R2 Datacenter
  • Microsoft SQL Server 2008 R2 Developer
  • Microsoft SQL Server 2008 R2 Enterprise
  • Microsoft SQL Server 2008 R2 Parallel Data Warehouse
  • Microsoft SQL Server 2008 R2 Standard
  • Microsoft SQL Server 2008 R2 Standard Edition for Small Business
  • Microsoft SQL Server 2008 Standard
  • Microsoft SQL Server 2008 Web
  • Microsoft SQL Server 2008 Workgroup
Keywords: 
KB2513181

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com