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 use tags to format their content, 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 extensible—there are ways to add your own tags. Here is an example of a simple XML file:
<?xml version="1.0"?>
<ORDER>
<CUSTOMER>Nancy Davolio</CUSTOMER>
<PRODUCT>
<ITEM>Sterilized Soil</ITEM>
<PRICE>$8.65</PRICE>
<QUANTITY>1 bag</QUANTITY>
</PRODUCT>
</ORDER>This file describes an order that Nancy Davolio (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 just its appearance, you can easily import the data into a database table.
In this exercise, you will import the Orders and Order Details XML documents into the GardenCo database.
USE the GardenCo database and the Orders and OrderDetails XML files in the practice file folder for this topic. These practice files are located in the My DocumentsMicrosoft PressAccess 2003 SBSImportingImportXML folder and can also be accessed by clicking Start/All Programs/Microsoft Press/Access 2003 Step by Step.
OPEN the GardenCo database and acknowledge the safety warning, if necessary.
On the File menu, point to Get External Data, and then click Import.
In the Files of type list, click XML.
Navigate to the My DocumentsMicrosoft PressAccess 2003 SBSImportingImportXML folder.
There is one file named Orders and two files named Order Details. Of the two Order Details files, one has the extension .xml, and the other has the extension .xsd. (You might not see the extensions unless your computer is set to display file extensions, but you can quickly view them by hovering over each file name.) XML consists of data and a schema, which describes the structure of the data. Programs that export to XML might combine the data and schema in one .xsd file, as with Orders, or might create an .xml file to hold the data and an .xsd file to hold the schema, as with Order Details. If the program exports two separate files, you will have to have both files to import both the data and the structure into Access.
Click Orders, click Import to open the Import XML dialog box, and then click the Options button to display the import options.
Tip
A powerful new feature in Access 2003 is the ability to apply a transform script to data as you import or export it. Transforms are a type of template that is 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 Access Help.
Click OK to accept the default to import structure and data.
The Orders file is imported and the Orders table is created.
Click OK to close the message that the import process is complete.
Repeat step 1 to open the Import dialog box.
Click Order Details (the .xml file), and then click Import.
Click Order Details and click OK to accept the default to import both structure and data.
The Order Details.xml and Order Details.xsd files are imported and the Order Details table is created.
Click OK, and then open and view the Orders and Order Details tables to confirm that the data and structure were imported.
The two date fields that were imported, OrderDate and ShippedDate, are displayed in the format yyyy-mm-dd. They were also imported as text rather than date values, which would make it difficult to use them in queries to find orders placed between specified dates.
Close the Order Details table. Leave the Orders table open.
Click the View button to switch to Design View.
Change the Data Type for OrderDate and ShippedDate to Date/Time.
Click the Save button to save your changes.
Return to Datasheet view.
The dates are now displayed in a more conventional format, and can be manipulated as dates.
CLOSE the GardenCo database.