How to add xml encoding <?xml version="1.0" encoding="UTF-8"?> to xml Output in SQL Server

Question

Probably a duplicate of unanswered. SQL Server 2008 - Add XML Declaration to XML Output

Please let me know if this is possible. I read in some blogs

http://forums.asp.net/t/1455808.aspx/1

http://www.devnewsgroups.net/group/microsoft.public.sqlserver.xml/topic60022.aspx

But I couldn't understand why I can't do this.

Answer 1

You have to add it manually. SQL Server always stores xml internally as ucs-2 so it is impossible for SQL to generate it a utf-8 encoding header

See "Limitations of the xml Data Type" on MSDN

The XML declaration PI, for example, <?xml version='1.0'?>, is not preserved when storing XML data in an xml data type instance. This is by design. The XML declaration (<?xml ... ?>) 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.