You can use the ReadXML method DataSet or DataTable, to input the data from an XML file into a table in memory.
(Note: The example below uses an
untyped dataset but I would suggest using a
typed dataset or transfering the data to a
typed dataset after you have finished reading the file and formatting/validating the data as needed)
[COLOR="blue"]Dim [/COLOR]dsXml [COLOR="blue"]as New [/COLOR]DataSet
dsXml.ReadXml(strYourFile)
Now that the data is in your dataset/datatable you can loop thru each of the records and perform any needed formatting, validating, addition of data you need to your datatable before transferring the data to the database.
Once your done with all your validation & formatting there are different options available to you for transferring the data from your dataset/datatable to the database but Ive found using
sqlBulkCopy to be the fastest, specially if your import file is large.
("
Bulk Insert" could be used to transfer the data directly from the file to the database, but you loose the ability to format & validate the data and also the ability to use transactions and rollback the import if there is an error).
[COLOR="Blue"]Dim [/COLOR]conBatch [COLOR="blue"]As New [/COLOR]SqlConnection
[COLOR="blue"]Dim [/COLOR]trans [COLOR="blue"]As [/COLOR]SqlTransaction = Nothing
[COLOR="blue"]Try[/COLOR]
[COLOR="SeaGreen"]'Open db connection and assign transaction[/COLOR]
conBatch.ConnectionString = strYourConnectionStringHere
conBatch.Open()
trans = conBatch.BeginTransaction
[COLOR="seagreen"]'Bulk Copy records to Database[/COLOR]
[COLOR="blue"]Using [/COLOR]bcpPck [COLOR="blue"]As New [/COLOR]SqlBulkCopy(conBatch, SqlBulkCopyOptions.Default, trans)
bcpPck.BatchSize = 0
bcpPck.DestinationTableName = "tblNameHere"
bcpPck.WriteToServer(dsXml.Tables("tblNameHere"))
[COLOR="blue"]End Using[/COLOR]
trans.Commit()
[COLOR="blue"]Catch [/COLOR]ex [COLOR="blue"]As [/COLOR]Exception
trans.Rollback()
Messagebox.Show(ex.Message)
Return
[COLOR="blue"]Finally[/COLOR]
trans.Dispose()
conBatch.Close()
conBatch.Dispose()
[COLOR="blue"]End Try[/COLOR]
