databasedev.co.uk - database solutions and downloads for microsoft access

Export Access Data to XML Format

XML is a very simple and efficient way to exchange data between different computer programs. More and more companies choose to use XML files to move data back and forth, rather than using the Microsoft Access 2007 files. Most often you’ll be asked to send a XML file, which is easier to integrate with other reporting programs, rather than the high specialized .ACCDB database format.

In case the entire idea of using XML files scares you, then you have to know this: you don’t exactly need to know how XML looks like or works in order to use it. So, fear not the technology, just follow the instructions and Microsoft Access 2007 will produce the XML file for you.

In order to export Access data to an XML file, follow the steps:

1. Click on the External Data Ribbon, then choose Export » More » XML File
   
   
   Fig 1: Choosing where to export the data
2. Access will ask to choose a destination where to save the file. You can simply use the one Access suggests (which is exactly where the database you want to import is located). If you want to save it in other location, just click on Browse and select the location. Also you can change the name of the file.
   
   In my example, since I’m exporting a file called “Table1”, Microsoft Access suggests the name “Table1.xml” for the XML file which is about to create:
   
   
   Fig 2. Choose the location where you want to save the XML file and give it a name
3. Now it’s time to choose what file type you want to create.
   
   Access allows you to choose between three file types:
• Data (XML) – creates a XML file with all the content of all the records in the table
• Schema (XSD) – it doesn’t contain any data, but stores the definition which describes your table. You can use XSD files to:
• Give it to programmers so that they know what type of XML to expect from Access
• Use it to create new, empty table in another Microsoft Access database
• Presentation (XSL) – defines how a browser can convert the data in the XML file into HTML web page (which displays normally online). If you choose this option, Access also creates a XML file which uses the XSL file. So if you choose to create a XSL file based on Table1 you’ll end up with "Table1.HTM" and "Table1.XSL".



For this tutorial, we’ll choose XML file (which you’ll be using most of the time anyway).


Fig3. Choose the type of file

If you need to export related tables as well, click on "More options". In this case, Table1 doesn’t have any other tables related to it.


Fig4. Choose the related tables, if any, to be added to the XML file

You can also choose to export any other tables that are related to the main table. For example, if any other table contained a look up column based on Table1, then the respective table would have shown under the Table1 in the "Lookup Data" section.

4. Click OK to create the XML file. The result varies depending on what you chose at step 3.
5. If, at any time, you need to repeat the process without using the wizard, then tick the box near "Save Export Steps".
   
   
   Fig5: Save the export steps if you need to repeat the process at any time