Importing XML into MS Access 2003

Introduction

MS Access has been around for a long time, and over time it has added support for importing and exporting its data into HTML when HTML was king. When the extensive use of XML appeared on the landscape, MS Access added importing and exporting features to its arsenal. According to product documentation, MS Access 2003 fully supports importing and exporting XML.

Unlike HTML which was display-centric, XML is data-centric. Its tags have no more meaning than you bestow upon them. XML called for more discipline than HTML coders were used to in creating their pages, but at a minimum required a "well formed" document. Developers also had to put into writing what their "tags" really meant, and how their tags related (XML schema) to their data (XML data). This was the difficult part, particularly at a time when standards were still fluid. The difficulty in importing arises because of the inherent relationship that must exist between these two parts.

Importing a simple XML file

The imported file

The example chosen is one from my previous articles, the webstudents.xml. The next paragraph shows the complete file. This meets the minimum requirement of a "well formed" XML document.

<?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> 

This document is also in the root folder of the local IIS, and browsing it brings up the display shown in the next picture. Since there are no error messages when it is browsed, it meets the minimum requirements. It has three student nodes whose "ids" are stored in attributes, and each student node has two child nodes with no attributes.

Importing the file into MS Access 2003

Create a new MDB file and change its default to xml.mdb. You may save it to the default directory. This database has no tables, or other objects except for the utilities. Click on Import... hyperlink by following the trail File -->Get External Data.... as shown.

This opens up the default folder My Documents with a default file type which may be one of several different MS Access file extensions, but not XML. Since the file used in this tutorial is on the local web server, this was accessed as shown in the next picture.

When you highlight the webstudents.xml file, click on the Import button. The next window shown in the next picture, Import XML, pops up as shown. In this picture the student node has been expanded. You can see the two child nodes of the XML file.

Now if you click on the Options button you will see three options that you can use to import together with the default option, Structure and Data. While the options button is dimmed (inactive), a new button, Transform. is spawned as shown.

For now, just click on the OK button. This brings up the following MS Access message, and a new table is created with the name student (corresponding to the node).

The student table is now part of the database and you can create all other MS Access objects such as Form, Report, and Data Access Pages. Observing the table details, you will notice that although all student information showed up correctly, the student IDs are missing. So the import is not really complete. There is something missing in the import which is not quite apparent. The program does not give any indication as to what might be missing.

Importing the same file into Excel 2003

The premise for this attempt at importing is that all Office products, especially the 2003 vintage, have strong support for XML. The XML import steps from MS Excel are similar to the ones from MS Access, and will not be repeated except to show what happens when the import button is clicked after following the trail Data-->XML--> Import.... The next MS Excel message window pops up saying that the XML is lacking a schema and offers to create one. This is in Microsoft's best tradition of being user friendly.

Clicking OK to this message will bring up the cell locator where you want to park your data as shown. It does not, however, describe how the schema looks. You have the option of creating a new worksheet or using an existing worksheet.

Since there is only one worksheet in this otherwise blank XLS file, the option to click the OK button was taken. This brings in the data with every detail in it as shown. Now you see that not only did it bring in the child nodes correctly, it also brought in the student node details correctly. The XLS file is saved to xml.xls (it is saved to My Documents by default).

Now if you import the xml.xls file into MS Access you will get the complete information because it now happens to be an Excel table import. This is no different from importing worksheet data to MS Access.

One more time

Now we go back to an earlier step and use the 'transform...' button to see if we can succeed in importing the original webstudents.xml file. When the Transform... button is clicked during the import operation you get the following window. Again this is not a very informative screen unless there was a choice of transforms already present.

Clicking on the Add... button, the following window opens. It gives slightly more information than the previous screen. The program is looking for a file of type xsl or xslt. These are the well know extensible style languages. This will not be useful to the user as it gives him no clue for browsing to the file that will complete the import. Digging a little deeper, you will find that there are two wizards lurking in the labyrinths which are not listed, the Import XML Wizard and the Export XML Wizard.

Summary

Using the Import XML utility in MS Access 2003 failed to completely import a very simple file as some component in the MS Access 2003 may be missing, although it was verified that none of the functionality was disabled. The import was tested with Microsoft Office Access 2003 (11.6566.6568) SP2 on Office XP Professional Media Center Edition. However, MS Excel seems to do a nice job of the importing.