Recipe 18.2 Import XML into Existing Tables
18.2.1 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.
18.2.2 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.
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.
18.2.3 Discussion
This example works because there is an exact mapping between the
element names in the XML file and the table and field names in the
Access Car table, so Access can figure out where the data is supposed
to go. As long as the data in the XML file does not have any
anomalies, then this solution will work nicely.Access will be unable to import the data in certain rows if there is
a data type mismatch. Consider the following XML file, 18-02-bad.xml:
<?xml version="1.0" encoding="UTF-8" ?>
<dataroot xmlns:od="urn:schemas-microsoft-com:officedata">
<Car>
<Make>Mini Cooper</Make>
<Model>S</Model>
<Price>20,000</Price>
</Car>
<Car>
<Make>Ford</Make>
<Model>Edsel</Model>
<Price>unknown</Price>
</Car>
</dataroot>
The Price element for the second car, the Edsel, is unknown. The
Price column in the Car table is expecting a currency value. When you
perform the insert, appending to the existing table,
you'll see the error message shown in Figure 18-6.
Figure 18-6. Error message appending bad XML to an existing table
If you open the ImportErrors table, you'll see the
information shown in Figure 18-7.
Figure 18-7. The ImportErrors table shows error information for data that failed to be appended to the table
If you open the Car table as shown in Figure 18-8,
you'll see that the Make and Model for the Edsel row
of data imported correctly. However, the Price for that row is set to
0, the default value.
Figure 18-8. Access fails to import data from an XML file where it can't convert the data to the correct data type
If there is no default value specified for the Price column, then no
value will be entered for Price, but Make and Model will be imported
successfully. If the Required property for Price is set to Yes, then
the entire row will be skipped, and you'll have an
additional row in the ImportErrors table with the following data in
the Error Message column:
Microsoft JET Database Engine: The field
`Car.Price' cannot contain a Null
value because the Required property for this field is set to True.
Enter a value in this field.