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>

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
– Christof Colle, Commented Mar 14, 2016 at 7:56

Community · Accepted Answer · 2020-06-20 09:12:55Z

2

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')

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Mar 9, 2016 at 16:27

Gottfried Lesigang

67.6k9 gold badges60 silver badges124 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Christof Colle Over a year ago

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 €

Collectives™ on Stack Overflow

Avoid encoding of ampersand in SQL XML attribute

1 Answer 1

EDIT

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

EDIT

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related