SOLVED

XML Conversion For Node Text Containing &#38

New Contributor

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.

4 Replies

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

See List of XML and HTML character entity references - Wikipedia

 

@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?

best response confirmed by Doug_Marquardt (New Contributor)
Solution
See the Wikipedia link I posted, one way is &#38;
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.