By some legacy reason xml-text is currently being saved as pure binary data in a system I´m maintaining.
More than often we need to check this xml data to see if everything is as it should and every single time I stumble on how to do this, can’t seem to keep it in memory so here it is for the world to use:
SELECT convert(varchar(max), convert(varbinary(max), binaryColumn)) AS 'binary to text' FROM YourTable where YourCriteria = XXXXX