Importing XML Data into Access

You can import an XML file into Access. The process is very simple but is also somewhat limited in that it does not provide you with very many import options. To import an XML file, follow these steps:

  1. Open the database that is to contain the XML file.

  2. Right-click in the Database window and choose Import from the context menu. The Import dialog box appears.

  3. From the Files of Type drop-down box, select XML.

  4. Select the folder where the XML file is located.

  5. Select the XML file.

  6. Click Import. The Import XML dialog box appears (see Figure 19.11).

    Figure 19.11. The Import XML dialog box, which allows you to specify the details of the import process.


  7. Select the table whose data you want to import and then click OK. Access imports the XML file.

There are several problems and limitations associated with the process of importing XML data:

  • The only type of Schema Definition (XSD) files that Access can read are Access-generated XSD files. This means that unless an Access-generated XSD file is available to you to use during the import process, you have little control over the structure of the imported table.

  • If the XML file is not syntactically correct, the import process fails. In that case, Access generates an ImportErrors table.

  • Access can only read an element-centric rather than an attribute-centric XML file.

  • If the table name in the XML file is dataroot, root, or schema, the export process fails, resulting in the generation of an ImportErrors table.

Despite all these limitations, the ability to import XML files into Access is a valuable aspect of Access 2003. If the XML file is properly formatted, the import process should go rather smoothly.

    Part III: Creating Your Own Database and Objects
    Part V: Advanced Topics