Moving table data from SQL Developer into Application Express using XML

Gary, an Application Express customer for many years, recently sent me a message with a problem he was having getting table data, exported from Oracle SQL Developer in XML format, into Oracle Application Express. He pointed me to his post on the Oracle Application Express discussion forum on OTN.

Using the EMP table as an example, he showed me that the data he exported from SQL Developer looked something like:

He originally thought that Application Express was choking on the CDATA values. It isn't that Application Express didn't like the CDATA elements, it's just that they are two completely different XML formats. Underneath the hood, Application Express uses DBMS_XMLSAVE.INSERTXML for the XML loading, and it uses the default ROWSET and ROW tags. SQL Developer, for whatever reason, uses a different format. Maybe there's a way to control this in SQL Developer, but I'm not aware of it.

So how do we transform the XML from SQL Developer into something compatible with the standard ROWSET format used by Application Express? Well...this is exactly what XSLT was designed for. I'm no XSLT expert, but I learned enough from Carl's examples to accomplish this type of simple transformation. You can use any XSLT engine to do this type of transformation, but I chose to use Microsoft's command-line utility, msxsl, to do this.

1) Before I did anything else, I had to re-generate the XML from SQL Developer so the DATA type data values would be in the canonical date format used by Application Express XML data loading. To do this, from the SQL Worksheet in SQL Developer, I issued the statement:

alter session set nls_date_format = 'rrrr-MM-dd"T"HH24:mi:ss."000"'

And then exported the data from SQL Developer to XML again, which now gave me:

2) mxsxl did not like the encoding the SQL Developer included in my XML file (it will give an error like "System does not support the specified encoding.") So I edited the XML file from SQL Developer and changed:

encoding='Cp1252'

to

encoding='utf-8'

In my case, this didn't present a problem, as I have all ASCII characters in my XML file.


3) Then, I created a text file named 'emp.xslt' with the following contents:

4) Finally, using the command-line in Windows, I simply issued:


msxsl emp.xml emp.xslt



And voilà! I was able to take:

And turn it into:

7839
KING
PRESIDENT

1981-11-17T00:00:00.000
5000

10


7934
MILLER
CLERK
7782
1982-01-23T00:00:00.000
1300

10

ready for loading into Application Express, and all with just a little bit of XSLT.

I haven't tested what is output from SQL Developer for CLOBs and BLOBs, so I can't speak for this solution for those types. But for the most common data types, this simple transformation can work quite easily.