adding encoding information to the result of FOR XML

Question

I have a Script, which returns a XML using FOR XML in SQL 2008. Is there any way to add the version and encoding information in the beginning of the output. Eventually, i am planning to save the output in a file.

For example, right now my output looks like this

<Agents>
  <Agent id="1">
    <Name>Mike</Name>
    <Location>Sanfrancisco</Location>
  </Agent>
  <Agent id="2">
    <Name>John</Name>
    <Location>NY</Location>
  </Agent>
</Agents>

I would like to append the line <?xml version="1.0" encoding="UTF-8"?> in the beginning of the Xml output

So i want the output something like

<?xml version="1.0" encoding="UTF-8"?>
<Agents>
 <Agent id="1">
   <Name>Mike</Name>
   <Location>Sanfrancisco</Location>
 </Agent>
<Agent id="2">
  <Name>John</Name>
  <Location>NY</Location>
</Agent>

score 3 · Answer 1 · edited May 23 '17 at 11:52

As @gbn points out in another answer and on another question, "the XML data is stored internally as ucs-2", and SQL Server doesn't include it when producing the data. However, you can convert the XML to a string and append the XML declaration at the beginning manually. However, simply using UTF-8 in the declaration would be inaccurate. The Unicode string which SQL produces is in UCS-2. For example, this will fail:

SELECT CONVERT(xml,N'<?xml version="1.0" encoding="UTF-8"?>' + CONVERT(NVARCHAR(MAX),CONVERT(XML,N'<x>' + NCHAR(10176) + N'</x>')));

with error:

Msg 9402, Level 16, State 1, Line 1 XML parsing: line 1, character 38, unable to switch the encoding

This, on the other hand, will work as expected:

SELECT CONVERT(xml,N'<?xml version="1.0" encoding="UCS-2"?>' + CONVERT(NVARCHAR(MAX),CONVERT(XML,N'<x>' + NCHAR(10176) + N'</x>')));

Here is code which will produce the full, declaration-laden XML string you seek for your example data:

DECLARE @Agents TABLE
(
    AgentID int,
    AgentName nvarchar(50),
    AgentLocation nvarchar(100)
);
INSERT INTO @Agents (AgentID, AgentName, AgentLocation) VALUES (1, N'Mike', N'Sanfrancisco');
INSERT INTO @Agents (AgentID, AgentName, AgentLocation) VALUES (2, N'John', N'NY');

WITH BaseData AS
(
    SELECT
        (
            SELECT
                AgentID AS '@id',
                AgentName AS 'Name',
                AgentLocation AS 'Location'
            FROM    @Agents
            FOR     XML PATH('Agent'), ROOT('Agents'), TYPE
        ) AS AgentXML
), FullStringTable AS
(
    SELECT
        *,
        '<?xml version="1.0" encoding="UCS-2"?>' +
        CONVERT(nvarchar(max),AgentXML) AS FullString
    FROM        BaseData
)
SELECT
    AgentXML AS OriginalXML,
    FullString,
    CONVERT(xml,FullString) AS FullStringConvertedToXML
FROM        FullStringTable;

gbn · Answer 2 · 2012-01-25T12:13:33.027

1

SQL Server internally always uses ~~utf-16~~ ucs-2 so you could just append it like we did. That is, SQL Server would never generate anything with "utf-8".

Edit: after some digging:

edited Jan 25 '12 at 12:13

answered Jun 02 '11 at 13:28

gbn

422,506
82
585
676

score 0 · Answer 3 · answered Jun 02 '11 at 16:54

If you will not be attempting to manipulate the results as TSQL XML then the simplest thing to do will be create a varchar with the string you wish to append then add the XML to it using a CAST statemnt to convert the XML to varchar.

declare @testXML as XML
declare @testPrefix as varchar(255)
set @testPrefix = '<?xml version="1.0" encoding="UTF-8"?>'
set @testXML = '<Agents>  <Agent id="1">    <Name>Mike</Name>    <Location>Sanfrancisco</Location>  </Agent>  <Agent id="2">    <Name>John</Name>    <Location>NY</Location>  </Agent></Agents>'

select @testPrefix
Select @testXML
select @testPrefix + CAST(@testXML as varchar(max))

adding encoding information to the result of FOR XML

3 Answers3

Linked