Export Access data as XML

In Microsoft Access, you can output data to Extensible Markup Language (XML) (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) by exporting to an XML document. You can also export a schema to specify the structure of the XML document as well as a stylesheet to describe the presentation of the data.

  1. In the Database window (Database window: In Access 2003 and earlier, the window that appears when a database or project is opened. It displays shortcuts for creating new database objects and opening existing objects. In later versions, it is replaced by the Navigation Pane.), click the name of the table, query, form, or report that you want to export (export: To copy data and database objects to another database, spreadsheet file, or file format so that another database or program can use the data or database objects. You can export data to a variety of supported databases, programs, and file formats.), and then on the File menu, click Export.
  2. In the Save as type box, click XML(*.xml).
  3. Click the arrow to the right of the Save in box, and select the drive or folder to export to.
  4. In the File name box, enter a name for the file, and then click Export.
  5. In the Export XML dialog box, do one or more of the following:
    • To export the data to an XML document, select Data (XML).
    • To export the schema to an XML file, select Schema of the data.
    • To format your table or query data by using an Extensible Style Language file, select Presentation of your data (XSL).
  6. ShowIf you are exporting live data or if you want to specify further options for exporting your static data, click More options

The Export XML dialog expands to let you

  • export static data and apply a transform, include related tables or queries,
  • apply a predefined filter or sorting criteria,

 Note   You cannot apply predefined filtering and sorting criteria when exporting from an Access project.

  • export live data,
  • specify options to export a schema, or
  • specify options to export a presentation stylesheet.

Do one or more of the following:    

ShowExport static or live data to XML

  1. Select the Data tab.

 Note   The Export data check box shows the selection made in step 5.

  1. Enter a destination for the exported files in the Export location dialog box or use the Browse button. The path can be to a hard disk drive or a universal naming convention (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename.) (UNC) path on a server.
  2. To select an encoding format other than the default, click the Encoding drop-down box and make a selection.
  3. ShowIf you are exporting static data in a table, query, or a report or form that has a table or a query as a static data source

  • The tree under Data to Export displays the database object you are exporting and any related tables or queries. To include any related tables or queries in the exported data, select them in the tree.

 Note   You cannot exclude the database object that you have selected to export in the Database window.

  • Under Records to Export, you can choose to export all records in the selected database objects, apply a predefined filter and export only the filtered records, or export only the current record.
  • Select Apply Existing Sort if you want to sort the exported data according to any predefined sorting criteria.

 Note   You cannot apply predefined filtering and sorting criteria when exporting from an Access project.

In the Export Transforms dialog box, do one of the following.

  • In the displayed list of transforms, select a transform and click OK.

 Note    Selecting (none) and clicking OK is the same as not applying any transform.

  • If the displayed list does not contain the necessary transform, add the transform file by clicking Add. This opens the Add New Transforms dialog box which allows you to add a file with the extension xsl or xslt. After adding the transform, select it and click OK.

 Note   If the XML data references any transforms, the Export Transforms dialog box also lists these transforms. These transforms and the item (none) cannot be renamed or removed.

  1. ShowIf you are exporting live data in a table, query, stored procedure, function, or a form or report in a Microsoft Access Project (ADP)

  1. Select Live Data. The database object you are exporting is connected to a live data source on a SQL server.

 Note   The Live Data option in the Export XML dialog is disabled if you are trying to export a form or a report that is bound to a stored procedure. However, this can be accomplished programmatically through the Access object model.

  1. Specify the Virtual Directory for the live data source. This virtual directory is the HTTP address of the SQL server data source. It defaults to http://<machine name of the SQL server>/<database>. For more information on how to set up a virtual directory, see "Using IIS Virtual Directory Management for SQL Server Utility" on the Microsoft Developer Network (MSDN). For an example of how to set up a virtual directory, see "Creating the nwind Virtual Directory" on MSDN.

ShowExport schema to XML

  1. Select the Schema tab.

 Note   The Export schema check box shows the selection made in step 5.

  1. To include additional information on the underlying structure of the database such as the primary key and index information, select the Include primary key and index information check box.
  2. To embed the schema directly into the XML data document, select the Embed schema in exported XML data document option.

To create a schema file which is separate from the data file, select the Create separate schema document option, and enter a valid destination for the file or use the default.

ShowPresent your data

  1. Select the Presentation tab.

 Note   The Presentation format drop-down box shows the selection made in step 5.

  1. Do one of the following:
  • To run the exported file from a client machine, select the Client (HTML) option under the Run from selection. This option generates an HTML file on the client machine which contains scripting code to merge the presentation (XSL) file and the data (XML) file. This option has the advantage of indirectly referencing the presentation file from the HTML file instead of embedding the presentation information in the data. Therefore, either the data (XML) file or presentation (XSL) file can be changed without having to rerun the original document.
  • To run the exported file from a server, select the Server (ASP) option under the Run from selection. This option generates an ASP file that merges the presentation (XSL) information with the data (XML) and sends the resulting HTML file to the client machine for display.
  1. When you are exporting forms or reports, in order for images to appear correctly in an XML presentation, they must be located external to the XSL document which contains internal references to the files. Do one of the following:
  • Select the Put images in option and the file location.
  • Select the Don't include images option to exclude any document images from the XML version.
  1. Enter a destination for the exported presentation file in the Export location dialog box, or use the Browse button. The destination path can be to a hard disk drive or a universal naming convention (universal naming convention (UNC): A naming convention for files that provides a machine-independent means of locating the file. Rather than specifying a drive letter and path, a UNC name uses the syntax \\server\share\path\filename.) (UNC) path on a server.
  1. If you do not need to specify further options, click OK to complete the export operation.
 
 
Applies to:
Access 2003