Search XML with a LIKE or similar full search operation

Question

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 would work in this situation.

The equivalent of

Select s.Name, ts.ValueXML from table t (nolock) 
join table2 ts (nolock) on t.key_Id = ts.key_Id
join table3 s (nolock) on ts.key_Id=s.key_Id
where s.Name like '%Lab%' and ts.ValueXML  like '%PreviewDateRange%'

ERROR: Argument data type xml is invalid for argument 1 of like function.

relevant ts Table columns

ValueXml (XML(.), null)

The item I'm searching for should be an attribute. So if the above isn't possible, then anything containing that attribute would be a good alternative.

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.

Comments

[Sergey Kalinichenko]

This should work. Could you show an example where it does not work?

[Sergey Kalinichenko]

Could you post the table definition, and tag the question with the type of RDBMS that you are using?

[yairr]

This is a non-production, one off query. Performance doesnt matter. I tried this and got Error: Conversion of one or more characters from XML to target collation impossible

[Sergey Kalinichenko]

@P.Brian.Mackey It should do the trick then - I tried it on sqlfiddle, and it worked fine.

[MattH]

Edit required as the prose response mentions cast varchar and the code mentions nvarchar. Varchar is fine, but if a collation conversion is encountered nvarchar can be used - that was the part I needed.

[user2173353]

This seems to search the XML tag names also. Any way to get rid of those?