Avoid encoding of ampersand in SQL XML attribute

Question

Image I have a currency table, containing e.g. this record :
- Id = 1
- Code = EUR
- Symbol = €
Important to notice :
The input in our database is already property HTML-encoded!

Now, when I use this SQL statement :

SELECT '@id'        = Currency.Id
    , '@code'       = Currency.Code
    , '@symbol'     = Currency.Symbol
FROM Currency
FOR XML PATH('currency')
    , ROOT('list')
    , TYPE
;

...it unfortunately results into the following XML :

<list><currency id="1" code="EUR" symbol="&amp;euro;" /></list>

Notice that the Euro-symbol has been re-encoded, rendering it invalid.

How can I avoid that? How can I obtain the following XML output :

<list><currency id="1" code="EUR" symbol="&euro;" /></list>

http://stackoverflow.com/questions/11840981/are-characters-or-allowed-in-xml — granadaCoder, Mar 09 '16 at 15:30
I'm sorry, Shnugo. I didn't vote or accept yet because up until now I didn't find the solution that I was looking for. I've only just began to accept mentally that the solution that I'm looking for won't be available... — Christof Colle, Mar 14 '16 at 07:56

score 2 · Accepted Answer · edited Jun 20 '20 at 09:12

The result you get unfortunately and is re-encoded and invalid is perfectly correct - but not what you expect. You pass in € which is a string. Within XML this is escaped as &euro; and will be re-coded to €.

You must stop to think of XML as kind of formalized string. This is a technical issue. XML will handle this implicitly.

There are two ways:

Go the string-way and cast your XML to NVARCHAR, do any string manipulation you might want (e.g. REPLACE(myXML,'&euro;','€') and cast back to XML or
(I'd prefer this!) hand in the € as actual symbol and let the XML engine do the encoding.

EDIT

One more thing: SQL Server doesn't know the € entity. Try with € or €:

SELECT '€' AS [@EuroSign]               --works
      ,'&euro;' AS [@NamedEscapedEuro]  --will be encoded
      ,'&#8364;' AS [@EscapedEuro]      --will be encoded
FOR XML PATH('TestEuro'),ROOT('root')

SELECT --CAST('<x>'+'€'+'</x>' AS XML).value('/x[1]','nvarchar(10)') AS [@EuroSign]              --not allowed!!!
       --CAST('<x>'+'&euro;'+'</x>' AS XML).value('/x[1]','nvarchar(10)') AS [@NamedEscapedEuro] --not allowed, exists, but not known in SQL Server!
       CAST('<x>'+'&#8364;'+'</x>' AS XML).value('/x[1]','nvarchar(10)') AS [@EscapedEuro]       --works
FOR XML PATH('TestEuro'),ROOT('root')

Not the solution that I was hoping for, but apparently you are right. I'll go with your preferred solution : storing the €-symbol as an actual symbol in my database, so XML will output it properly encoded as € — Christof Colle, Mar 14 '16 at 08:00

Avoid encoding of ampersand in SQL XML attribute

1 Answers1

EDIT