Search Xml With A Like Or Similar Full Search Operation

I want to search an XML valued column to see if a contains a string. I don't know the schema, I want to know if the string is contained anywhere at all. I don't know if XPATH wou

Solution 1:

The simplest (but definitely not the fastest to execute) way would be to cast your column to nvarchar(max) before passing it to like:

cast(ValueXml as nvarchar(max)) like '%PreviewDateRange%'

Solution 2:

There you go:

SELECT*FROM MyTable
WHERE MyXmlMetadataColumn.exist('//*/text()[contains(upper-case(.),upper-case("MySearchTerm"))]') =1

This seems to work fine for me and also does not search the XML tag names as the solution provided by "dasblinkenlight".

I don't know how performant it is though.