Use a LIKE statement on SQL Server XML Datatype

Question

If you have a varchar field you can easily do SELECT * FROM TABLE WHERE ColumnA LIKE '%Test%' to see if that column contains a certain string.

How do you do that for XML Type?

I have the following which returns only rows that have a 'Text' node but I need to search within that node

select * from WebPageContent where data.exist('/PageContent/Text') = 1

Answer 1

Yet another option is to cast the XML as nvarchar, and then search for the given string as if the XML vas a nvarchar field.

SELECT * 
FROM Table
WHERE CAST(Column as nvarchar(max)) LIKE '%TEST%'

I love this solution as it is clean, easy to remember, hard to mess up, and can be used as a part of a where clause.

EDIT: As Cliff mentions it, you could use:

...nvarchar if there's characters that don't convert to varchar