Thursday, December 10, 2009

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.

4 comments:

Byte64 said...

Hi Joel,
this was also the subject of an article i published some time ago, when i found out that SQLDeveloper's export format had changed.

Theoretically there should be a way to make the translation platform independent, (read M$ independent) by specifying the XSL stylesheet in the prolog of the XML file using the xml-stylesheet processing instruction.
I believe Firefox and Opera support it, as well as Internet Explorer.
At that point i guess one could save the resulting output as an XML file and voilá!

Flavio

Gussay said...
This comment has been removed by the author.
Gussay said...

Hi Joel,

Thanks for this, very useful!

I got the following error when using your example with msxsl.
I kept getting "Reference to undeclared namespace prefix: 'xsl'."

To fix, I had to add 'xmlns:' before the xsl declaration
<xsl:stylesheet version = "1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

and then add a trailing slash to the 'output' and the 'value-of' tags
<xsl:output method="xml" version="1.0" encoding="utf-8" indent="yes"/>
<xsl:value-of select="text()"/>

I also had to do another export of EMP table data from SQL developer
as the test data didn't transform (possibly because of the lower case tags in the test data?)

Cheers,
Gus..

Joel R. Kallman said...

@Flavio - sorry, I should read your blog more. ;)

@Gus - thanks for pointing out these errors. I need to QA my blog posts better. When I copied and pasted the XML data into blogger, for whatever reason, the tags were converted to lowercase (I still have the original data files I copied from). Same thing for the 'xmlns'. I corrected my post.

Lessons learned:

1) Retest after publishing post
2) Only copy XML when editing the raw HTML of the blog post.

Thanks again Gus.