Unanswered: using full text indexes to scan stored XML
A little help is appreciated:
I have an oracle database where I store XML in a clob column, similar to a text column in SQL server. If I have a bookXML table with two columns (bookid, rawXML), that stores xml in the format:
in oracle I can write a query similar to an xpath expression that is performed on all rows:
select bookid, rawXML
where CONTAINS(rawXML, 'smith WITHIN author');
The results of the query return all rows with the value of smith within the <author> node.
I want to be able to accomplish the same thing in sql server. I know a full text index on either a text or image (file type = XML) column is the way to go, but I can not find any examples on what the query would look like and I would like to avoid having to install an XML IFilter on the server.