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.
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.