|[ Team LiB ]|
Recipe 18.2 Import XML into Existing Tables
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.
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.
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:
|[ Team LiB ]|