XML Encoding from XmlTextWriter won't go into SQL Server

[gdrenfrew]

Hello all,

I'm creating some XML in my project using the XMLTextWriter, and outputting it to a string via a memory stream so insert it into SQL Server 2000 using the OPENXML functionality in a stored procedure.

When I try to run the stored procedure I get this error:
"Server: Msg 6603, Level 16, State 1, Procedure sp_xml_preparedocument, Line 22
XML parsing error: Switch from current encoding to specified encoding not supported."

When I look at the sXmlString before I send it to the stored procedure, it looks something like this:
?<?xml version="\1.0\" encoding="\utf-8\"?>\r\n<RefreshData> <Portfolio_Records><data.... ></RefreshData>

When I (manually) remove the "encoding" declaration, the leading "?" and all the "\r\n" etc from the XML it inserts fine. How can I remove these characters programmatically? Or can I encode it in such a way so these characters don't appear? Or am I going about this the wrong way?

 

[obislavu]

The IOM encoding mechanism on SQL does not recognize utf-8.
You are right, remove the first line and all "\r\n" from the string.

string.Replace('\r','');
StringBuilder.Replace('\r','');

obislavu

 

[gdrenfrew]

Thanks for the reply.

I ended up putting the contents of the memorystream through a bogstandard StreamReader to get the output as UTF-16

MemoryStream ms = new MemoryStream();
XmlTextWriter xTW = new XmlTextWriter(ms,System.Text.Encoding.Unicode);

{build the document in the XmlTextWriter etc etc}
.
.
.
.

xTW.Flush();
ms.Flush();
ms.Position = 0;

string sOutput;
sOutput = new System.IO.StreamReader(ms).ReadToEnd();

The output came out as RTF-16 which is what I need to insert it via OPENXML into my SQL Server database