Access Cookbook - Ken Getz [331]
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:
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.
18.3. Import XML Using a Schema (XSD)
Problem
You need to import an XML file that has a certain schema. but don't know ahead of time what the schema will be. You need to create a table that has the correct data types, and then generate a new AutoNumber primary key for each row appended to the table.
Solution
If you want to apply a particular schema when you import an XML file, you need to import the schema file, or XSD, before importing the data. If you have already created a table with the desired structure, you can have Access save the schema for you by exporting the table and saving the schema as a separate file. This is an easy way to use Access to create schema files. You can also manually create a schema file by using a text editor, and save it with an XSD file extension. You also can use a schema file that has been provided to you by your company or by a partner. Follow these steps to import a schema file and then an XML file:
Open the 18-03.MDB sample database.
Choose File → Get External Data → Import from the menu to load 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 18-03.xsd, and click Import, which will load the XML Import dialog box shown in Figure 18-9. Note that the Options button is disabled. When you import a schema, there is no data involved. Click OK and then OK again.
Figure 18-9. Importing an XSD file
Open the Car table in design view. Note that the table includes a column named ID for a primary key as well as the columns for the data contained in the XML source file. Close the table.
To import the XML data, Choose File → Get External Data → Import from the menu to load 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 18-03.xml, and click Import, which will load the XML Import dialog box. Expand the plus sign and note that the same three columns, Make, Model and Price are displayed. Click the Options