Solomon Rutzky (6/29/2013)
This is a little more flexible, and straight-forward, than using FOR XML as that might not be desirable when debugging a query.
As with most solutions to a given problem there are trade offs. If any of these characters appear in the text you're trying to view you'll have a harder time using the XML data type:
SELECT CAST('<' AS XML)
GO
SELECT CAST('&' AS XML)
GO
SELECT CAST('<' AS XML)
GO
I've found a case where the processing-instruction technique also falls down (although I have yet to isolate the actual technical reason) but it seems a bit more durable than the solution using XML type.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato