Microsoft Access Database: XML Data Export in Access

There are several ways to export data from Microsoft Access to other file formats. The general method when exporting data usually refers to the data itself namely to an Excel spreadsheet (.xls/.xlsx) or as a CSV (comma separated value) file or even as an HTML file for the web.

However, using the XML (Extensible Markup Language) export option you can choose to either export the data, structure or both.

What is XML? It was designed to transfer data and its structure from one platform to another using a universal format (for the internet) and display it as an HTML file.

Within Microsoft Access, you can export tables, queries/views, datasheets, forms or reports which contain both the data (records) and their structures. There must be some data present in order to succesfully export anything; structures can not be exported on their own.

Microsoft Access Database: Exporting As XML

When you export a database object as an XML document, Microsoft Access will provide you with several options:

  1. You can just export the data from a table, query/view, datasheet, form or a report (forms and reports must be bound to a data source) into an XML file. The name of the export file is whatever you call with the extension .xml and is deemed a snapshot of the data.
  2. You can also export the structure from a table, query/view, datasheet, form or report as an XML schema file. This type of file describes the data tags of the fields and rules for your XML data document. The file format is the filename with the extension .xsd.
  3. Additionally, you can also save the file structure that describes the layout and presentation of the data from a table, query/view, datasheet. form or a report. For tables, queries/views and datasheets, the presentation file is similar to a spreadsheet structure and is saved with the filemane with the _report.xml added to it. With the forms and reports however, this file is saved as XML file called ReportML.
  4. Finally, you can choose to save the file as an XSL format which is an Extensible Stylesheet Language format which is a custom style layout of the data file. It’s very similar to style sheets when designing pages for the web (if you know what that is).

Microsoft Access Database: How To Export To XML File

First select the object (table, query, form or report). I’ve chosen a query which contains two related tables.

In Microsoft Access 2010, go to the ‘External‘ tab on the ribbon bar and select the XML File icon.Microsoft Access Database XML Export Data - External TabIn the next screen, you confirm the name of the xml file and location which is the default of the selected object and can be changed.

Microsoft Access Database XML Export Data - Save AsAfter you confirm the above screen, the next dialog box appears allowing to choose one or all three output types.

Microsoft Access Database XML Export Data - Options

Click the OK button and another screen will ask you to optionally save the changes for next time which can be ignored.

If you had choosen all three options, the following data files (which I opened in a text editor; Notepad) can been viewed.

Microsoft Access Database XML Export Data File ResultsThe above three files can be used to populate a web page, server database to display data and the layouts based on the export definitions chosen.

Developers may use this approach to link with other external development applications namely the .Net framework and web services.

This entry was posted in Forms, MS Access, Queries, Reports, Tables, Web and tagged , , , , , . Bookmark the permalink.

3 Responses to Microsoft Access Database: XML Data Export in Access

  1. Ben says:

    The only way I would approach this (and I have to be honest, haven’t looked into this myself) will be to write some VBA code to open a connection to the web service and upload the XML file from the current db function call (CurrentDB using DOA/ADO). Have Google’d for VBA code on this?

  2. Aman says:

    Hi Ben,
    I am currently faced with this problem .I am trying to migrate a data from access 2007 to a web-service ( asp.net 4.0 SOAP). I have successfully created the XML file from ( access query ) using VBA and built a c# console application to upload the file to the web-service. Now i am looking for any possible ways to export directly from access to web-service an XML file , hopefully on the fly to avoid any XML file storing on the local system.
    Thanks.

  3. Ben Beitler says:

    Instead of using the save export command which is available to versions 2007 and 2010, consider using a macro to run and re-run the export to XML option which has the added benefit of running several tasks wrapped into one procedure.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Confirm you are human.