XML clob w/ SQL module
Monday, 24 March 2014 · 4 messages · last reply 25 Mar 2014
BE
Bednar, Scott E24 Mar 2014 01:42 #
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
CG
Christian Grün24 Mar 2014 10:18 #
Hi Scott,
Quoted text · Bednar, Scott E · 3 lines
> 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 can well imagine that our SQL Module implementation [1] could be extended to support more data types, but we should probably try to stick with the standard Java SQL types. Do you know if oracle.sql.CLOB inherits some class from the "java.sql" package?
Christian
[1] https://github.com/BaseXdb/basex/blob/master/basex-core/src/main/java/org/basex/query/func/FNSql.java
BE
Bednar, Scott E24 Mar 2014 21:57 #
Christian, thanks for the response. It does look like Oracle JDBC implements java.sql.* interfaces:
http://docs.oracle.com/cd/E18283_01/java.112/e16548/jdbcvers.htm
Turns out the existing SQL module does read java.sql.SQLXML object values (line 373). To make it work, you need the ojdbc6.jar, xdb.jar and xmlparserv2.jar files on your classpath, and set this property: -Doracle.jdbc.getObjectReturnsXMLType="false"
I got it working, but the results were not what I was expecting: the XML is escaped (i.e. >element_name<, etc) within the sql:column element. Maybe this is the "fallback" string representation (line 380 in the module)? Any suggestions for getting the actual XML into BaseX?
Thanks, Scott
Quoted text · Christian Grün · 13 lines
-----Original Message----- From: Christian Grün [mailto:christian.gruen at gmail.com] Sent: Monday, March 24, 2014 2:19 AM To: Bednar, Scott E Cc: basex-talk at mailman.uni-konstanz.de Subject: Re: [basex-talk] XML clob w/ SQL module Hi Scott, > 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 can well imagine that our SQL Module implementation [1] could be extended to support more data types, but we should probably try to stick with the standard Java SQL types. Do you know if oracle.sql.CLOB inherits some class from the "java.sql" package? Christian [1] https://github.com/BaseXdb/basex/blob/master/basex-core/src/main/java/org/basex/query/func/FNSql.java
CG
Christian Grün25 Mar 2014 11:24 #
Hi Scott,
Quoted text · Bednar, Scott E · 1 line
> I got it working, but the results were not what I was expecting: the XML is escaped (i.e. >element_name<, etc) within the sql:column element. Maybe this is the "fallback" string representation (line 380 in the module)? Any suggestions for getting the actual XML into BaseX?
Yes, it seems as if the input is not recognized as valid XML. I have just uploaded a new snapshot [1]. If you start BaseX with the -d flag, it generates some additional debugging info. Could you give it a try? As an alternative, you could also send me one or two of the escaped XML snippets.
Christian
[1] http://files.basex.org/releases/latest/
Quoted text · Bednar, Scott E · 22 lines
> > Thanks, Scott > > > -----Original Message----- > From: Christian Grün [mailto:christian.gruen at gmail.com] > Sent: Monday, March 24, 2014 2:19 AM > To: Bednar, Scott E > Cc: basex-talk at mailman.uni-konstanz.de > Subject: Re: [basex-talk] XML clob w/ SQL module > > Hi Scott, > >> 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 can well imagine that our SQL Module implementation [1] could be extended to support more data types, but we should probably try to stick with the standard Java SQL types. Do you know if oracle.sql.CLOB inherits some class from the "java.sql" package? > > Christian > > [1] https://github.com/BaseXdb/basex/blob/master/basex-core/src/main/java/org/basex/query/func/FNSql.java
Written for BaseX 7.8. BaseX may have changed since: please check the current documentation.
⚡Generated with XQuery