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:
- SqlDataAdapter adpter = new SqlDataAdapter();
- DataSet ds = new DataSet();
- int appointment_ID = 0;
- string nric = null;
- string client_Name = null;
- DateTime? appointment_Date = null;
- DateTime? start_Time = null;
- SqlConnection conn = new SqlConnection(@"Data Source=USER1-PC\SQLEXPRESS;Integrated Security=true;Database=ProductDB");
- XmlReader xmlFile = XmlReader.Create("G://appointment.xml", new XmlReaderSettings());
- ds.ReadXml(xmlFile);
- int i = 0;
- conn.Open();
- for (i = 0; i <= ds.Tables[0].Rows.Count - 1; i++)
- {
- appointment_ID = Convert.ToInt32(ds.Tables[0].Rows[i].ItemArray[0]);
- appointment_Date = DateTime.Parse(ds.Tables[0].Rows[i].ItemArray[1].ToString());
- start_Time = DateTime.Parse(ds.Tables[0].Rows[i].ItemArray[2].ToString());
- nric = ds.Tables[0].Rows[i].ItemArray[3].ToString();
- client_Name = ds.Tables[0].Rows[i].ItemArray[4].ToString();
- SqlCommand command = new SqlCommand("insert into Appointment values(" + appointment_ID + ",'" + appointment_Date + "'," + start_Time + "'," + nric + "'," + client_Name + ")", conn);
- adpter.InsertCommand = command;
- adpter.InsertCommand.ExecuteNonQuery();
- }
- conn.Close();
- MessageBox.Show("Done .. ");
Here is appointment.xml:
- <Interface>
- <Appointment>
- <Appointment_ID> 000000087508</Appointment_ID>
- <Appointment_Date>2018-01-25</Appointment_Date>
- <Appointment_Start_Time>13:15:00</Appointment_Start_Time>
- <Client>
- <NRIC>456987123</NRIC>
- <Client_Name>JAMES</Client_Name>
- </Client>
- </Appointment>
- <Appointment>
- <Appointment_ID>000000087501</Appointment_ID>
- <Appointment_Date>2018-01-25</Appointment_Date>
- <Appointment_Start_Time>14:30:00</Appointment_Start_Time>
- <Client>
- <NRIC>963258741</NRIC>
- <Client_Name>Bill Tan</Client_Name>
- </Client>
- <Client>
- <NRIC>123456789</NRIC>
- <Client_Name>David Ang</Client_Name>
- </Client>
- </Appointment>
- <Checksum>2</Checksum>
- </Interface>