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:
- 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.
- 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.
- 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.
- 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.In 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.
After you confirm the above screen, the next dialog box appears allowing to choose one or all three output types.
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.
The 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.