databasedev.co.uk - database solutions and downloads for microsoft access

Import XML data into an Access database.

Introduction

Sharing data across software applications is inevitable in any business environment. Knowing how to update your Microsoft Access 2007 database with data from various applications will facilitate easy management of data.

There will be occasions when you need to exchange information with programs whose data is formatted in a different manner. Transferring data manually is cumbersome, time consuming, and often results in errors. Knowing how to use the Microsoft Access data conversion techniques can eliminate these problems and allow you to handle any conversion situation you encounter.

Collecting data from various file formats, such as XML, can be a crucial database management task as XML is one of the widely used file formats used for storing data.

Whilst building databases using Access, you may have to collect data from various sources. XML is a popular file format for many documentation purposes, as XML files are self-describing and platform independent. Therefore, learning how to import data from different file formats such as XML can be as important as knowing how to retrieve or manipulate data within a database. Moreover, importing data directly from an XML file can make your job very easy, as you do not have to spend excess time manually entering the data in an Access table.

XML Formats

Using Microsoft Office Access 2007, you can create three types of XML file formats that are used to complement each other in giving a total description of a data source.

Format Description
eXtensible Markup Language (XML) Contains just the data in the data source. It is an easy-to-read text file that can be used to make Access data available to individuals who do not have Access installed on their computers.
XML Schema Definition (XSD) Contains the schema for a dataset. An XSD file contains information about the structure of data in the corresponding class of XML files.
eXtensible Stylesheet Language (XSL) Can serve as a stylesheet for the XML file, or it can be used to transform data in the XML file. You can use an XSL file with the Extensible Stylesheet Language Transformations (XSLT) programming language to apply formatting, display specific information in different ways, produce a PDF or HTML file, and so on.

Viewing XML and XSD Files

You can view XML and XSD files in Internet Explorer, which applies a default XSL transform to them for improved display. You can also view these files using Microsoft Word or Notepad, but the display is not formatted as well.

Import Options

Access allows you to specify how you want an XML file to be imported into a database. There are various Import options available in Access for importing an XML file.

Import Option Description
Structure Only Imports only the structure of the XML file as an empty table.
Structure And Data Imports both the structure and the data of the XML file.
Append Data To Existing Table(s) Imports the XML data, adding it to an existing table.

How to Import XML Data into an Access Database

Import XML Data and Schema into an Access Database:

  1. If necessary, open the XML or XSD file in Notepad that you want to import, and verify its data.
  2. Open the desired database and, on the External Data tab, in the Import group, click XML File.
  3. In the Get External Data - XML File dialog box, click Browse to locate the XML or XSD file.
  4. In the File Open dialog box, navigate to the folder that contains the XML or XSD file you want to import, select the desired XML or XSD file, and click Open.

    Select the file type All Files so that XML or XSD files will be displayed.
  5. In the Get External Data - XML File dialog box, click OK.
  6. If the file type is XML, in the Import XML dialog box, in the Import Options section, select the desired option and click OK.
  7. If the file type is XSD, in the Import XML dialog box, click OK.
  8. In the Get External Data - XML File dialog box, click Close.
  9. If necessary, open the imported table and verify its structure.

If you need to go the other way, check out Convert Microsoft Access .mdb files to XML

The Author

Ramesh Gupta originally from New Delhi, India is now working as a freelance developer and lives in Boston, MA. He has designed and worked on numerous databases for previous companies and has experiences in many Microsoft technologies including Microsoft Access, Microsoft Excel, SQL Server and VB.NET.