Save XML data to SQL Server table

Question

I have this xml file, and I want to save value NUMBER (for example) to a SQL Server table.

<ORDER>
  <ORDER_HEADER>
    <NUMBER>10945</NUMBER>
    <TIME>7.8.2013 12:45:20</TIME>
    <NOTE>this is Note</NOTE>   
  </ORDER_HEADER>
</ORDER>

This is my code:

XDocument doc = XDocument.Load("C:\\Users\\L\\Desktop\\data.xml");
var NUMBER = doc.Descendants("NUMBER");
var TIME = doc.Descendants("TIME");
var NOTE = doc.Descendants("NOTE");

foreach (var cislo in NUMBER)
{
    SqlConnection conn = new SqlConnection("Data Source=***");
    conn.Open();

    using (SqlCommand cmd = conn.CreateCommand())
    {
       cmd.CommandText = "Update CISLO SET cislo = @cislo1;";
       cmd.Parameters.AddWithValue("@cislo1", doc);

       cmd.ExecuteNonQuery();
    }
 }

 MessageBox.Show("OK");

I get this error:

There is no mapping from object type System.Xml.Linq.XDocument to a known managed provider native type.

On row:

cmd.ExecuteNonQuery();

jle · Accepted Answer · 2013-08-26 08:19:20Z

3

You are passing 'doc', which is your XDocument, into the parameter. Try changing

cmd.Parameters.AddWithValue("@cislo1", doc);

to

cmd.Parameters.AddWithValue("@cislo1", cislo);

answered Aug 26, 2013 at 8:19

jle

9,5095 gold badges50 silver badges68 bronze badges

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

3 Comments

MarcinJuraszek Over a year ago

probably should be cislo.Value

Kate Over a year ago

YES!! cmd.Parameters.AddWithValue("@cislo1", cislo.Value);

jle Over a year ago

Good call, @MarcinJuraszek

Collectives™ on Stack Overflow

Save XML data to SQL Server table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related