How to Import Data from Xml File into Ms Access

How to Import Data from Xml File into Ms Access
Page content

XML is a very important language for the web because of its wide use. Depending on your needs, there may be situations where you want to import data from an XML file into MS Access. Here’s how to do it.

Access 2003

MS Access 2003 can import XML data. To import XML data into Access 2003 follow these steps.

The first thing you need to do is prepare the XML file from which you want to import data. Before you try importing the file, it is advisable to make sure that the file contains well-formed XML.

Open the Access database into which you want to import your data, or create a new Access database.

Go to File-> Get External Data and click on Import.

An ‘Open File’ dialog appears. Enter the path of the XML file from which you want to import data and click Import.

The Import XML dialog appears with a list of tables it can import from the XML file. If you click on the plus sign before the table name, the table is expanded and you can view the fields of the tables.

Clicking on the Options button on this dialog will provide some additional options you can set. These are ‘Import Structure Only’, ‘Import Structure and Data’ and ‘Append Data to Existing Table(s). After selecting the option as per your choice, click on OK. If everything worked correctly, a message box will let you know that the import is finished.

Access 2007

Importing XML into Access 2007 is not very much different from importing XML into Access 2003, except for the differences that arise from the fact that Access 2007 has a different interface. Here’s how to do it.

The first step is to prepare the XML file and to open the database into which you want to import the XML file, or create a new database.

Click on the External Data tab in the Access ribbon. The menu options that appear lets you import data from various sources like text files, ODBC database, HTML document, XML files, dBASE files, Lotus 1-2-3 file etc. Click on the XML file menu item.

The ‘Get External Data - XML File’ dialog appears. Locate the XML file by clicking on Browse and navigating to the file. Once the file is selected, click OK. The Import XML dialog appears, which is similar to the one in Access 2003 with additional options of ‘Import Structure Only’, ‘Import Structure and Data’ and ‘Append Data to Existing Table(s).

Select the option you want and click OK. The next screen that appears offers an option not available in Access 2003. This screen notifies you that Access has finished importing the data and asks you if you want to save the import steps. This is, in a way, similar to a macro. If you choose to save the import steps, you can run it later anytime you need it. This will create the table by importing the data from the XML file without having to go through the entire process again. This may come in handy in some situations.

If you check the box ‘Save import steps,’ it will ask you to enter a name for the import steps, enter a description (optional), and click on ‘Save Import’. If you don’t want to save your import steps, just click on ‘Close’.