Access Cookbook - Ken Getz [330]
In the File name dialog box, navigate to the XML file you want to import, and click Import, which will load the XML Import dialog box. The samples include an XML file named 18-01.XML that you can use.
Expand the plus sign (+) to show the structure of the XML file and click the Options button to expand the dialog box, as shown in Figure 18-2.
Figure 18-2. Viewing the structure of an XML file when importing into Access
The Options button shown in Figure 18-2 enables you to specify how you'd like the XML imported. If you choose Structure and Data as shown here, a new table will be created. Click OK twice to confirm the import.
A new table named Car has been created.
Discussion
When importing an XML file that has the structure Access expects, containing a hierarchical set of nested elements, the table name is derived from the first element after the root, in this case Car. The Make, Model and Price elements become columns in the table. The source XML file looks like this:
When you open the Car table in Datasheet view, it looks like Figure 18-3, with the data organized by column (element), with each row representing a single Car element in the XML file.
Figure 18-3. The Car table in Datasheet view
TIP
If you import the same file a second time, choosing the same options, then a second table named Car1 will be created. Rows will not be appended to the first Car table unless you explicitly select that option.
See Also
The following MSDN article gives a good explanation of XML namespaces: http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnexxml/html/xml05202002.asp?frame=true
18.2. Import XML into Existing Tables
Problem
When you import XML into a new table, the data is read as Text, regardless of whether some elements contain numeric values. You need the XML data to conform to certain data types for each element.
Solution
The simplest way to solve the problem is to create a table structure prior to importing the data. In Recipe 18.1, all of the columns in the new table are created as Text with a maximum size of 255, as shown in Figure 18-4 where the Price column is selected.
Figure 18-4. The numeric data for Price is imported as Text
Follow these steps to create a table structure that better matches your XML data:
Open the 18-02.MDB database.
Create a new table named Car (to match the name of the first element that follows the root element in the XML file). It's important that the name of the table match this element.
Create the columns and data type shown in Table 18-1.
Table 18-1. Columns and data types for Car
Column name
Data type
Make
Text 20
Model
Text 20
Price
Currency
Save the table and close the Table Designer.
Choose File → Get External Data → Import to display the Import dialog box.
In the Files of type drop-down list at the bottom of the dialog box, select XML (*.xml, *.xsd).
In the File name dialog box, navigate to the XML file you want to import, and click Import, which will load the XML Import dialog box. Select the 18-02.xml file and click Import.
Click Options and choose Append Data to Existing Table(s) as shown in Figure 18-5. Click OK and then OK again.
Figure 18-5. Appending XML data to an existing table
Open the Car table in datasheet view. You will see that the XML data has been appended to the table correctly.
Discussion
This example works because there is an exact mapping between the element names in the XML file and the table