Import XML into MS Access table

barrett.mcguire

I can successfully export a DataSet from vb.net to an XML file. I need to be able to import the XML file, containing the DataSet, into an MS Access table. The MS access table already exists and its structure is an exact duplicate of the the table that was used for the original export, but is blank--just waiting for data. Thanks in advance

4 Replies

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

MythicalMe21y ago
No. The whole XML file is read into the dataset at once. The problem is that the rows in the data table are not being marked as edited. So I would create a datarow object and iterate through the table and use the BeginEdit method followed up immediately by the EndEdit method.
barrett.mcguire21y ago
Do I have to add the records row by row? Do I need to provide any mapping from the XML file to the database table? Thanks in advance
barrett.mcguire21y ago
Thanks for the info. When I update, no errors occur, but the data is not written to the database table. This is what I have done: Created Table in MSAccess called "Matt"--contains primary key and no data. Table was created by copying the original table that the XML import was created from. 1. Created OLEDBAdapter 2. Configured the adapter per the wizard (sql statement = "Select * from Matt") 3. Right-clicked the adapter in design view and selected "Generate DataSet" 4. Created a button on a form with the following code: DataSetXMLImport1.ReadXml("Matt.xml") 'DataSetXMLImport1.WriteXml("Crap.xml") OleDbDataAdapter1.Update(DataSetXMLImport1.Tables("Matt")) I know that the DataSet is populated through the ReadXML because I can open the file "Crap.xml" created with WriteXML. Below is a subset of my XML file: - - 37 A bunch of Stuff. More Stuff. - - 37 A bunch of Stuff. More Stuff. - -
MythicalMe21y ago
You'll need to use the ODBCDataAdapter to write the data to MS Access. When you drag the data adapter from the toolbox to the designer a wizard will guide you through the setup. Once the data adapter is set up you then use the Update member of the adapter to write the data from the data set. ds.ReadXML("MyXML.xml") da.Update(ds.Tables("XMLTable"))