If you have a varchar field you can easily do
SELECT * FROM TABLE WHERE ColumnA LIKE '%Test%'
select * from WebPageContent where data.exist('/PageContent/Text') = 1
You should be able to do this quite easily:
SELECT * FROM WebPageContent WHERE data.value('(/PageContent/Text)', 'varchar(100)') LIKE 'XYZ%'
.value method gives you the actual value, and you can define that to be returned as a VARCHAR(), which you can then check with a LIKE statement.
Mind you, this isn't going to be awfully fast. So if you have certain fields in your XML that you need to inspect a lot, you could:
With this, you'd basically "extract" a certain portion of the XML into a computed field, make it persisted, and then you can search very efficiently on it (heck: you can even INDEX that field!).