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.
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.
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.
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.
Open the Car table in datasheet view. You will see that the XML data has been appended to the table correctly.
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" ?>
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.
If you open the ImportErrors table, you'll see the information shown in Figure 18-7.
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.
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.