Home

Microsoft Access and XML

 

Importing an XML File

An XML file is essentially a document made of at least one table. Normally, the table is very well structured because that's the essence of XML. An XML document can contain disparate data with various parents and different child nodes all over the place. Therefore, before importing an XML file to your database, you should be familiar with its structure.

A typical XML file starts with a root node:

<?xml version="1.0" standalone="yes"?>

Under it, the global parent node starts and closes itself:

<?xml version="1.0" standalone="yes"?>
<FunFurniture>
</FunFurniture>

This global parent node is not the table. Inside that node, you should have a node that would represent a table and it can repeat itself as many times as necessary:

<?xml version="1.0" standalone="yes"?>
<FunFurniture>
  <Employees>
  </Employees>
  <Employees>
  </Employees>
</FunFurniture>

Notice that, in our example, what we refer to as a record is in plural. This is not required. It is just our choice. In this example, the Employees node is our table. Put it another way, each Employees node represents the table we want.

Inside each table, you should have the name of each column followed by its value:

<?xml version="1.0" standalone="yes"?>
<FunFurniture>
  <Employees>
    <EmployeeNumber>924795</EmployeeNumber>
    <FirstName>Donald</FirstName>
    <LastName>Tripleton</LastName>
    <Title>Sales Manager</Title>
  </Employees>
  <Employees>
    <EmployeeNumber>274957</EmployeeNumber>
    <FirstName>Jeanne</FirstName>
    <LastName>Wooley</LastName>
    <Title>Sales Associate</Title>
  </Employees>
  <Employees>
    <EmployeeNumber>684078</EmployeeNumber>
    <FirstName>Irene</FirstName>
    <LastName>Polsen</LastName>
    <Title>Sales Associate</Title>
  </Employees>
  <Employees>
    <EmployeeNumber>297149</EmployeeNumber>
    <FirstName>Monica</FirstName>
    <LastName>Jackson</LastName>
    <Title>Sales Associate</Title>
  </Employees>
</FunFurniture>

The group of nodes inside the table represents that columns and their values. Of course an XML document can be more than that but this is a typical structure of a normal XML document that can be imported.

After creating and saving an XML file, you can import it in Microsoft Access. To do this, in the External Data tab of the Ribbon and in the Import section, click the XML File button XML File. This would display the Get External Data - XML File dialog box. After selecting the file and clicking OK, a dialog would come up to help you identify the table to use in the document. It would display the node(s) that indicate(s) a table.

Exporting to XML

If you have created a (complete or semi-complete) database that you want to use outside of Microsoft Access, one way you can transfer it is to change its format into XML. Fortunately Microsoft Access can take care of the whole process for you. In fact, besides exporting a table, you can also ask Microsoft Access to create both a schema file and a style sheet for the table.

To export a table to XML format, in the Navigation Pane:

  • Right-click the table that holds the data, position the mouse on Export, and click XML File
  • Click the table you want to export. On the Ribbon, click External Data and, in the Export section, click More -> XML File

The Export - XML File dialog box that comes up allows you to verify the name of the file that will be created and the path where it will go. Once you are ready, you can click OK. A dialog box would come up asking which one(s) of the three files you want to have created:

Export XML

If you want to create a more elaborate XML application with advanced options, you can click the More Options button. This would close the previous dialog box and open another one:

This dialog box allows you to specify more details on how the table should be exported. For example, you can change the encoding scheme you want. The two options available are UTF-8 (which should be enough for characters in US English) and UTF-16 (if you are planning to use Unicode or international characters) for characters referred to as wide characters. By default, when you ask Microsoft Access to create an extensible style sheet (XSL), it would create the file using the same name as the table. If you want a different name, you can click the Transforms button. This allows you either to select a different file or to create the file with a different name. Also, by default, if you ask Microsoft Access to create a schema, it creates an XSD file using the same name as the table. If you want a different name, you can click the Schema tab and specify another name for the file.

After making your selections, you can click OK. The file(s) you specified would be created in the folder that was indicated.


Home Copyright © 2008-2012 FunctionX