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.