I would like to know if it's possible to do a Full-Text search on specific XML nodes within a cataloged XML column.
XML Sample:
<Items>
<Item>
<Field>first_name</Field>
<Data>John</Data>
</Item>
<Item>
<Field>last_name</Field>
<Data>Doe</Data>
</Item>
</Items>
I'm able to query by individual field using XPATH expressions similar to the following:
SELECT * FROM table
WHERE xml_column.query('/Items/Item[Field="first_name"]').value('(/Item/Data)[1]','varchar(255)') like 'John'
However what I would like to accomplish is to be able to FREETEXT search individual XML nodes, for example only "first_name" fields. So something similar to the following statement only this does not work:
SELECT * FROM table
WHERE (FREETEXT(xml_column.query('/Items/Item[Field="first_name"]').value('(/Item/Data)[1]','varchar(255)'), 'John'))