Importing XML Files into Access 2007

In an earlier article on Access 2003, importing an XML file into the Access application was described. It was shown that, while exporting data from a table was easier, importing an XML file was not straightforward. In this tutorial an XML file will be imported into Microsoft Access 2007, the latest version of MS Access for comparison.

Creating a blank database

From Start –>Microsoft Office choose MS Access 2007 and click to open the program. In the open program click on the Microsoft Office button and from the drop-down menu choose the New…option to create a blank database, ImportXML as shown. After providing a name when you hit the create button you will have created a blank database with a default Table1 with a single row.

Importing an XML File

The XML file that will be imported is shown in the next listing, a file used in several other tutorials on the DevShed sites. This file is on the present machine as well as on some networked drives.

<?xml version="1.0"?>
<wclass> 
<!-- My students who attended my web programming class --> 
<student id="1"> 
<name>Linda Jones</name>  
<legacySkill>Access, VB5.0</legacySkill> 
</student> 
<student id="2"> 
<name>Adam Davidson</name>  
<legacySkill>Cobol, MainFrame</legacySkill> 
</student> 
<student id="3"> 
<name>Charles Boyer</name>  
<legacySkill>HTML, Photoshop</legacySkill> 
</student> 
<student id="4"> 
<name>Charles Mann</name>  
<legacySkill>Cobol, MainFrame</legacySkill> 
</student> 
</wclass> 

{mospagebreak title=Importing the XML file}

Click on the External Data tab on the ribbon. In the menu, click on the XML File to import from an XML file.

Although the tooltip shows that you could either import or link to an XML file, when you click you will be presented with the next window which has only one option, Get External Data.

You may browse and point to the XML file on your drive. Here the file is on a network drive as shown.

When you choose the file and open, you will see the following window, Import XML, displayed. When you expand the student node you will see only the two items name and legacySkill as shown. The import did nothing with the attributes of the student in the original file. It appears to pick only repeated student nodes.

Assuming you import Structure and Data and click OK, the Get External Data window shows up with the "Save Import Steps" step. If you choose Save Import Steps by placing a check mark in the check box you will see it adds a few more controls to the window as shown. If this is some routine operation you do, you can leverage the integration of the Outlook program as described in the following window.  Of course you need to place a check mark to create Create Outlook Task as well.

The saved steps are added to the current database with the Saved Imports shortcut in the External Data tab. When you want to run the steps you only need to click on this shortcut which opens the following window.

When you click on the Save Imports button, you create the table into which your imported data contained in the webstudents.xml file will be saved. This table will have a title corresponding to the repeated elements, namely student.

You will notice that the import failed to bring the student ids into the table, which is very similar to importing an XML file into Access 2003. The design of the student table is as shown in the next picture.

Choosing the Transform… control in the ImportXML window above does not provide any help as it only points to files of the type Transform in My Documents folder.

{mospagebreak title=Modifying the webstudents.xml file}

It is possible to bring the XML file contents into an Access table if the attributes are replaced by elements as shown in the next listing. The basic data remains the same by this conversion for this particular file.

<?xml version="1.0"?>
<?xml version="1.0"?>
<Xstudent> 
<!-- My students who attended my web programming class --> 
<student> 
<id>1</id> 
<name>Linda Jones</name>  
<legacySkill>Access, VB5.0</legacySkill> 
</student> 
<student>
<id>2</id> 
<name>Adam Davidson</name>  
<legacySkill>Cobol, MainFrame</legacySkill> 
</student> 
<student>
<id>3</id>  
<name>Charles Boyer</name>  
<legacySkill>HTML, Photoshop</legacySkill> 
</student> 
</Xstudent>

When you bring in the above file by following the previously mentioned import steps and browse to the above file, the Import XML window displays the following.

Now if you bring in structure and data you will successfully add a student table  with its data as shown in the next picture.

{mospagebreak title=Another method for bringing in an XML file}

As previously noted in earlier tutorials the MS Excel program does a better job of importing an XML file even if the XML nodes contain attributes. The next picture shows how you may import the same webstudents.xml file using MS Excel 2007. Click on the From XML Data Import option to open or map to an XML file.

This displays the following Excel message as shown here.

The Excel program is going to create a schema of the source when you click the OK button, which displays the following window, named ImportData.  Here you have the option of placing your file in the existing worksheet or in a new worksheet. It also offers a default location beginning with the first cell in the current work sheet.

When you accept the default and click on OK, it brings the data into the work sheet as shown in the next window.

This sheet can be imported into Access 2007 with ease as shown in the next two windows. Here you are bringing Excel file data into Access 2007 by choosing the appropriate icon in the External Data tab.

This opens the following window, Get External Data – Excel Spreadsheet, where you will browse to the file saved in the step when you imported the XML file into MS Excel 2007.

After browsing to the saved worksheet file when you click OK, you will display the following window, which shows the Import Spreadsheet Wizard‘s first step.

When you click on the next button you will pop open the following window where you indicate that the first row has column headings.

Place a check mark to indicate that the First Row Contains Column Headings and click on the Next button. The following window gets displayed. Choosing No Duplicates for the ID will allow it to make this column the Primary Key later. You alter the properties of each of the columns one by one. In the  window  shown click on the Next button.

In this step you can choose the Primary key as shown in the next  window. By default Access 2007 chooses the Primary key as a surrogate key.

When you click on the Next button you will be asked for name for the table. Access 2007 provides a default but here webstudents is indicated as shown.

When you click Finish it takes you back to the Save Import steps window. When you click OK it will create a table, webstudents, whose design is shown in the next window.

Summary

Importing  XML files having nodes with attributes does not work, a behavior similar to that experienced with Access 2003. However, if the XML file has repeated nodes like the ones returned by a database, import is effortless. MS Excel can handle XML files with nodes having attributes.

One thought on “Importing XML Files into Access 2007

  1. Access 2007 fails in a manner similar to Access 2003 while importing attribute-centric XML files. The wizard that guides the user through the process of importing an XML file does not give adequate help when it fails (partially fails). With attribute-centric XML files it fails to import the attribute related information. Excel 2007 does it better as it creates an XSD on the fly.
    Thanks for reading the article. Now I look forward to your comments and suggestions.
    Sincerely, Jay

[gp-comments width="770" linklove="off" ]