Search XML Column as a String Using LIKE

You can search an XML column as a string without using XQuery by converting it to a string and then using LIKE. However as a computed column can’t be part of a WHERE clause you need to wrap it in another SELECT like this:

(SELECT CONVERT(varchar(MAX), [XMLData]) as [XMLDataString] FROM [TABLE_NAME]) this_is_irrelevant
WHERE [XMLDataString] like '%SEARCH_STRING%'