Access Cookbook, 2nd Edition [Electronic resources] نسخه متنی

Recipe 18.4 Export XML

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.

Figure 18-11. The Export XML dialog box allows you to select related tables

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.

Figure 18-12. Exporting related tables generates XML for the data in each table

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.

Figure 18-13. You can create a separate XSD schema document or embed the schema along with data in one XML document

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

Figure 18-14. Exporting a query to XML produces an element for each row of data returned by the query

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 XMLthis also allows you to select the rows and
columns to include or to export data based on expressions.