Importing a simplified, contrived XML file - Oracle

The steps involved in importing are very similar to exporting, so we will pick up at the point where you are ready to import an XML as shown in the next picture.

Here we need to take a break, because we do not have a table into which this XML file can be imported. For XML data such as that shown above, how do we construct a table? We attempt to create a table named 'ARGENTINA' in Oracle 10G XE using the following script. We can test and verify in the SQL utility available.

When you try to export this table to an XML file you will see that, unless the table is populated, you cannot export the table.

The next picture shows an exported file after creating a row in the table.

Now we want to create an XML file and then load it into the ARGENTINA table in the HR database.

Here is the XML file used for this purpose. Only the CustomerID and KeyVal have been altered.

Now when you click on the Load button you will get the following message stating that the load succeeded.

Now you can see in the object Browser the contents of the ARGENTINA table as shown in the next picture.

Summary

Importing XML data into Oracle 10G XE is not as easy as exporting a table to XML formatted data. This same type of difficulty was experienced while importing XML into MS Access 2003. The error messages when importing fails provided by Oracle 10G XE are not very useful as they do not provide any clue. The reason for this difficulty in the case of Oracle appears to be related to the non-standard way XML data may be authored. When one follows the rules to produce Canonical XML the import is easy.