Getting an Error when inserting xmlfile to database

Question

I'm trying to insert a xml file that I read from a stream into a column of type xml in mssql server like this:

//read file from stream

                    var reader = new StreamReader(request.InputStream, Encoding.UTF8);
                    var string = reader.ReadToEnd();

//convert to xmldoc

                    XmlDocument xmlDoc = new XmlDocument();
                    xmlDoc.LoadXml(string);

//Try to inser it into the table using the xmlDoc

        using (SqlConnection con = new SqlConnection(_connectionString))
        {
            con.Open();
            using (SqlCommand cmd = new SqlCommand(
                @"INSERT INTO [XMLTable] (XmlData) VALUES(@XmlData);", con))
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@XmlData", xmlDoc.InnerXml);

                cmd.ExecuteScalar();
             
            }
        }

//Sample xml :

    <?xml version="1.0" encoding="utf-8"?>
<ChangeSSNR version="1.0" xmlns="http://schemas.testschema.com/ChangeSSNR/1.0/">
  <Header version="1.0">
    <From>someone</From>
    <To>someoneelse</To>
    <TimeStamp>1900-01-01T01:01:01+01:00</TimeStamp>
    <ppnSchema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:nil="true" />
  </Header>
  <ChangeSSNRid id="4294967295">
    <SSNRChange>
      <NewSSNR>09834098098</NewSSNR>
      <OldSSNR>9879879827345</OldSSNR>
    </SSNRChange>
  </ChangeSSNRid>
  <ChangeSSNRid id="42949367295">
    <SSNRChange>
      <NewSSNR>098340980983</NewSSNR>
      <OldSSNR>98798798273453</OldSSNR>
    </SSNRChange>
  </ChangeSSNRid>
</ChangeSSNR>

Getting an exception : XML parsing: line 1, character 38, unable to switch the encoding

Kind Regards

/Rudy

Answer 1

The XML file shouldn't have any leading spaces on the first line:

XML

<?xml version="1.0" encoding="utf-8"?>
<ChangeSSNR version="1.0" xmlns="http://schemas.testschema.com/ChangeSSNR/1.0/">
    <Header version="1.0">
        <From>someone</From>
        <To>someoneelse</To>
        <TimeStamp>1900-01-01T01:01:01+01:00</TimeStamp>
        <ppnSchema xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                   xsi:nil="true"/>
    </Header>
    <ChangeSSNRid id="4294967295">
        <SSNRChange>
            <NewSSNR>09834098098</NewSSNR>
            <OldSSNR>9879879827345</OldSSNR>
        </SSNRChange>
    </ChangeSSNRid>
    <ChangeSSNRid id="42949367295">
        <SSNRChange>
            <NewSSNR>098340980983</NewSSNR>
            <OldSSNR>98798798273453</OldSSNR>
        </SSNRChange>
    </ChangeSSNRid>
</ChangeSSNR>

It is better to use LINQ to XML while dealing with XML. It is available in the .Net Framework since 2007.

c#

...
XDocument xdoc = XDocument.Parse(string);
...
cmd.Parameters.AddWithValue("@XmlData", xdoc.ToString());