How to save xml data to database using mvc4

Question

I have created an xml file named SeedData.xml like this.

<Root>
    <User>
        <UserID>1</UserID>            
        <UserName>Admin</UserName>       
        <FirstName>Admin</FirstName>
        <LastName>Admin</LastName>
        <ShortName>Admin</ShortName>        
    </User>
</Root>

I have included the path in web.config like this.

<appSettings>    
    <add key="myFilePath" value="D:\XmlSample\Common\SeedData.xml"/>
  </appSettings>

I need to add the data in xml file to database.

I got the path of xml

string filePath = ConfigurationManager.AppSettings["myFilePath"];

But how can i extract values from xml and save in database.I searched a lot in google,but I was not able to find the solution. Pls help

I guess you need this http://stackoverflow.com/questions/2441673/reading-xml-with-xmlreader-in-c-sharp — ePezhman, Apr 17 '13 at 08:33

score 1 · Accepted Answer · answered Apr 17 '13 at 09:46

XML is a general purpose tag based language and very easy to transfer and store data across applications. The .Net technology is widely supported XML file format. The .Net Framework provides the Classes for read, write, and other operations in XML formatted files . Moreover the Dataset in ADO.NET uses XML format as its internal storage format.

Here we are going to insert the values of an XML file to a Database Table using SQL Insert Command . Here the Dataset using an XmlReader for read the content of the XML file. Locate the XML file using XmlReader and pass the XmlReader as argument of Dataset. Also establish a connection to the Database using a connectionstring . After getting the data from XML file to the Dataset , we can loop through the dataset values and use insert command to add the values to table in the Databse.

Try this

 using System;
    using System.Data;
    using System.Windows.Forms;
    using System.Xml;
    using System.Data.SqlClient; 

    namespace WindowsApplication1
    {
        public partial class Form1 : Form
        {
            public Form1()
            {
                InitializeComponent();
            }

            private void button1_Click(object sender, EventArgs e)
            {
                string connetionString = null;
                SqlConnection connection;
                SqlCommand command ;
                SqlDataAdapter adpter = new SqlDataAdapter();
                DataSet ds = new DataSet();
                XmlReader xmlFile ;
                string sql = null;

                int ID = 0;
                string Name = null;
                double Price = 0;

                connetionString = "Data Source=servername;Initial Catalog=databsename;User ID=username;Password=password";

                connection = new SqlConnection(connetionString);

                xmlFile = XmlReader.Create("Xmlfile.xml", new XmlReaderSettings());
                ds.ReadXml(xmlFile);
                int i = 0;
                connection.Open();
                for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
                {
                    ID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);
                    Name = ds.Tables[0].Rows[i].ItemArray[1].ToString();
                    Price = Convert.ToDouble(ds.Tables[0].Rows[i].ItemArray[2]);
                    sql = "insert into tablenamevalues(" + ID + ",'" + Name + "'," + Price + ")";
                    command = new SqlCommand(sql, connection);
                    adpter.InsertCommand = command;
                    adpter.InsertCommand.ExecuteNonQuery();
                }
                connection.Close();
                MessageBox.Show("Done .. ");
            }
        }
    }

How to save xml data to database using mvc4

1 Answers1