XML Path expression to include Special Characters



I am trying this SQL to get the firstname and lastname from SQL Server 2008 tables using XML Path expression. The data contains special characters. When I try the sql, I get an error the following error:



FOR XML could not serialize the data for node 'LastName' because it contains a character (0x001B) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive



How can I rewrite the SQL to include these characters in the xml ( maybe as CDATA?)



SELECT (
SELECT A1.FirstName
, A1.LastName
FROM dbo.kc_consumer AS A1
FOR XML PATH('Consumer') , TYPE)
AS ConsumerData
FOR XML PATH('Element'), ROOT('Elements')

No comments:

Post a Comment