Full-Text Index on an XML Column

Question

This is the reference material I found:

Full-Text Index on an XML Column

http://msdn.microsoft.com/en-us/library/bb522491%28v=SQL.100%29.aspx

So my question is related to how does one perform specific functionality on an XML element based on full text search. In the reference, it mentions that FTS ignores the XML markup. Element tags are used as token boundaries.

I give you my scenario:

I want to rank my full text search results based on each element. Since I am storing the XML document in one column, I can only use full text search method CONTAINSTABLE on that column to get ranking as a whole for that column, but I can't get ranking for each element or perform specific functionality on each element for custom ranking.

Can someone (or Microsoft representative) help me on this issue? If there is no way of getting this custom ranking on elements in an xml column record, then the only option is to decompose the XML elements into its own columns in a database table and then use full text search to get the ranking of each column representing each element.

So just to be clear, the following is my example. I want to give the first element a ranking of weight 10 if my full text search is satisfied on the first element and a ranking of weight 8 if full text search is satisfied on the second element. Basically when doing a full text search, I want to be able to return results based on my own custom ranking rather than the CONTAINSTABLE with the RANK. The problem with this example is it only returns literal search rather than based on full text search due to using the "exist" function on the xml column.

eg.

select FT_TBL.searchXmlColumn.exist('/root/element1/text()[contains(lower-case(.),lower-case(sql:variable("@SearchWordOrPhrase")))]') * 10 +

FT_TBL.searchXmlColumn.exist('/root/element2/text()[contains(lower-case(.),lower-case(sql:variable("@SearchWordOrPhrase")))]') * 8 AS RANKtotal

FROM CustomXmlTable AS FT_TBL INNER JOIN  CONTAINSTABLE(CustomXmlTable, searchXmlColumn,    @SearchWordOrPhrase) AS KEY_TBL ON FT_TBL.guid = KEY_TBL.[KEY]

ORDER BY RANKtotal DESC

Thanks, very much appreciated.

 

 

Answer 1

Hi,

here many example are available please open link, i hope it will be helpful.

http://msdn.microsoft.com/en-us/library/ms345117%28SQL.90%29.aspx

http://webhelp.esri.com/arcgisserver/9.3/java/index.htm#geodatabases/configu-855814768.htm

---

Please mark the post as answer if it is helpfull to you

Answer 2

Full Text search treats a column in a row as a document, which it will search for the needed criteria.  If a row has several columns to search, then it can search all of the columns selected (a list or just *), but any search criteria must all appears within a single column in order to be found. 

For example, the phrase "Red Camaro" must be in a single column. If one column has "Red" and another contains "Camero" even on the same row, those are not considered as a match.

To get the kind of search that you want, I believe that you are left with decomposing the XML into separate columns.  You can keep the XML column in addition, of course.

RLF

PS - Oops, just realized that the original message was from May 3, 2010.  I hope that the poster has moved on to other issues in the last 3 years.