Exporting and Importing Data in Related Tables

Access 2003's new capability to export data and schemas from related tables lets you generate hierarchical XML documents. The documents contain elements from a base table, such as Orders, and child elements from corresponding records in the related table. Access-specific schemas define the table structures and relationships. Access 2003 also lets you limit the export to a selected record or apply the current filter, and offers the option to export lookup data.

Exporting Related Tables and Their Schema

To test-drive exporting related tables with a single record from the Orders table and related records from the Order Details table, do the following:

1. Open NorthwindCS.adp's Orders table in Datasheet view and select the first order (10248).

2. Choose File, Export, select XML Documents (*.xml), type Order10248 in the File Name text box, and click Export All to open the Export XML dialog. In this case, Export All is a misnomer.

3. Accept the defaults, and click More Options to expand the Export XML dialog and display the Orders and Order Details tables. Expand the nodes to display related lookup tables, and mark the Order Details table for export (see Figure 23.31).

   Figure 23.31. Access 2003's enhanced Export XML dialog displays the table you select for export and its directly related table, if any. Lookup Data nodes disclose other related tables.

   

   Note

   Exporting all Orders, Order Details, and lookup tables creates a 978MB XML file. Exporting lookup tables adds every record of the lookup tables to the end of the XML file. Images in the categories table are stored with Base64 encoding in CDATA elements. Base64 encoding translates binary bytes to combinations of the 64 printable low-order ASCII text characters.

4. Click OK to export the Order10248.xml and Order10248.xsd files.

5. Open Order10248.xml in IE to verify that the three Order Details line items are present as child elements of the order.

6. Open Order10248.xsd in IE and explore the schema that defines the two tables, indexes, and primary and foreign keys.

The <keyref... /> node at the end of Order12048.xsd establishes the relationship (a primary-foreign key constraint) between the two tables with the following elements:

 <xsd:keyref refer="Orders_FK_Order_Details_Orders"         name="Order_x0020_Details_OrderID">      <xsd:selector xpath="."/> <xsd:field xpath="OrderID"/> 

The schema uses XPath expressions to specify the relationship between the OrderID fields of the Order Details and Orders tables.

Note

XPath is a W3C recommendation for XML Path Language 1.0 (http://www.w3.org/TR/xpath). XPath is a very complex language for navigating XML documents during transformation with XSLT. XPath 2.0, which is even more complex than XPath 1.0, and XSLT 2.0 were in the working draft stage when this book was written.

Recreating and Populating Related Tables

You can recreate the Orders and Order Details tables in another database from Order12048.xml and Order12048.xsd, but it's more interesting to import all NorthwindCS tables into a new database. Including the lookup tables lets you verify that all relationships are present.

The following example recreates the NorthwindCS database from OrdersAll.xml and OrderAll.xsd. You create OrdersAll.xml by marking the check box for every table and exporting the XML document and schema, as mentioned in the preceding section's note.

To import all tables from NorthwindCS from OrdersAll, do the following:

1. Create a new Access data project named OrdersCS.adp with a new SQL Server database named OrdersSQL. Alternatively, create Orders.mdb.

2. Choose File, Get External Data, Import, select XML (*.xml;*.xsd), and open OrdersAll.xml. The Import XML dialog displays Tables subnodes for the eight tables (see Figure 23.32)

   Figure 23.32. The Import XML dialog displays subnodes for all tables in NorthwindCS. Expanding the nodes shows the tables' fields.

   

3. Click OK to import the tables, which takes a minute or two depending on the speed of your computer.

4. Open the imported tables in Datasheet view to confirm their resemblance to the source tables.

   Note

   The schemas that Access 2003 generates don't include application-specific annotations to describe SQL Server or Jet extended properties, such as captions and lookup data. Subdatasheets appear because the default value of the Subdatasheet Name property is [Auto]. You must add lookup data manually.

5. Click Database Diagrams in the Database window, and then click the Create Database Diagram in Designer shortcut to open Diagram1 and the Add Table dialog.

6. Click Add 8 times to add all tables to the designer and close the Add Table dialog. Closing the add table dialog adds the relationship lines between the table fields (see Figure 23.33).

   Figure 23.33. Adding the imported tables to a database diagram displays the relationships between primary and foreign keys.

   

   Note

   If you import the tables to a Jet database, Access automatically performs steps 5 and 6.

7. Close the designer and save the diagram with an appropriate name.

Appending XML data to tables with SQL Server identity columns or Jet AutoNumber fields doesn't work. Only SQL Server or Jet can update these fields. If you edit Order10248 to eliminate the values of the <OrderID> element or remove the element entirely, records in the ImportErrors table state that the table wasn't imported and data wasn't inserted. You must use SQL Server updategrams to insert XML data in one or more tables. Updategrams, which SQLXML 3.0 installs and documents, are beyond the scope of this book.