How to decode & etc using XQuery/SQL?

Question

Using Microsoft SQL Server...

declare @x xml
set @x = '<Example>&lt;You &amp; Me&gt;</Example>'
select cast(@x.query('/Example/text()') as nvarchar(50))

The result is "&lt;You &amp; Me&gt;" rather than "<You & Me>".

How can I have SQL read the XML in such as way that '&lt;', '&amp;' and '&gt;' are decoded?

Answer 1

Use value() instead of query().

declare @x xml
set @x = '<Example>&lt;You &amp; Me&gt;</Example>'
select @x.value('(/Example)[1]', 'nvarchar(50)')

SQL-Fiddle