How to insert the XML file in DB

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> 000000087508Appointment_ID>
<Appointment_Date>2018-01-25Appointment_Date>
<Appointment_Start_Time>13:15:00Appointment_Start_Time>
<Client>
<NRIC>456987123NRIC>
<Client_Name>JAMESClient_Name>
Client>
Appointment>
<Appointment>
<Appointment_ID>000000087501Appointment_ID>
<Appointment_Date>2018-01-25Appointment_Date>
<Appointment_Start_Time>14:30:00Appointment_Start_Time>
<Client>
<NRIC>963258741NRIC>
<Client_Name>Bill TanClient_Name>
Client>
<Client>
<NRIC>123456789NRIC>
<Client_Name>David AngClient_Name>
Client>
Appointment>
<Checksum>2Checksum>
Interface>

Hello

I have solved your issue , please find following snippet :

public ActionResult Index()
{
SqlDataAdapter adpter = new SqlDataAdapter();
DataSet ds = new DataSet();
int appointment_ID = 0;
string nric = null;
string client_Name = null;
DateTime appointment_Date ;
DateTime start_Time ;
SqlConnection conn = new SqlConnection(@"Data Source=kdpl36\sqlexpress16,2433;Initial Catalog=iWebSquare_DB_TC;User ID=ecmusr;Password=Sql#2703");
XmlReader xmlFile = XmlReader.Create("c:/users/manav/documents/visual studio 2013/Projects/XMLInsertToDB/XMLInsertToDB/demo.xml", new XmlReaderSettings());
ds.ReadXml(xmlFile);
int i = 0;
conn.Open();
try
{
for (i = 0; i <= ds.Tables[1].Rows.Count - 1; i++)
{
appointment_ID = Convert.ToInt32(ds.Tables[1].Rows[i].ItemArray[0]);
appointment_Date = Convert.ToDateTime(ds.Tables[1].Rows[i].ItemArray[1]);
start_Time = Convert.ToDateTime(ds.Tables[1].Rows[i].ItemArray[2]);
nric = ds.Tables[2].Rows[i].ItemArray[0].ToString();
client_Name = ds.Tables[2].Rows[i].ItemArray[1].ToString();
SqlCommand command = new SqlCommand("insert into Appointment(appid,date,starttime,nric,name) values(" + appointment_ID + ",'" + appointment_Date + "','" + start_Time + "','" + nric + "','" + client_Name + "')", conn);
adpter.InsertCommand = command;
adpter.InsertCommand.ExecuteNonQuery();
}
}
catch(Exception e)
{
}
conn.Close();
return View();
}

Thanks

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Manav Pandya8y ago
Accepted answer
Hello

I have solved your issue , please find following snippet :
public ActionResult Index()
        {
            SqlDataAdapter adpter = new SqlDataAdapter();
            DataSet ds = new DataSet();
            int appointment_ID = 0;
            string nric = null;
            string client_Name = null;
            DateTime appointment_Date ;
            DateTime start_Time ;
            SqlConnection conn = new SqlConnection(@"Data Source=kdpl36\sqlexpress16,2433;Initial Catalog=iWebSquare_DB_TC;User ID=ecmusr;Password=Sql#2703");
            XmlReader xmlFile = XmlReader.Create("c:/users/manav/documents/visual studio 2013/Projects/XMLInsertToDB/XMLInsertToDB/demo.xml", new XmlReaderSettings());
            ds.ReadXml(xmlFile);
            int i = 0;
            conn.Open();
            try
            {
                for (i = 0; i <= ds.Tables[1].Rows.Count - 1; i++)
                {
                    appointment_ID = Convert.ToInt32(ds.Tables[1].Rows[i].ItemArray[0]);
                    appointment_Date = Convert.ToDateTime(ds.Tables[1].Rows[i].ItemArray[1]);
                    start_Time = Convert.ToDateTime(ds.Tables[1].Rows[i].ItemArray[2]);
                    nric = ds.Tables[2].Rows[i].ItemArray[0].ToString();
                    client_Name = ds.Tables[2].Rows[i].ItemArray[1].ToString();
                    SqlCommand command = new SqlCommand("insert into Appointment(appid,date,starttime,nric,name) values(" + appointment_ID + ",'" + appointment_Date + "','" + start_Time + "','" + nric + "','" + client_Name + "')", conn);
                    adpter.InsertCommand = command;
                    adpter.InsertCommand.ExecuteNonQuery();
                }
            }
            catch(Exception e)
            {

            }

            conn.Close();
            return View();
        }
Thanks