How to import XML data in c#

Question

i want to import xml data using C# dot net to the SQLSERVER 2000 any one post solution for this I have an XML file like this below i mentioned - - 452 - --> COICLT0001 VIL COIVIL0001 SHG1 10120aa BALA RF 500 600 - 453 - --> COICLT0001 VIL COIVIL0001 SHG2 10120BB BALAJI DL 300 500

Satyapriya Nayak · Answer

Hi Raj kumar Try this... Untitled Page using System; using System.Configuration; using System.Data; using System.Linq; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.HtmlControls; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Xml.Linq; using System.Data.SqlClient; public partial class _Default : System.Web.UI.Page { string connStr = ConfigurationManager.ConnectionStrings[ConnectionString].ConnectionString; protected void Button1_Click(object sender EventArgs e) { SqlConnection con = new SqlConnection(connStr); DataSet reportData = new DataSet(); reportData.ReadXml(Server.MapPath(test.xml)); SqlBulkCopy sbc = new SqlBulkCopy(con); sbc.DestinationTableName =employee; sbc.ColumnMappings.Add(TALLY_REC_ID_CODE empid); sbc.ColumnMappings.Add(CLUSTER_ID empname); sbc.ColumnMappings.Add(UNIT_TYPE empaddress); sbc.ColumnMappings.Add(VPRC_ID empcity); sbc.ColumnMappings.Add(SHG_ID empstate); sbc.ColumnMappings.Add(PIP_NO empzip); sbc.ColumnMappings.Add(NAME_OF_THE_MEMBER emplic); sbc.ColumnMappings.Add(TYPE_OF_LOAN empstatus); sbc.ColumnMappings.Add(PRINCIPAL empp); sbc.ColumnMappings.Add(INTEREST empi); con.Open(); sbc.WriteToServer(reportData.Tables[0]); con.Close(); Label1.Text = Records inserted successfully; } } Thanks

Rajkumar R · Answer

reportData.ReadXml(Server.MapPath(report.xml)); instead of the above code i want to select and upload the xml.file. but here it is statically declared as report.xml My need is i m having an fileupload control when i select a file then i want to insert the corresponding XML file into the table

Rajkumar R · Answer

Thanks a lot for ur response satyapriya

Rajkumar R · Answer

i have solved the error but one more help please i want to browse the file and to upload that how can select the file using fileupload control and to upload that xml file in sqlserver

Rajkumar R · Answer

i have an FileUpload control from this i will brose my xml file then i want that into sqlserver 2000 using C# post solution for this

Datta Kharad · Answer

Hi You can try this code.. class Program { private static void SaveXmlToDatabase (DbConnection connection XmlDocument xmlToSave) { String sql = INSERT INTO xmlTable(xmlColumn) VALUES (@xml); using (DbCommand command = connection.CreateCommand()) { XPathNavigator nav = xmlToSave.CreateNavigator(); string xml = nav.SelectSingleNode(/catalog/cd[title='Manowar']).InnerXml; command.CommandText = sql; command.Parameters.Add( new SqlParameter(@xml SqlDbType.Xml) {Value = new SqlXml(new XmlTextReader(xml  XmlNodeType.Document null)) }); DbTransaction trans = connection.BeginTransaction(); try { command.ExecuteNonQuery(); trans.Commit(); } catch (Exception) { trans.Rollback(); throw; } } } static void Main(string[] args) { XmlDocument document = new XmlDocument(); document.Load(args.First()); SqlConnection connection = new SqlConnection( Server=myServerAddress;Database=myDataBase;Trusted_Connection=True;); SaveXmlToDatabase (connection document); connection.Close(); } }