Exporting a Table as an XML Document to a Web site

Let’s look at an example that shows how to export a table as an XML document to a Web site. Let’s start with a Microsoft Access project connected to the Pubs database, then export XML files for the authors table to a Web site on my intranet.

Select Tables on the Objects bar, and highlight the authors table. Then, choose File, Export. In the Save as type drop-down list in the Export Table dialog box, select XML Documents (*.xml). Access enters authors into the File name drop-down list. Place the cursor just in front of authors in the File name list, then type http://yourservername/websitename. To save the files for the authors table in the pma17 Web site on the cab2000 Web server, I entered http://cab2000/pma17. You must be able to use HTTP to connect to the Web site. If you can’t (e.g., because you don’t have a live connection to the Internet), save the files in a local folder and move them to the Web site later. After specifying a file type and destination, click Export and wait for Access to connect to the Web server. After making the connection, Access opens the Export XML dialog box; the Data (XML) and the Schema of the data check boxes are already selected. These two options create the .xml and .xsd files. Select the Presentation of your data (XSL) check box to cause Access to generate the .xsl and .htm file. By default, Access prepares a client-side script for creating the HTML table file. The .xsl file in combination with the .xml file creates the .htm file. You can specify the creation of an .asp file by selecting Advanced in the Export XML dialog box and clicking the Presentation tab. If the Export Presentation (HTML 4.0 Sample XSL) check box isn’t selected, select the box. Then, select Server (ASP) to create an .asp file instead of the default .htm file. Figure A shows the settings that create an .htm file.

You can open the .xml file in a browser to view the raw XML document, or you can open the .htm file to view the XML document in table format. Both files reside in the pma17 Web site on the cab2000 Web server. Figure B shows the first two rows of data for the raw XML document. Figure C shows the same two rows in an HTML table.

You can import the .xml file as a table into an Access database file or into an Access project. This capability simplifies transferring database objects as XML documents between Jet and SQL Server databases. Access 2002 always imports an XML document as a table regardless of its source.

Start the import process by opening a new Access project that connects to a database other than Pubs (or you can also open an Access database file). Begin importing an XML document as a table by selecting File, Get External Data, Import. After the Import dialog box opens, select XML Documents in the Files of type drop-down list. Enter the location for the .xml file, including the data values for the XML document. The corresponding .xsd file that defines the schema is usually in the same folder as the .xml file. In the File name drop-down list, enter the URL for the .xml file: http://cab2000/pma17/authors.xml. Next, click Import, OK in the Import XML dialog box to import the file structure and data. You can use this default option to create a new table and populate it with values based on the XML document. You can import just the structure of the document without any data values, or you can append the data values in the XML document to an existing table that has the same name as the XML document. (The first option is convenient for creating a new table without populating the table with any values.) You can select any of the options by clicking Options in the Export XML dialog box, selecting the alternative you want, and clicking OK. After Access processes the XML document, click OK. In this example, these steps result in a new table named authors in the Access project or Access database file.