Loading and Unloading XML Files Using Oracle 10G XE - Importing a simplified, contrived XML file
(Page 4 of 4 )
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.
| DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware. |
