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!

Jacob Saylor

Software developer in Kentucky

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: