databasedev.co.uk - database solutions and downloads for microsoft access

Import XML Data into a Microsoft Access Database

Moving information back and forth between computers is very easy if you use an XML file. Microsoft Access 2007 recognizes XML files provided they have the structure expected by the software. Don’t worry if you have no idea how to write a XML file; most likely you’ll be given one to use so you only need to know what to do with it.

The best part of XML files is that they can be used with many applications running on many different operating systems. And just for the record, XML is NOT a data format and technically it’s a meta-language (or in simple words: a language for creating other languages). You can open a XML file with Notepad (or other text editors) and even edit it manually (hardly likely for a user to do that though).

For the purpose of this tutorial we’ll be using a XML file we created in the "Export Access Data to XML Format" tutorial.

Here’s how the table looked in our example:

Fig1. The table in our Example database

So, let’s create a blank database called “Example2” and then start the import process.

1. Click on External Data ribbon, go to the “Import” section and click on XML file:

Fig2. Click to Import the XML file

2. Browse to the location where the file is saved, click Open and then OK.

Fig3. Browse to the file’s location, choose it and then click OK. In the main "Get External Data…" window click OK.

You can also upload a schema file and Access 2007 will know how to create a table based on that information.

3. Access will scan the file and display its structure of the table to be created (remember that our example was just a simple table, without anything dependent on it).

You can choose to add the structure only, both structure and data in the table or to apply the schema to an existing table.
Schema files comprises detailed information about the elements a table contains, how the elements are arranged and what type of information each element can contain. Schema files do not contain any data so to populate the table you’ll need to repeat some steps (see the Note at the end of tutorial)

For the purpose of his tutorial we are importing both the structure and data.

Fig4. Click OK to import the structure and data of the table

4. If you know you’ll be importing the same file again, tick the box near "Save import steps" so that the import process will take place without having to use the wizard again.

Fig5. Save the import steps if you’d be using the file again

Double click the Table it added (in our case Table11) and you’ll see the structure and the data of the table we created in the previous tutorial.

And here’s our imported table:

Fig6. The imported table

Note: If you used a schema file you’ll end up with a BLANK table without the actual data. So to populate the data base you need to repeat steps 2,3 and 4.