The Great XML-Access Database Exchange

• By April Wells
• May 7, 2004

Like this article? We recommend
Essential XML: Beyond MarkUp

Extensible Markup Language (XML) is a handy way to exchange data between different kinds of computer systems, different operating systems, even different—and often completely incompatible—databases. You can extract the data from one format and, through the use of an intermediary XML file, bring that data into a completely different format. This technique allows data to be migrated from one platform to another; for example, when switching from one type of database to another, or from flat-file storage to a database management system.

This article shows both how to export data from a Microsoft Access database to an XML file, and how to import data from an XML file into the same Access database.

XML: Like a Database

XML is somewhat similar to a database in construction and terminology:

• Elements in XML documents have relationships, in much the same way that entities in a database have relationships.

• Elements in XML documents have a parent-child relationship, just as tables with a primary key-foreign key relationship have a parent-child relationship.

The ultimate parent of an entire relationship tree in XML is the root element. If you look at a book as the root, the title page and chapters would be children of the book (and siblings of one another), a paragraph would be a child of a chapter, a sentence would be a child of a paragraph, and so on.

In addition to relationships, elements have content and attributes:

• An XML element consists of everything starting with and including the start tag and encompassing everything up to and including the element's end tag. The data between the tags can consist of several kinds of content:

• If an element acts as a container for other elements, it's said to have element content.

• A chapter's mixed content may consist of text (paragraphs and sentences) as well as illustrations, graphs, pictures, code, etc.

• A sentence is an example of simple content because it contains only text.

• An element that carries no information is said to have empty content.

• XML attributes are characteristics of an entity and typically map roughly to the columns in a table in a relational database management system, as in the following common examples:

• First name

• Last name

• City

• State

• Attributes are associated with values:

• Last name: Smith

• First name: John

• City: Madison

• State: Wisconsin

Although XML elements are typically entirely user-defined, a few naming rules must be followed:

• Names can contain letters, including non–English letters, as long as the interpretation software and any software used to read or manipulate the XML document doesn't have a problem interpreting these letters.

• Names can contain numbers and other characters, but cannot start with a number or punctuation character.

• Names cannot start with xml, XML, Xml, etc.

• Names cannot contain spaces.

• Avoid using hyphens (-) in names. Interpretation software may read the hyphen as a minus sign; for example, trying to subtract name from first if you use first-name.

• Avoid using periods (.) in names. The interpretation software may see the dot as part of an address or code example (first.name).