How to save xml file into xml column

Question

I have an xml file I want to save it's content into database to read it again.

I use XmlDocument to load the xml file and using InnerXml to save this xml.

This is my code

cmd.CommandText = @"INSERT INTO pages(BPMNID,XML) VALUES (@BPMNID,@XML)";
cmd.Parameters.AddWithValue("@BPMNID",2);
cmd.Parameters.AddWithValue("@XML", XDoc.InnerXml);

cmd.Connection = conn;
conn.Open();

try
{
    cmd.ExecuteNonQuery();
}

When run the previous code I get an exception

XML parsing :Unable to Switch the encoding

Hint: Xml column is a xml column

You should check out Can we stop using AddWithValue() already? and stop using .AddWithValue() - it can lead to unexpected and surprising results... — marc_s
– marc_s, Commented Sep 20, 2015 at 9:14
Load the XML from the file into a string and then pass that into the SQL query. The SQL query doesn't know about XmlDocument:.. — marc_s
– marc_s, Commented Sep 20, 2015 at 9:18

marc_s · Accepted Answer · 2015-09-20 09:49:46Z

1

Try to use code like this:

public void SaveXml(int id, string xmlFileName)
{
    // read your XML
    string xmlContent = File.ReadAllText(xmlFileName);

    // set up query
    string insertQuery = "INSERT INTO pages(BPMNID, XML) VALUES (@BPMNID, @XML)";

    using (SqlConnection conn = new SqlConnection(-your-connection-string-here-))
    using (SqlCommand cmd = new SqlCommand(insertQuery, conn))
    {
        // define parameters
        cmd.Parameters.Add("@BPMNID", SqlDbType.Int).Value = id;
        cmd.Parameters.Add("@XML", SqlDbType.VarChar, -1).Value = xmlContent;

        // open connection, execute query, close connection
        conn.Open();
        cmd.ExecuteNonQuery();
        conn.Close();
    }
}

edited Sep 20, 2015 at 9:49

answered Sep 20, 2015 at 9:26

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ahmed Over a year ago

are you mean ReadAllText ? not LoadAllText

Ahmed Over a year ago

I get the same exception

marc_s Over a year ago

@Ahmed: updated my code - using SqlDbType.VarChar, -1 seems to be working (at least for me), even with XML files containing the encoding="utf-8" in their header

Ahmed Over a year ago

Done, Thanks for your help

Collectives™ on Stack Overflow

How to save xml file into xml column

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related