Search XML with a LIKE or similar full search operation
sql, sql-server-2008-r2, xml, xpath
Solution
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%'
Problem
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.