Betsy Rolland
LIS 600 Independent Study
Transforming XML to Microsoft XML
Terry Brooks
Winter 2006
Microsoft Access allows both importing of XML data into Access and exporting of data from Access into XML. However, because XML generally stores data in a hierarchy and Access stores data in linked tables, data may need to be transformed somehow in order to be useful. Access doesn't have a specific format of XML, like Word's WordprocessingML or Excel's SpreadsheetML.
Select File->Get External Data->Import as shown here to start the process of importing data into Access. There are three options for importing XML data. You can import the data and create a new table, you can create a new table based on the structure of the data, but without the actual data, or you can append the data to an existing table. In the Import dialog, simply select the XML file you want to import and choose how you want Access to import it.
One important thing to note is that Access ignores attributes when importing data. If your XML data has attributes you want preserved, an XSLT stylesheet to transform the attributes to elements can be run on the data before importing. This can be done either before starting the Import or by using the Import dialog's "Options" button and selecting the XSLT stylesheet to apply.
Below is the result of importing the "Office" XML structure into Access:
XML:
Access:
Interestingly, when I tried to import our LIS 545 flowers XML file, it produced two separate tables with just the name element and its data:
Exporting a single table from Access into XML is quite simple. Select or open the table you want to export, then select File->Export. Choose your name and location, select ".xml" from the drop-down menuand click Export. A dialog box will pop up asking what information you want exported:
Both an XML file and an XSD schema file will be created here. As with the Import dialog, clicking the More Options box will allow you to apply an XSLT stylesheet, this time before the export. Both files contain a little bit of Access-specific data, but there isn't a default format like when saving Word or Excel documents in their respective XML formats.
It is also possible to export both multiple linked tables (with one or more relationships defined) and queries following the same procedure.