Jieha Lee

Jieha Lee

  • 1.5k
  • 186
  • 8.9k

How to insert the XML file in DB

Jan 19 2018 3:48 AM
How to insert this XML file in SQL Server? I have done one but cannot output. The error said "String was not recognized as a valid DateTime"
 
Here the code that I done:
  1. SqlDataAdapter adpter = new SqlDataAdapter();  
  2. DataSet ds = new DataSet();  
  3. int appointment_ID = 0;  
  4. string nric = null;  
  5. string client_Name = null;  
  6. DateTime? appointment_Date = null;  
  7. DateTime? start_Time = null;  
  8. SqlConnection conn = new SqlConnection(@"Data Source=USER1-PC\SQLEXPRESS;Integrated Security=true;Database=ProductDB");  
  9. XmlReader xmlFile = XmlReader.Create("G://appointment.xml"new XmlReaderSettings());  
  10. ds.ReadXml(xmlFile);  
  11. int i = 0;  
  12. conn.Open();  
  13. for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)  
  14. {  
  15. appointment_ID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);  
  16. appointment_Date = DateTime.Parse(ds.Tables[0].Rows[i].ItemArray[1].ToString());  
  17. start_Time = DateTime.Parse(ds.Tables[0].Rows[i].ItemArray[2].ToString());  
  18. nric = ds.Tables[0].Rows[i].ItemArray[3].ToString();  
  19. client_Name = ds.Tables[0].Rows[i].ItemArray[4].ToString();  
  20. SqlCommand command = new SqlCommand("insert into Appointment values(" + appointment_ID + ",'" + appointment_Date + "'," + start_Time + "'," + nric + "'," + client_Name + ")", conn);  
  21. adpter.InsertCommand = command;  
  22. adpter.InsertCommand.ExecuteNonQuery();  
  23. }  
  24. conn.Close();  
  25. MessageBox.Show("Done .. ");  
Here is appointment.xml:
  1. <Interface>  
  2. <Appointment>  
  3. <Appointment_ID> 000000087508</Appointment_ID>  
  4. <Appointment_Date>2018-01-25</Appointment_Date>  
  5. <Appointment_Start_Time>13:15:00</Appointment_Start_Time>  
  6. <Client>  
  7. <NRIC>456987123</NRIC>  
  8. <Client_Name>JAMES</Client_Name>  
  9. </Client>  
  10. </Appointment>  
  11. <Appointment>  
  12. <Appointment_ID>000000087501</Appointment_ID>  
  13. <Appointment_Date>2018-01-25</Appointment_Date>  
  14. <Appointment_Start_Time>14:30:00</Appointment_Start_Time>  
  15. <Client>  
  16. <NRIC>963258741</NRIC>  
  17. <Client_Name>Bill Tan</Client_Name>  
  18. </Client>  
  19. <Client>  
  20. <NRIC>123456789</NRIC>  
  21. <Client_Name>David Ang</Client_Name>  
  22. </Client>  
  23. </Appointment>  
  24. <Checksum>2</Checksum>  
  25. </Interface>

Answers (1)