Export data from XML to SQL Server

Some times  we need that we want to transfer the data from a XML file to the sql server.I know that there are several ways to do that.But this methods is quite simple and sort and obviously very fast.

Only we need to read xml file into a dataset then simply write this data to SQL server by SQLBulkCopy Command.And you are done.


        DataSet ds = new DataSet();
        ds.ReadXml(Server.MapPath("HotelList.xml"));       
        DataTable dt = ds.Tables[0];
       // dt.Columns.Remove("HotelID");
       // dt.Columns.Remove("HotelFileName");
       // dt.Columns.Remove("HotelName");
       // dt.Columns.Remove("CityID");
       // dt.Columns.Remove("CityFileName");
       // dt.Columns.Remove("CityName");
       // dt.Columns.Remove("StateID");
       // dt.Columns.Remove("StateFileName");
       // dt.Columns.Remove("StateName");
       //dt.Columns.Remove("CountryCode");
       // dt.Columns.Remove("CountryFileName");
       // dt.Columns.Remove("CountryName");
       // dt.Columns.Remove("FolderName");
       // dt.Columns.Remove("FileName");
       // dt.AcceptChanges();
        string strconnection = "Data Source=Shert-PC;Initial Catalog=hotel;Integrated Security=True";
        SqlConnection con = new SqlConnection(strconnection);
        con.Open();
        SqlBulkCopy sbc = new SqlBulkCopy(con);
        sbc.DestinationTableName = "tbl_xml"; 
        sbc.BulkCopyTimeout = 100000000;
        sbc.WriteToServer(dt);
        con.Close();