Full-Text Search and the XML Datatype - SQL Server 2008

Beyond indexing the XML column, you can also full-text index the XML column using the built-in XML IFilter in SQL Server. You can combine the XML column index with the full-text index. However, the following differences apply to full-text indexing of XML:

  • Markup is not indexed; only content is indexed. Therefore, the elements are the boundaries of the full-text indexing.
  • Attributes are not indexed, since they are considered part of the markup. If you mostly store your values in attributes, you will want to use an XML index, not full-text search.
  • Full-text search returns the full XML document, not just the section where the data occurred. If you want to retrieve a particular element that contained the search phrase, you need to further query the returned XML document with XQuery.
  • The XQuery contains method and the full-text search contains method are different. Full-text search uses token matching and stemming, while XQuery is a substring match. Other than these differences, the standard full-text restrictions are in effect, such as having a unique key column on the table and executing the correct Data Definition Language (DDL) to create the index. The DDL that follows creates a full-text index on a table containing an XML column. A primary key index called pkft is created in the following code:
CREATE FULLTEXT CATALOG ft AS DEFAULT
CREATE FULLTEXT INDEX on xmltblnew(people) KEY INDEX pkft

You can combine an XML column index, both primary and secondary, with a full-text index. Whether you do this depends on what your data in the tables looks like, what your application workload does, and the overhead that you want to place on the server for creating and maintaining your index. If you find that you are querying data in your XML column regularly, and a lot of the XML information is not stored as attributes, then creating both a column and full-text index may speed up your query response time. First, you will want to filter based on the full-text index, and then you can use XQuery on the returned data to filter the XML data even more.

For example, the following code uses a full-text search with the contains keyword and an XQuery that also uses the contains keyword. Remember that the full-text search contains keyword is different from the XQuery one. The full-text search is a token-match search that uses stemming, whereas the XQuery one is a substring match. Therefore, if you search for “swim” using full-text search, you will also find values for “swimming” and “swam.” However, with XQuery, you will find only “swimming” and “swim,” since XQuery performs a substring match.

SELECT * FROM xmltblnew
WHERE contains(people,'Tom')
AND people.exist('//familyName/text()[contains(.,"Rizzo")]') =1

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SQL Server 2008 Topics