Show All

Export Access data as XML

In Microsoft Access, you can output data to Extensible Markup Language (XML) 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, click the name of the table, query, form, or report that you want to export, 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. If 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, or apply a filter or sorting criteria,
   • export live data,
   • specify options to export a schema, or
   • specify options to export a presentation stylesheet.

   Do one or more of the following:   

   Export static or live data to XML

   1. Select the Data tab.

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

   2. 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 (UNC) path on a server.
   3. To select an encoding format other than the default, click the Encoding drop-down box and make a selection.

   4. If 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.

        Note  You can only apply a filter if the filter has already been defined for the table or query.

      • Select Apply Existing Sort if you want to sort the exported data according to any predefined sorting criteria.

      • Click Transform to specify any transform that you want to apply to the exported data.

        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.

   5. If 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.

      2. 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.

   Export schema to XML

   1. Select the Schema tab.

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

   2. 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.
   3. 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.

   Present your data

   1. Select the Presentation tab.

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

   2. 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.
   3. 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.
   4. 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 (UNC) path on a server.
7. If you do not need to specify further options, click OK to complete the export operation.