SQL Server Management Studio (SSMS) doesn't always show you what you want to see, sometimes it "interprets" your results, especially when it comes to XML. Open a query window in SSMS and enter the following query:
select 1 Tag, 0 as Parent, CompanyName [Inserted!1!CompanyName!cdata]
FROM (SELECT 'Johnson & Johnson' CompanyName) AS A
FOR XML EXPLICIT
This returns exactly what we would expect to see:
<Inserted>
<CompanyName><![CDATA[Johnson & Johnson]]></CompanyName>
</Inserted>
The CompanyName contains data that would cause errors in valid XML so we wrap it in a CDATA tag. But when you store your XML in a XML Variable, something strange happens:
DECLARE @XMLExample XML
SET @XMLExample = (select 1 Tag, 0 as Parent, CompanyName [Inserted!1!CompanyName!cdata]
FROM (SELECT 'Johnson & Johnson' CompanyName) AS A FOR XML EXPLICIT)
select @XMLExample
You might expect to see the exact same output we saw from the first example but instead we get this:
<Inserted>
<CompanyName>Johnson & Johnson</CompanyName>
</Inserted>
The CDATA code has been escaped. Change the variable to a varchar(max) and there's no problem:
DECLARE @XMLExample nvarchar(max)
SET @XMLExample = convert(nvarchar(max), (select 1 Tag, 0 as Parent, CompanyName [Inserted!1!CompanyName!cdata]
FROM (SELECT 'Johnson & Johnson' CompanyName) AS A FOR XML EXPLICIT))
select @XMLExample
Which means that I need to store data that should contain CDATA as varchar not as XML. <sigh>