CODEWITHSUNDEEP
c#xmlsql-server-2008
Ahmed
Ahmed

Reputation: 305

How to save xml file into xml column

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

Upvotes: 2

Views: 7197

Answers (1)

marc_s
marc_s

Reputation: 755541

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();
    }
}

Upvotes: 1

Related Questions