T-SQL CONVERT function inconsistent with XML entities

• surreydude.

  SSChasing Mays

  Points: 601

  More actions

  August 27, 2014 at 6:20 am

  #292625

  I have the following data stored in a column in a table:

  £10,000.00 & ' " % < > Guðmundsdóttir Björk Lårs Marqués María-Jose Carreño Quiñones

  I query this in a stored procedure using the FOR XML clause and universal table, and store the result in an XML data type variable:

  DECLARE@ResultXML

  EXECUTE[someStoredProcedure] @Result OUTPUT

  This data (as XML) is then written to a file; in order to do this, I CONVERT the data to NVARCHAR since there are unicode characters in the source:

  DECLARE@strResultNVARCHAR(MAX)

  SET@strResult= CONVERT( NVARCHAR( MAX ), @Result, 1 )

  Now this works fine, except on inspection, SQLServer has decided to render the data thus:

  £10,000.00 & ' " % < > Guðmundsdóttir Björk Lårs Marqués María-Jose Carreño Quiñones

  Why it has changed the apos and quot entities to the corresponding character but not the other entities is beyond me.

  Has anyone come across this or have any ideas how to preserve XML entities?

  Thanks