Generate XML in encoding UTF-8 using SQL Server

Question

I want to generate an XML in UTF-8 encoding. But by default it is generating in UCS-2.

Please help me to generate XML in UTF-8 encoding. Below is my query:

select
    isnull(cast('5678' as nvarchar(50)),'') [Vlootnummer],
    isnull((select top 1 cast(EngineNo as nvarchar(50)) 
            from VTS_DEMO.dbo.VehicleDetails v
            join VTS_DEMO.dbo.VehicleDevice vd on v.VehicleId = vd.VehicleId 
                                               and ObuID = '353234023894171'), '') as Kenteken,
    isnull((select top 1 cast(FillingStationName as nvarchar(50)) Units 
            from VTS_DEMO.dbo.FillingStation 
            where GeoFenceId = 3655),'') Locatie,
    isnull((select top 1 GeofenceCode 
            from VTS_DEMO.dbo.GeoFence 
            where GeoFenceId = 3655), '') GeoFencingID,
    isnull(cast(case when 1 = 0 then '' else '2017-02-07T23:15:25Z' end as nvarchar(50)),'') Aankomsttijd,
    isnull(cast(case when 1 = 0 then '2017-02-07T23:15:25Z' else NULL end as nvarchar(50)),'') Vertrektijd
FOR XML PATH('Notificatie')

When I send this XML as attachment in mail using stored procedure msdb.dbo.sp_send_dbmail, when its opened in notepad++ then it shows UCS-2.

SQL Server (up to and including version 2016) does **NOT support** UTF-8 ... — marc_s, Feb 13 '17 at 05:22

score 0 · Answer 1 · edited May 23 '17 at 11:53

As per the MSDN documentation, It is a limitation on the XML datatype on MS SQL Server. SQL Server always saves an XML datatype with the character encoding of UCS-2.

The XML declaration PI, for example, , is not preserved when storing XML data in an xml data type instance. This is by design. The XML declaration () and its attributes (version/encoding/stand-alone) are lost after data is converted to type xml. The XML declaration is treated as a directive to the XML parser. The XML data is stored internally as ucs-2. All other PIs in the XML instance are preserved.

Considering the above, you can add this manually to take affect. In an attempt to do this, you can use the following to gain advantage of FOR XML

SELECT CAST((SELECT [Columns] FROM [Tables] FOR XML AUTO) AS VARCHAR(MAX)) AS xmldata

You can check this answer for a discussion similar to this.

Hope this helps!

Generate XML in encoding UTF-8 using SQL Server

1 Answers1