Use a LIKE statment on SQL Server XML Datatype

MSSql not allow you to search the XML datatype using the Like keyword, the following is the sample query to allow you to convert your XML datatype data into string, after that you can utilize the MSSql build in string function to query for your data.

SELECT * FROM
    (SELECT *, CONVERT(varchar(MAX), [COLUMNA]) as [XMLDataString] FROM TABLE) x
WHERE [XMLDataString] like '%Test%'