Just sharing some information I've learned over the past week about using BaseX with Oracle using the SQL Module... in particular, to extract XML out of CLOB fields in Oracle you need to jump through a couple hoops:
To get things set up, the following jars need to be on the classpath (I just put them into the BaseX lib directory which gets picked up if you start BaseX using one of the .bat scripts): - ojdbc6.jar - xdb6.jar - xmlparserv2.jar
With this last jar, xmlparserv2.jar, there are a few things to watch for. First, you need a later version of the jar. Earlier versions are smaller (the jar for 11g is about 1350KB, the jar you have should probably be at least that big), and the filename isn't different for different versions. Be sure of what you have... you can find it in Oracle distributions here: {ORACLE_HOME}/oracle/produce/{VERSION_NUMBER}/lib/xmlparserv2.jar
The jar can also be found under /lib in the Oracle client distributions.
Next, you will need to reconfigure the out-of-the-box xmlparserv2.jar to use some standard java components rather than Oracle's: Change the entries in the files in the META-INF/services as follows: - for javax.xml.parsers.DocumentBuilderFactory replace oracle.xml.jaxp.JXDocumentBuilderFactory with com.sun.org.apache.xerces.internal.jaxp.DocumentBuilderFactoryImpl - for javax.xml.parsers.SAXParserFactory replace oracle.xml.jaxp.JXSAXParserFactory with com.sun.org.apache.xerces.internal.jaxp.SAXParserFactoryImpl - for javax.xml.transform.TransformerFactory replace oracle.xml.jaxp.JXSAXTransformerFactory with com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl
The answer on this thread has complete instructions on reconfiguring the jar: http://stackoverflow.com/questions/11853967/errors-with-xmlparserv2-jar-and-...
Finally, you will need to add the following property setting to the startup script you are using (basex.bat, basexhttp.bat, basexgui.bat): -Doracle.jdbc.getObjectReturnsXMLType="false"
This tells Oracle to return the java standard java.sql.SQLXML type that BaseX is expecting rather than the proprietary oracle.xdb.XMLType. See: http://docs.oracle.com/cd/E18283_01/java.112/e16548/jdbcvers.htm#BABGHBCC
Here's how I added it to the script: set PROPS=-Doracle.jdbc.getObjectReturnsXMLType="false" java -cp "%CP%" "%PROPS%" %VM% org.basex.BaseX %*
Once you have done all of that, you should be able to run an XQuery like this to get XML out of Oracle:
sql:init("oracle.jdbc.OracleDriver"), let $conn := sql:connect("jdbc:oracle:thin:@myoracledb.org:1523:mydbsid", "username","password") return sql:execute($conn, "SELECT xmltype(MyXMLClob) from MyTable")
If you get escaped XML in you results, you still need to reconfigure your xmlparserrv2.jar as shown above.
As a side note: the above configuration is also necessary to make the basexhttp.bat script successfully start the server. If you use the un-configured xmlparserrv2.jar it will not start.
Hope this helps!
Cheers, Scott Bednar
-----Original Message----- From: Bednar, Scott E Sent: Sunday, March 23, 2014 5:43 PM To: 'basex-talk@mailman.uni-konstanz.de' Subject: XML clob w/ SQL module
I'm trying to extract XML stored in a CLOB from an oracle database using the sql module for BaseX. When I select the clob value, I get this sort of thing:
<sql:column name="MSG">oracle.sql.CLOB@49458ef3</sql:column>
I've tried casting it to XMLType (similar message) and to char (buffer size too small for the XML I'm dealing with).
Is there a way of doing this short of creating a specialized Java module for it?
Thanks, Scott