Using XML with Access 2003

Using Access 2003, you can import data from an XML file as well as export data as XML files. The import and export methods in Access 2003 also enable you to import and export XML data, schema information, and/or presentation information to and from Microsoft SQL Server 2000 Desktop Engine,

Follow these instructions to import data from an XML file into Access 2003:

1. Select File --> New to create a new database file.

The New File task pane opens on the right side of the Access window.

2. Select Blank Database from the New list in the New File task pane.

The File New Database window appears.

3. Enter a name for the database in the File New Database window and then click Create.

A Database window opens in the Access workspace.

4. To get the import ball rolling, choose File --> Get External Data --> Import.

The Import dialog box makes an appearance.

5. In the Import dialog box, choose XML from the Files of Type drop-down menu and then browse to the location of your XML file. Select your XML file and then click Import.

The Import XML dialog box appears.

6. Click the Options button to show all the import options.

You can choose to import Structure Only, Structure and Data, or Append Data to Existing Table(s). If you import an XML Schema, the Options button is grayed out, because the only option is to import the structure only. You can also click the Transform button to add an existing XSLT stylesheet to the imported data.

You must import the entire XML file to Access — you can't select a subset of the information for import. The fields in the database table will be the same as the names of the elements in the imported XML document.

Follow these instructions to export data from an Access 2003 table to an XML file:

1. Open the database table in Access and then select File --> Export.

The Export dialog box comes to the fore.

2. In the Export dialog box, choose XML in the Save as Type drop-down menu and then click the Export button.

The Export XML dialog box appears. You can choose any or all of the options: Data, Schema, and/or Presentation. If you click the More Options button in this dialog box, you can choose additional features of the XML file, XML Schema file, or XSLT file.

Depending on your choices in the Export XML dialog box, Access 2003 will create the following files:

• An XML file: Additional options include exporting all records or just the current record; applying an existing filter, sort, or XSLT stylesheet to the data; and choosing an encoding format.

• An XML Schema file: Schema options include creating a separate schema file, embedding the schema file in an XML document, and including primary-key and index information.

• An XSLT file: Access creates an XSLT file and also creates an HTML or ASP file from the transformation of the XML file with this XSLT document. Options include the choice of an HTML file or an ASP file and, if the data includes images, the choice to export images in these files.

In addition to exporting XML files from Access 2003 database tables, you can export Access 2003 database tables as RTF (Rich Text Format) files. This automatically displays the data in tabular form in a Word document. To transform your XML data for print, you can import the file to a database table in Access 2003 and then export the data as an RTF file.