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
– marc_s, Commented Feb 13, 2017 at 5:22

Community · Accepted Answer · 2017-05-23 11:53:14Z

0

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!

edited May 23, 2017 at 11:53

CommunityBot

11 silver badge

answered Feb 13, 2017 at 5:04

N00b Pr0grammer

4,6676 gold badges36 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Generate XML in encoding UTF-8 using SQL Server

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related