If you haven’t realized it by now, you cannot do a LIKE without any magic on a XML column. If you do, you will get the following error :
Argument data type xml is invalid for argument 1 of like function
If you need to do a LIKE on a column with XML, there are a few ways to go about it. I however for the most part just need to do this through SQL Server Management Studio, so I use the quick solution of converting it to a varchar field, and problem solved. This would look like the following :
SELECT * FROM [tableName] WHERE CAST([XMLColumnName] AS nvarchar(max)) LIKE N'%[textToSearchFor]%'
Hope this helps and happy coding!