Forum Discussion

Doug_Marquardt's avatar
Doug_Marquardt
Copper Contributor
Jul 30, 2022
Solved

XML Conversion For Node Text Containing &#38

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 s...
  • olafhelper's avatar
    olafhelper
    Aug 02, 2022
    See the Wikipedia link I posted, one way is &#38;
  • Doug_Marquardt's avatar
    Doug_Marquardt
    Copper Contributor
    Aug 01, 2022

    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?

    • olafhelper's avatar
      olafhelper
      Bronze Contributor
      Aug 02, 2022
      See the Wikipedia link I posted, one way is &#38;
      • Doug_Marquardt's avatar
        Doug_Marquardt
        Copper Contributor
        Aug 03, 2022
        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.

Resources