Access Hacks

Hack #63. Import Varied XML Data into Access

Access is pretty good at importing simple XML data, but sometimes you want to import data that isn't precisely the way Access expects it to be.

Access lets you import data from XML files into its tables. For example, let's consider a database containing a table that defines a list of books. Figure 7-19 shows the Design view for this table. It includes six fields of three different types.

To begin, the table contains a few books, as shown in Figure 7-20.

The easiest way to see the XML format Access expects to receive when it imports data to this table is to export some of the data, which you can do by selecting a table in the database and then selecting Export... from the File menu. In this case, the XML format we'll need to let Access import automatically looks like the data that was just exported as XML. In other words, exporting records into XML shows the XML node structure any XML data being imported back in should have. Example 7-1 shows the exported data.

Figure 7-19. A simple table to which we'll import data

Figure 7-20. Test data in the books table

Example 7-1. New data for import

<?xml version="1.0" encoding="UTF-8"?> <dataroot> <books> <ISBN>0596002637</ISBN> <Title>Practical RDF</Title> <Tagline>Solving Problems with the Resource ...