Loading and Unloading XML Files Using Oracle 10G XE

In many cases, exporting a table to create an XML file is easy, but importing an XML file to create a table is much more difficult. This is true for Oracle 10G XE, as you will see in this article.

In a recent article we saw how to import an XML file in to an MS Access Application to create a table. Similarly another article discussed the exporting of an MS Access table to an XML file. Of the two processes, while the exporting is handled very easily by most programs, the importing appears to be not such a straightforward process. In SQL 2000 server, while the exporting of data to an XML file was handled by TSQL extensions easily, the importing of an XML file to a table was not just as easy. In Oracle 10G XE, a similar situation exists: the exporting is done quite easily, whereas importing requires a few more considerations. This article considers, both loading and unloading an XML file into the database using Oracle 10G XE.

Exporting a table to an XML file in Oracle 10G XE

From Start–>All Programs–>Oracle database 10G Express edition–>Go to database home page… you can get to the login page for the HR database as shown in the next picture.

This opens up the HOME of the HR database as shown in the next picture.

Now click on the Utilities icon to open the next window as shown.

Now click on the Data Load/Unload icon to take us to the next step as shown.

Now click on TO XML to open the following wizard as shown.

{mospagebreak title=From Table to XML}

Here you choose a Schema HR and from the HR database choose a table, and then export it. You can save it to an XML file. It is as simple as that. There is no need to script any SQL statement. If you need to export a smaller number of columns in the table, the wizard allows you to do this as well. Now click on the Next button to open the next window. Click on the drop-down related to Table and from the drop-down list choose the Departments table as shown.

Click on the Next button to open the next window where you may choose the columns. We choose all the columns and also click on the Export as file check box.

Now click on Unload Data. This opens up the following message.

If you choose to save the file, it gives you the file name by which it is saved. The Desktop directory was chosen to save the file; it gets saved there.

Voila, you have an XML file which may be opened from the browser as shown. Nothing can be simpler than this.

If you did not choose to export and left the check box unchecked, it will unload the XML file to the browser right away as shown. The returned XML is a well formed XML document with a root element of ROWSET enclosing the ROW Elements. Each ROW element corresponds to a row in the table. The table name is not displayed except for its structure as found on the table.

At this point you may be interested in looking at the table structure to verify the XML against it as shown in the next picture.

{mospagebreak title=Importing an XML File into Oracle 10G XE}

Let us say we want to import an XML file as shown in the next picture. This XML file was generated by exporting a table from an MS Access application.

Simplifying the test further

The MS Access created file has Microsoft Namespace references, details of columns, and so forth. In this format the Load into Oracle will fail. From experience the XML document loading error messages in Oracle 10G XE  do not give much help to the user. This help would have been very useful as the user does not know what to do next. Hence for this tutorial the following simplified procedure was used.

The idea is to create a table, export it as an XML document and try to load it to the same table with different data. The rest of the steps do show that this at least succeeds.

{mospagebreak title=Importing a simplified, contrived XML file}

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.

[gp-comments width="770" linklove="off" ]
antalya escort bayan antalya escort bayan