10

Folks,

I have a webservice that returns data in ISO-8859-1 encoding - since it's not mine, I can't change that :-(

For auditing purposes, I'd like to store the resulting XML from these calls into a SQL Server 2005 table, in which I have a field of type "XML NULL".

From my C# code, I try to store this XML content into the XML field using a parametrized query, something like

SqlCommand _cmd = new SqlCommand("INSERT INTO dbo.AuditTable(XmlField) VALUES(@XmlContents)", _connection);

_cmd.Parameters.Add("@XmlContents", SqlDbType.Xml);
_cmd.Parameters["@XmlContents"].Value = (my XML response);

_cmd.ExecuteNonQuery();

Trouble is - when I run this code, I get back an error:

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

?? I was trying to figure out where and how I could possibly "switch" the encoding - no luck so far. What does this really mean? I cannot store XML with ISO-8859-1 encoding in SQL Server 2005?? Or is there a trick to a) tell SQL Server 2005 to just accept this encoding, or b) to automagically convert the webservice response to UTF encoding before storing in SQL Server?

Thanks for any hints, pointers, tips! Marc

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • FYI to readers - near duplicates: http://stackoverflow.com/questions/1564718/using-stringwriter-for-xml-serialization and http://stackoverflow.com/questions/1564718/using-stringwriter-for-xml-serialization – ziesemer Jan 25 '12 at 04:58

6 Answers6

5

You need to convert to utf-16

I'm not an expert on XML in SQL Server even though I use it, but we had the same problem last year and it was mis-match of the string datatype declared in SQL compared to the xml being sent.

gbn
  • 422,506
  • 82
  • 585
  • 676
1

Edit
I missed the ISO-8859-1 part of the question - the solution below is good for UTF8, but obviously doesn't solve Marc's problem as he can't alter the encoding.

Here's the solution I use:

And a slightly modified version of the code from above (I've tested it with a UTF8 file using SQL 2005):

using System.IO;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;

...
using (SqlConnection connection = new SqlConnection("conn string"))
{
    connection.Open();
    string sql = "INSERT INTO mytable (xmlColumn) VALUES (@xmlData)";
    using (SqlCommand command = new SqlCommand(sql, connection))
    {
        // Swap round if the source file is unicode         
        string xml = File.ReadAllText(@"C:\myxml.xml");
        //string xml = File.ReadAllText(@"C:\myxml.xml", Encoding.Unicode);

        using (MemoryStream stream = new MemoryStream())
        {
            using (StreamWriter writer = new StreamWriter(stream, Encoding.Unicode))
            {
                writer.Write(xml);
                writer.Flush();
                stream.Position = 0;

                SqlParameter parameter = new SqlParameter("@xmlData", SqlDbType.Text);
                parameter.Value = new SqlXml(stream);
                command.Parameters.Add(parameter);
                command.ExecuteNonQuery();
            }
        }
    }
}
Chris S
  • 64,770
  • 52
  • 221
  • 239
  • Did you try this where the input file had ISO-8859-1 encoding? Also, you're missing "using" statements, so -1. – John Saunders Jul 07 '09 at 09:16
  • John, as I'm sure you know MemoryStream.Dispose does nothing so there's no gain there, flushing the writer (i.e. writer.Dispose) would leave the SqlCommand with a disposed Writer. If I performed the execute inside the Writer's using() then the Writer would not be flushed and so empty, which is my reasoning for why I've done it like above. The code expects a UTF16 file so it wouldn't work with IS0-8859 (which is UTF8 as far as I know). – Chris S Jul 07 '09 at 14:37
  • Missed the ISO-8859-1 part of the question, my mistake – Chris S Jul 08 '09 at 16:29
  • MemoryStream.Dispose may do nothing _today_. That's in implementation detail you should not depend on, ever. Don't end using until ExecuteNonQuery is finished, of course. And you need to set the stream position back to 0. – John Saunders Jul 08 '09 at 16:33
  • Ok fair enough, I've taken the advice and updated it. The code before was running (runs faster now strangely though). – Chris S Jul 08 '09 at 16:46
  • The reader SqlXml uses sets the stream position incidently, but that's along the lines of what you mentioned about MemoryStream.Dispose – Chris S Jul 08 '09 at 16:51
  • ...and true to OOP best practices, it now ignores the framework implementations of IDisposable and disposes of it all. Hopefully it works as I have only compiled it on snipper compiler, I'm not able to test it on this pc. – Chris S Jul 08 '09 at 22:34
  • Much better. Just need one more "using" around the SqlCommand. – John Saunders Jul 09 '09 at 13:44
  • you're a strict task master John! added dispose for the DbCommand which I didn't spot – Chris S Jul 09 '09 at 13:47
  • This is very similar to http://stackoverflow.com/a/1566154/751158. Why are you passing `SqlDbType.Text`? If you're going to pass it at all, shouldn't it be `SqlDbType.Xml` (as the OP already had)? – ziesemer Jan 25 '12 at 04:52
  • I can only vote this up once, but this deserves to be the top answer here. In the end, it doesn't matter what encoding is declared or used, as long as the `XmlReader` can parse it. It will be sent pre-parsed to the database, and then the DB doesn't need to know anything about character encodings - UTF-16 or otherwise. In particular, note that the XML declarations aren't even persisted with the data in the database, regardless of which method is used to insert it. Please don't make waste by running XML through extra conversions, as shown in other answers here and elsewhere. – ziesemer Jan 25 '12 at 04:59
1

Even I faced similar issue while inserting xml content to db. For ex , input was like this:

Insert Into TestData(Xml) Values ('<?xml version="1.0" encoding="UTF-8"?><Test/>')

This kind of statement used to fail and I was getting "unable to switch .." error. Later I simply prefixed N to xml string like this : 

Insert Into TestData(Xml) Values (N'<?xml version="1.0" encoding="UTF-8"?><Test/>')

After this it started working !!!

Karthik D V
  • 906
  • 1
  • 11
  • 19
1

I found this on google. http://social.msdn.microsoft.com/forums/en-US/sqlxml/thread/d40ef582-4ffe-4f4b-b6b8-03c6c0ba1a32/

I think you can replace the line

_cmd.Parameters.Add("@XmlContents", SqlDbType.Xml);

with

_cmd.Parameters.Add("@XmlContents", System.Data.SqlTypes.SqlXml);
Nathan Koop
  • 24,803
  • 25
  • 90
  • 125
  • 1
    Hi Nathan, doesn't seem to work - I get a compile time error: Error 2 'System.Data.SqlTypes.SqlXml' is a 'type', which is not valid in the given context Ideas?? – marc_s Dec 22 '08 at 19:28
1

Could you possibly re-write the xml as unicode (perhaps to a MemoryStream) and send that? Note: if you are just storing the data, you can use varbinary(max) (and it will actually be quicker). This has no encoding difficulties, and will also allow you to audit any corrupt xml that you receive.

If you are querying the data as xml inside the database server then xml is obviously the way to go.

Marc Gravell
  • 1,026,079
  • 266
  • 2,566
  • 2,900
  • Thanks for the tip - but since I want to query and report on the data using XQuery, I do need to keep it as XML inside SQL Server 2005. – marc_s Dec 21 '08 at 21:46
  • Then perhaps look at simply re-writing the xml to chaneg the encoding before sending it to the server. – Marc Gravell Dec 21 '08 at 21:50
1

I posted some info:

http://mrboratech.blogspot.com/2009/02/how-to-store-xmldocument-mssql-2005-as.html