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)
CodePudding user response:
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 & 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),'&','&'),'>','>'),'<','<')
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.