Importing Information from an XML File | MicrosoftR Office AccessT 2007 Step by Step

Extensible Markup Language (XML) files are often used for exchanging information between programs, both on and off the Web. XML files are similar to HTML files in two ways: both are plain text files that indicate formatting within tags, and both use start and end tags. However, HTML tags describe how elements should look, whereas XML tags specify the structure of the elements in a document. Also, as its name implies, the XML tag set is extensiblethere are ways to add your own tags. Here is an example of a simple XML file:

<?xml version="1.0"?> <ORDER>    <CUSTOMER>Mindy Martin</CUSTOMER>    <PRODUCT>       <ITEM>Sterilized Soil</ITEM>       <PRICE>$8.65</PRICE>       <QUANTITY>1 bag</QUANTITY>    </PRODUCT> </ORDER>

This simple file describes an order that Mindy Martin (the customer) placed for one bag (the quantity) of Sterilized Soil (the item) at a cost of $8.65 (the price). As you can see, when the data's structure is tagged rather than its appearance, you can easily import the data into a database table. An actual file created for this purpose would contain many instances of the <order> through </order> block, one for each order.

The information in an Access table consists of data and structure. When a table is exported to XML, the data and structure can be exported to separate files, in which case the data is placed in an .xml file, and the structure is placed in an .xsd file, which is referred to as a schema. (The structure can also be embedded in the .xml file.) In the following exercise, the Orders file contains both data and structure, and the Order Details files store the data in one file and the structure in another. If the structure is stored in an .xsd file, make sure it is in the same folder as the matching .xml file, otherwise Access will import the data and assign default properties to all fields.

In this exercise, you will import XML documents into an Access database.

USE the 06_ImportXML database, the 06_Orders XML file, and the 06_OrderDetails XML and schema files. These practice files are located in the Chapter03 subfolder under SBS_Access2007.

OPEN the 06_ImportXML database.

1.	On the External Data tab, in the Import group, click the XML File button. The Get External Data wizard starts.
2.	On the Select the source and destination of the data page, click Browse.
3.	In the File Open dialog box, navigate to your Documents\MSP\SBS_Access2007\Chapter03 folder.
4.	Click the 06_Orders XML data file, and then click Open. You won't see the extensions if your computer is set to hide extensions for known file types, but you can display them in a ScreenTip by pointing to each file name.
5.	On the Select the source and destination of the data page, click OK. The Import XML dialog box opens. Tip Access 2007 can apply a transform script to XML data as you import or export it. Transforms are a type of template used to convert XML data to other formats. When you apply a transform during import, the data is transformed before it enters the table, so you can adapt an XML file to a different table structure. For more information about using transforms, search for transforms in the Access Help file.
6.	With the Structure and Data import option selected, click OK. Access imports the 06_Orders file and creates the Orders table with the imported data.
7.	On the Save Import Steps page, click Close.
8.	Repeat Steps 1 through 7 to import the 06_OrderDetails XML data file (the file with the .xml extension, not the file with the .xsd extension).
9.	Open the Orders and OrderDetails tables to confirm that Access imported the data and structure.

CLOSE the 06_ImportXML database.