Access Cookbook 2nd Edition-Access Cookbook 2nd Edition

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.

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.

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.

[ Team LiB ]