001priyank 001priyank - 2 months ago 12x
SQL Question

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

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

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



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

gbn gbn

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.