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.

Answer 2

When I read this post I thought it was the "end of the line"... no solution... I almost gave up on the approach... but in fact there is a way to work around this limitation by converting the XML to varchar(max) and then appending the declaration to the beginning of the string. The following post shows how:

Using SQL Server "FOR XML": Convert Result Datatype to Text/varchar/string whatever?

A simple example would look something like this:

SELECT 'MY DATA' As MyColumn INTO #MyTable 
SELECT '<?xml version="1.0" encoding="UTF-8"?>' + 
CAST((SELECT MyColumn FROM #MyTable FOR XML PATH('')) AS VARCHAR(MAX)) AS XmlData
DROP TABLE #MyTable 

The Output:

<?xml version="1.0" encoding="UTF-8"?>
<MyColumn>MY DATA</MyColumn>