Tech
Forums
Jobs
Books
Events
Videos
Live
More
Interviews
Certification
Training
Career
Members
News
Blogs
Contribute
An Article
A Blog
A Video
An Ebook
An Interview Question
Register
Login
1
Answer
How to insert the XML file in DB
Jieha Lee
6y
147
1
Reply
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
>
Post
Reset
Cancel
Answers (
1
)
Next Recommended Forum
how to download the image in asp.net.Image stored in db?
checkboxlist select query in j query