SQL Server xml deserialization

Question

I have this example of script for deserialization of an xml, but I'm getting a weird description from it (attached)

How can I write this to get Atom cloth - Black/grey - which is the right deserialization?

Thank you

declare @x xml
set @x = '<options>
<option>
<code>99248</code>
<description>Atom&amp;#32;cloth&amp;#32;&amp;#45;&amp;#32;Black&amp;#47;grey</description>
<monthlycost>0.00</monthlycost>
<allowed />
</option>
<option>
<code>99239</code>
<description>Metallic&amp;#32;&amp;#45;&amp;#32;Sargasso&amp;#32;blue</description>
<monthlycost>12.85</monthlycost>
<allowed />
</option>
</options>'


select 
    pref.value('(code/text())[1]', 'varchar(32)') as Code           
   ,pref.value('(description/text())[1]', 'varchar(80)') as [Description]
   ,pref.value('(monthlycost/text())[1]', 'varchar(32)') as MontlyCost  
from 
   @X.nodes('/options/option') AS Options(pref)

Answer 1

You can call value() method twice to unescape the entities twice, for example :

select 
    pref.value('(code/text())[1]', 'varchar(32)') as Code           
   ,pref.value('(description/text())[1]', 'varchar(80)') as [Description]
   ,CONVERT(XML, 
            pref.value('(description/text())[1]', 'varchar(80)')
    ).value('.', 'varchar(80)') as [DescriptionFixed]
   ,pref.value('(monthlycost/text())[1]', 'varchar(32)') as MontlyCost  
from 
   @X.nodes('/options/option') AS Options(pref)

Sqlfiddle Demo

output :

|  Code |                                 Description |         DescriptionFixed | MontlyCost |
|-------|---------------------------------------------|--------------------------|------------|
| 99248 | Atom cloth - Black/grey |  Atom cloth - Black/grey |       0.00 |
| 99239 |    Metallic - Sargasso blue | Metallic - Sargasso blue |      12.85 |

Answer 2

It looks to me like your descriptions have been HTML escaped and then XML escaped.

Original:

Atom cloth - Black/grey

HTML Escape and convert spaces, slash, and dash to HTML number codes:

Atom cloth - Black/grey

XML Escape and convert ampersand to &:

Atom cloth - Black/grey

And that's what you have in your variable:

Atom cloth - Black/grey

You'll have to push the descriptions back through unescape functions, but I don't believe there are any native functions that do that. There's the value method like this like you're doing:

declare @x xml = '';

select @x.value('"Atom cloth - Black/grey"','nvarchar(50)');

But that just unescapes the XML and gets you the HTML escaped result:

Atom cloth - Black/grey

You have to do it again to get your final result:

select @x.value('"Atom cloth - Black/grey"','nvarchar(50)')

Atom cloth - Black/grey