Automate importing xml data into existing table

[topsy]

Hi,

I have been searching around for the best way to do this and I need some insight. I have a file folder where pre-structured XML files full of form data are being constantly dumped to. I am curious as to what is the best way to import the data from the XML file into an access database table.

The database I am working with has one large main table where all of the main record data is stored. There is a somewhat complex string of queries and reports based off this table that I am concerned about preserving. The problem is that the XML file is not structured in the same way the table is. The headings are named different, aren't in the same order, etc. I cannot use the import method and simply append it to the main table.

After much searching around I have found two options:

1) Use the built in XML import method that access provides to create a secondary table. Then find a way to take data from individual fields in the second table and map and insert it into a new record in the main table.

• I already have the import part of this option working. The only part I can't seem to understand is how to take data from the second table and get it into a new record in the main table under the correct headings

2) Read the data from the XML file all at once and then map and insert it into the main table

• I have not attempted this yet. I was having a hard time understanding how to retrieve the data from the XML file in the first place.

So.. which would be better/easiest to automate (most likely via button click on a form)? I only have a small understanding of VBA and even less understanding of anything XML. If anyone could point me to some sample code or outline the process I would greatly appreciate it. Hope I explained things okay.

 

[Frothingslosh]

Use option one. To transfer the data, build an append query in the query builder - the interface will have an option to specifically designate which destination field each column should be appended to.

You can then set up a button to do the import and then run the append query, all with one click.

 

[topsy]

Thank you for such a quick reply! I will start playing around with the append query. One other question..

Will I have to worry about duplicating data in the main table if the append is ran multiple times? For example:

Today I have a batch of 50 XML files. I import them to the second table and run the append query. The data is now in the main table and I delete all the XML files out of the dump folder.
Tomorrow I have 10 more new XML files in the dump folder. I import them into the second table and run the append query.

Will this duplicate yesterday's records as well? If so, what is the best way to avoid this? Removing the second table after each batch completes?

 

[Frothingslosh]

Yes, you will have to worry about that.

Generally, when importing to a temporary table, the first thing you want to do is either delete and rebuild the table or else run a basic delete query to remove all records from the table. In this case, the query would be to just drop the * field into the grid in the query builder and don't set any conditions. (Just remember, this deletes EVERYTHING in the table you've selected.) The SQL would look like 'DELETE * FROM YourTableName;'.

Once you've deleted the existing records from the temporary table, you can run the import and append process.

Also keep in mind that the records aren't totally removed from the database until you run a compact and repair, so you're going to want to make a point of running that periodically. I always recommend doing it manually (and only after making a backup), as the 'Compact on Exit' setting has a small chance of corrupting a database when run on a network share. I've lost a couple files over the years to that.

 

[topsy]

Sorry for the delay in posting. I finally got everything working properly today though. I really appreciate your help

 

[Frothingslosh]

No worries, I'm just glad you got it working!