XML Conversion For Node Text Containing &#38

[Doug_Marquardt]

When I cast a string (which is xml) to the xml data type and the node comments contain &#38 it gets converted to &amp. For example:

Declare @xml xml =
'
<SHOP_INSTRUCTIONS>
Remove &#38; return to stock.
</SHOP_INSTRUCTIONS>
';

Select @xml;

/*
Return This:
<SHOP_INSTRUCTIONS>
Remove &amp; return to stock.
</SHOP_INSTRUCTIONS>
*/

I would expect the conversion to be: Remove & return to stock.

Am I getting the correct results, or what am I missing?

Thanks.

[#2]

@Doug_Marquardt , & is just a different (decimal) encoding of & = ampersand.

See List of XML and HTML character entity references - Wikipedia

[#3]

@olafhelper 

Yes, I realize that... but if I do this it throws an error:

Declare @xml xml =
'
<SHOP_INSTRUCTIONS>
Remove & return to stock.
</SHOP_INSTRUCTIONS>
';

Select @xml;
XML parsing: line 3, character 9, illegal name character

So how do I represent & as part of the node text?

[#4 · Accepted solution]

See the Wikipedia link I posted, one way is &

[#5]

I finally figured out what my issue was... I didn't understand that the $amp; would be converted to & when reading the node text value with the XML parser. I was viewing the XML in SQL Server results grid.
I'm new to XML in SQL Server so this is new to me.
Thanks.