xml display & instead of & sql server

Question

I have a table from which i am generating XML using below code

Select CASE WHEN changed=0 THEN 'False' WHEN changed=1 THEN 'True' ELSE Null END As "@changed",
            Name as 'Name', 
            Integer AS 'Value/Integer',
            Decimal AS 'Value/Decimal',
            Dimensions AS 'Value/Dimensions',
            Units AS 'Value/Units',
            Percentage AS 'Value/Percentage',
            code AS 'Value/code',
            text AS 'Value/text',
            CASE WHEN boolean=0 THEN 'False' WHEN boolean=1 THEN 'True' ELSE Null END AS 'Value/boolean'
            from [abc].[xyz_detail_table](nolock) CD where code_ids='268973407' FOR XML PATH('Detail'), TYPE

Here text column has value as

Horse & Horse

but in the output xml it is coming as

> Horse & Horse

I have followed this below question but in my case TYPE is already mentioned. Can you please guide me how to resolve this issue

FOR XML PATH(''): Escaping "special" characters

Table DML used for column TEXT: Text(varchar(254),null)

Answer 1

As I state in the comments, the ampersand (&) is a reserved character in XML, as such when you put a value, such as 'Horse & Horse' into an XML node's text value, the ampersand is escaped (to &).

When parsing said value, your parsing application will read that escape sequence and display it correctly. Take the following:

SELECT t.x,
       t.x.value('(t/text())[1]','varchar(30)') AS v
FROM  (SELECT *
       FROM (VALUES ('Horse & Horse'))V(t)
       FOR XML PATH(''),TYPE) t(x);

This returns the xml value <t>Horse &amp; Horse</t> and the varchar value 'Horse & Horse'; when SQL Server consumed the node's value it changed the & to &.

If you "must" not have & then you can't treat your value as xml. You will need to CONVERT the value to a string based data type, and then REPLACE all the escape sequences you need to:

SELECT t.x,
       REPLACE(REPLACE(REPLACE(CONVERT(varchar(MAX),t.x),'&amp;','&'),'&gt;','>'),'&lt;','<')
FROM  (SELECT *
       FROM (VALUES ('Horse & Horse'))V(t)
       FOR XML PATH(''),TYPE) t(x)

Of course, if you try to CONVERT that value ('<t>Horse & Horse</t>') back to xml, you'll get an error:

XML parsing: line 1, character 11, illegal name character

So once you malform the XML, you will have to leave it as a string based data type, and you won't be able to consume it using SQL Server's XQuery tools.