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

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!