Hack 16 Work with XML in Microsoft Access 2003

figs/moderate.gif figs/hack16.gif

If you are a Microsoft Access user, you'll be happy to know that you can export Access 2003 data as XML.

Microsoft Access 2003 is Office's database application. You can create a table of data?an Access database?and label each field with a name you'd like to use as an XML element name. One way to get started is by importing an existing XML document into Access. Here's how to do it.

Open Access, and then select File Get External Data Import. In the Import dialog box, make sure it says XML in the "Files of type" pull-down menu. Navigate to the working directory and click on the file time.xml. Then click Import. Not all information is preserved, but close.

You will then see the Import XML dialog box. Click on the Options button, and the dialog will appear as it does in Figure 2-14. You can choose to import the XML structure only (i.e., only the markup) or the structure with data (i.e., the markup and content). You can also choose to append the data to an existing table; i.e., a table with the same name as the original document (in this example, time). If you append the data, the content of the XML document is added to a record of the database file using the same fields that are created from the element names.

Figure 2-14. Import XML dialog box in Access 2003

After you have imported the document, you should see a database table in the navigator view of Access, as shown in Figure 2-15. Click on the table's icon to open it. In Figure 2-16, you can see that the fields are labeled with the names of elements in time.xml.

Figure 2-15. time table in Access 2003 navigator view

Figure 2-16. time.mdb in Access 2003

To save this data as an XML document, select File Export, and the Export Table dialog box appears. In the "Save as type" pull-down menu, select XML, and enter a filename, such as TimeTable.xml. Then click Export All.

At this point, a few more choices are presented in the Export XML dialog (Figure 2-17). You can check one to three boxes to tell Access what you want to do. When all three boxes are checked, Access will save your database as an XML document (TimeTable.xml); create and save an XML Schema for the saved XML document (TimeTable.xsd); create and save an XSLT stylesheet that can transform the newly saved XML document as HTML (TimeTable.xsl); and create and save an HTML document based on this transformation (TimeTable.htm). Access reportedly supports only some of the XML Schema structures (http://www.w3.org/TR/xmlschema-1/), which will be reflected in the saved XSD file; namely, xsd:schema, xsd:element, xsd:appinfo, xsd:annotation, xsd:complexType, xsd:simpleType, xsd:restriction, xsd:choice, and xsd:all.

As exported, TimeTable.htm can be displayed only in Internet Explorer because it uses a VBScript function ApplyTransform() to apply TimeTable.xsl.

Figure 2-17. Export XML dialog box in Access 2003

If you click the More Options button on the Export XML dialog box, you can pick more settings for the XML document, the XML Schema, or the stylesheet. Under the Data tab, you can see that you can export all records of the database or just the current one. You can select UTF-8 or UTF-16 encoding. You can also apply a transformation (an XSLT stylesheet) to be applied at the time of export. Under the Schema tab, you get to choose whether to include primary key and index information or leave it out. You can also choose whether to save the schema as a separate file or embed the schema with the XML document. The Presentation tab lets you save as an additional document in HTML or ASP format and choose whether to include images. All three tabs let you browse to a location for the saved file as well as name the file.

2.7.1 See Also

  • Office 2003 XML, by Evan Lenz, Mary McRae, and Simon St.Laurent (O'Reilly)