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:
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.