Access Data Analysis Cookbook (Cookbooks)

18.4.1 Problem

You need to export Access data to an XML file so that it can be used in another application.

18.4.2 Solution

There are several different approaches to exporting XML data from Access, depending on the results that you want. The 18-04.MDB sample database has three tables, Car, Customer, and Preferences. The Car and Customer tables contain information about cars and customers, and the Preferences table contains information about which cars a customer prefers. The Preferences table is related to both the Car and Customer tables.

18.4.2.1 Exporting all of the data in related tables

If you wish to export all of the data in related tables, you can do so easily from the File menu. Follow these steps to export data as XML from the Preferences table:

1. Select the Preferences table in the database window. Right-click and select Export, and choose XML in the Save as type drop-down list at the bottom of the dialog box. Type a name for the XML file and click the Export button.

2. Leave the default options selected, and click the More Options button to expand the Lookup Data node. Check the two check boxes for Car and Customer, as shown in Figure 18-11. Since Car and Customer are both related to Preferences, Access allows you to select them here.

3. Click the OK button. Figure 18-12 shows the XML that is generated, with one element each of Preferences, Car, and Customer data displayed.

18.4.2.2 Exporting the data from a query

You can create a query to generate XML that displays data from the Preferences table along with the associated lookup data for the Car and Customer line items. Follow these steps to create the query and output the results to XML:

1. Create a query in the query designer that displays the results you wish to export to XML. The 18-04.MDB sample database contains qryCustomer, which selects the Customer name and Car make and model. Here is the SQL for the query:

   SELECT [CustLname] & ", " & [CustFname] AS Name, Preferences.Ranking, Car.Make, Car.Model FROM Customer INNER JOIN (Car INNER JOIN Preferences ON Car.CarID = Preferences.CarID) ON Customer.CustID = Preferences.CustID ORDER BY [CustLname] & ", " & [CustFname], Preferences.Ranking;

2. Save the query and close the query designer. Right-click on the query in the database window and choose Export. Then select XML in the Save as type drop-down list at the bottom of the dialog box. Type a name for the XML file and click the Export button.

3. Click the Export button and then click the More Options button. Select the Schema tab and note that the default options include exporting schema information in a separate schema document, as shown in Figure 18-13. Click OK.

This time the generated XML document contains an element for each row that the query returns, as shown in Figure 18-14.

18.4.3 Discussion

When you use the Access Export menu to export XML data, Access reads the table relationships and allows you to select related tables. The Schema tab on the Export dialog box allows you to select whether to create a separate schema file or to embed the schema information along with the data in one XML file. If you want to export data showing lookup data from related tables, you can export a query to XML this also allows you to select the rows and columns to include or to export data based on expressions.