AKAWN

When working with XML text the following common conversions are good to know.

XML	Character
&	&
&lt;	<
&gt;	>
&quot;	“
&apos;	‘
&#xD;	Carriage Return (CR)
&#xA;	Line Feed (LF)

To cast a text column which contains XML to human friendly text you could use the REPLACE function e.g.

DECLARE @xml_text nvarchar(MAX);
SET @xml_text = '&   <   >   "   '   
';

SELECT @xml_text AS xml_text;

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@xml_text
, '&' , '&')
, '&lt;'  , '<')
, '&gt;'  , '>')
, '&quot;', '"')
, '&apos;', '''')
, '&#xD;' , ' ')
, '&#xA;' , ' ') 
AS converted_text;

A reverse example of the above e.g. xml to nvarchar, would be:

DECLARE @xml xml;
SET @xml = '<test/> ';

SELECT @xml AS [xml];

SELECT REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(CAST(@xml AS nvarchar(MAX))
, '&', '&amp;')
, '<', '&lt;')
, '>', '&gt;') 
, '"', '&quot;')
, '''', '&apos;')
, ' ', '&#xD;')
, ' ', '&#xA;') 
AS converted_xml;