Working with XML Data with Microsoft Access

Access 2002 can import XML files to Access tables. Once XML data is imported into Access, you can use the Access data analysis tools just as you would with native Access data. You can also export the data in Access tables, queries, forms, or reports to XML files. Access 2002 does not support importing XML schemas.

To import XML data into Access, on the File menu, point to Get External Data, and then click Import. In the Files Of Type list, select XML Documents, select the XML file, and then click Import. Follow any additional directions that Access specifies to complete the import.

To export data as XML, in the Database window, select the database object that contains the data you want to export, click Export on the File menu, and then select XML Documents in the Save As Type list. Click Export. Select the appropriate options to export the Access object’s data as XML, the XML data’s schema, and the XML data’s presentation information. For example, you should consider exporting the XML data’s schema if you want to create additional XML data files that adhere to that schema, and you should consider exporting the XML data’s presentation information if you want to display the XML data on a Web site.

In the Export XML dialog box, the Data option must be selected for the Presentation Of Your Data (XSL) option to be available. Also, you can click the Advanced button to specify additional XML export options such as whether to include the XML schema information in the XML file, the encoding type, and so on.

Note

XML can be encoded (this means how certain characters and symbols are displayed in different languages) in either the UTF-8 or UTF-16 format. Encoding is disabled if you are not exporting data or schema information.

Putting It Together

In this exercise, you will import and export XML data to and from Access 2002.

1. Start Access and open the Northwind database in the Chap05 folder. If the Welcome screen appears, click OK to close it.

2. On the Main Switchboard screen, click Display Database Window.

3. On the File menu, point to Get External Data and then click Import.

4. In the Files Of Type list, select XML Documents.

5. Locate and select the CustList.xml file in the Chap10 folder, and then click Import.

6. When the Import XML dialog box appears, click OK. Click OK to close the success message.

7. Open the Customers1 table and examine the imported XML to see that it looks just like Access data. For example, XML element names become field names in the table. Close the table when you’ve finished.

8. With the Customers1 table selected, on the File menu, click Export.

9. In the Save As Type list, select XML Documents, and then click Export.

10. Select the Data (XML), Schema Of The Data, and Presentation Of Your Data (XSL) options, and then click OK.

11. Use Internet Explorer to open and examine the three files that Access creates: Customers1.xml (the data file), Customers1.xsd (the schema file), and Customers1.xsl (the transform file).

In Figure 10-10, which shows the XML data in Customers1.xml, you can see a reference to the schema defined in the file Customers1.xsd. The dataroot element contains a Customers1 element for each customer in the Customers1 table. Each Customers1 element contains information about each customer.

Figure 10-11 shows Customers1.xsd. This schema defines the structure of the XML data that is exported from the Customers1 table. For instance, this schema states that the dataroot element can contain any number of Customers1 child elements.

Figure 10-10: The Customers1.xml file.

Figure 10-11: The Customers1.xsd file.

Customers1.xls, shown in Figure 10-12, defines the layout of the XML data for presentation on a Web page. For example, the <TR> tag indicates that the XML data is to be displayed as an HTML-compliant table structure.

Figure 10-12: The Customers1.xsl file.

For more information on the data, schema, and transform files that Access generates and how you can work with them, see the topic “About XML data and Access” in Access Help.