none
Full text index XML column attribute problem

    Question

  • Hi,

     

    I’m using SQL Server 2005 Standard edition and specific data is stored in a XML column with specified schema. We need to do a full text search in the XML documents and for that purpose we created a full text catalogue. The problem is that element values are indexed but not the attribute values. I have created a small example below (test purpose) and the outcome is that the attribute value Monday is found but not Tuesday. Since we got many sub nodes in our documents the attribute values is not found. Is there a way to get all attribute values in XML documents indexed in SQL Server 2005?

    USE [master]
    GO
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [TestDB].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    
    use [TestDB]
    GO
    
    CREATE FULLTEXT CATALOG dataxml_catalogue AS DEFAULT
    GO
    
    CREATE TABLE TestTable (
      TestID int IDENTITY(1,1) NOT NULL,
      DataXML xml NOT NULL
    )
    
    CREATE UNIQUE CLUSTERED INDEX IX_TestTable_TestID ON TestTable (
      TestID ASC
    )
    
    CREATE FULLTEXT INDEX ON TestTable (DataXML)
      KEY INDEX IX_TestTable_TestID
      ON dataxml_catalogue
      WITH CHANGE_TRACKING AUTO
    
    INSERT INTO TestTable(DataXML) VALUES('<root><day id="Monday" /></root>')
    INSERT INTO TestTable(DataXML) VALUES('<root><subnode><day id="Tuesday" /></subnode></root>')
    
    SELECT * FROM TestTable WHERE CONTAINS(DataXML, 'Monday')
    SELECT * FROM TestTable WHERE CONTAINS(DataXML, 'Tuesday')
    
    Tuesday, March 30, 2010 7:07 PM

Answers

  • can you store your data as text instead of XML? This will work.
    USE [master]
    GO
    IF not exists( select * from sys.databases where name='testdb')
    create database testdb
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [TestDB].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    
    use [TestDB]
    GO
    if not exists(select * from sys.fulltext_catalogs where name='dataxml_catalogue')
    CREATE FULLTEXT CATALOG dataxml_catalogue AS DEFAULT
    GO
    
    CREATE TABLE TestTable2 (
      TestID int IDENTITY(1,1) NOT NULL constraint TestTable2PK primary key,
      DataXML varchar(max) NOT NULL
    )
    
    
    CREATE FULLTEXT INDEX ON TestTable2 (DataXML)
      KEY INDEX TestTable2PK
      ON dataxml_catalogue
      WITH CHANGE_TRACKING AUTO
    
    INSERT INTO TestTable2(DataXML) VALUES('<root><day id="Monday" /></root>')
    INSERT INTO TestTable2(DataXML) VALUES('<root><subnode><day id="Tuesday" /></subnode></root>')
    
    SELECT * FROM TestTable2 WHERE CONTAINS(DataXML, 'Monday')
    SELECT * FROM TestTable2 WHERE CONTAINS(DataXML, 'Tuesday')
    
    


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Wednesday, March 31, 2010 2:57 PM

All replies

  • can you store your data as text instead of XML? This will work.
    USE [master]
    GO
    IF not exists( select * from sys.databases where name='testdb')
    create database testdb
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [TestDB].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    
    use [TestDB]
    GO
    if not exists(select * from sys.fulltext_catalogs where name='dataxml_catalogue')
    CREATE FULLTEXT CATALOG dataxml_catalogue AS DEFAULT
    GO
    
    CREATE TABLE TestTable2 (
      TestID int IDENTITY(1,1) NOT NULL constraint TestTable2PK primary key,
      DataXML varchar(max) NOT NULL
    )
    
    
    CREATE FULLTEXT INDEX ON TestTable2 (DataXML)
      KEY INDEX TestTable2PK
      ON dataxml_catalogue
      WITH CHANGE_TRACKING AUTO
    
    INSERT INTO TestTable2(DataXML) VALUES('<root><day id="Monday" /></root>')
    INSERT INTO TestTable2(DataXML) VALUES('<root><subnode><day id="Tuesday" /></subnode></root>')
    
    SELECT * FROM TestTable2 WHERE CONTAINS(DataXML, 'Monday')
    SELECT * FROM TestTable2 WHERE CONTAINS(DataXML, 'Tuesday')
    
    


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Wednesday, March 31, 2010 2:57 PM
  • can you store your data as text instead of XML? This will work.
    USE [master]
    GO
    IF not exists( select * from sys.databases where name='testdb')
    create database testdb
    IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
    begin
    EXEC [TestDB].[dbo].[sp_fulltext_database] @action = 'enable'
    end
    GO
    
    use [TestDB]
    GO
    if not exists(select * from sys.fulltext_catalogs where name='dataxml_catalogue')
    CREATE FULLTEXT CATALOG dataxml_catalogue AS DEFAULT
    GO
    
    CREATE TABLE TestTable2 (
      TestID int IDENTITY(1,1) NOT NULL constraint TestTable2PK primary key,
      DataXML varchar(max) NOT NULL
    )
    
    
    CREATE FULLTEXT INDEX ON TestTable2 (DataXML)
      KEY INDEX TestTable2PK
      ON dataxml_catalogue
      WITH CHANGE_TRACKING AUTO
    
    INSERT INTO TestTable2(DataXML) VALUES('<root><day id="Monday" /></root>')
    INSERT INTO TestTable2(DataXML) VALUES('<root><subnode><day id="Tuesday" /></subnode></root>')
    
    SELECT * FROM TestTable2 WHERE CONTAINS(DataXML, 'Monday')
    SELECT * FROM TestTable2 WHERE CONTAINS(DataXML, 'Tuesday')
    
    


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941
    Wednesday, March 31, 2010 2:57 PM
  • Hi,

    Thanks for your answer!

    Since I need the data to be stored in XML format I created a second table with the data stored as nvarchar(max). To keep the information up to date with the primary table I use some triggers. For the search procedure I join the second table and perform the CONTAINS on the text column and it works perfectly.

    Thursday, April 1, 2010 6:41 AM