Oracle - java - get xmlType col into resultset

[Mike]

Hi
I've already posted it in Oracle group but no answer. But I think the
problem is rather programming not the db.

I think I've read everything about Oracle-Java and i still didn't get
it right.
I am desperate.

I want something as simple as that:
I inserted some documents into this table:
CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);

Now i want to retrieve whole documents in my java app and parse it.
I tried lots of combinations.
First tried something that works in SQL2008 and DB2:
qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
rs = st.executeQuery(qString);

and rs has rows but if i getString(1)
while (rs.next())
{ ....
saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
handler); //works with DB2 and MSSQL2008
i get a null pointer exception at saxParser

I system.out the rs.getString(1) instead of parsing it and it prints:
null

So i started to read about oracle and xml an came to this:
qString = "SELECT t.xmlDocum.getStringVal() FROM xmlDocuments t;"; //
gives me java.sql.SQLException: ORA-00911: invalid character
qString = "SELECT SYS_XMLGEN(XMLDOCUM) FROM XMLDOCUMENTS;" //no
results again


And if i use these queries in oracle command line tool:
SELECT t.xmlDocum.getStringVal() FROM xmlDocuments t;
SELECT XMLDOCUM FROM XMLDOCUMENTS;

SET LONG 10000;
SELECT XMLDOCUM.getStringVal() FROM XMLDOCUMENTS;

oracle gives me my documents. Connecting sys as sysdba from both java
and cmd.
i am using: oracle.jdbc.pool.OracleDataSource driver/connection:
OracleDataSource ds;
ds = new OracleDataSource();
ds.setURL(url);
conn = ds.getConnection(username, password);

connection works and I get other results:
SELECT count(*) from XMLDOCUMENTS;
i get 4 because 4 docs are in the table right now.

Please help me.
Mike

 

[Sigfried]

Mike a écrit :

Hi
I've already posted it in Oracle group but no answer. But I think the
problem is rather programming not the db.

I think I've read everything about Oracle-Java and i still didn't get
it right.
I am desperate.

I want something as simple as that:
I inserted some documents into this table:
CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);

Now i want to retrieve whole documents in my java app and parse it.
I tried lots of combinations.
First tried something that works in SQL2008 and DB2:
qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
rs = st.executeQuery(qString);

and rs has rows but if i getString(1)
while (rs.next())
{ ....
saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
handler); //works with DB2 and MSSQL2008
i get a null pointer exception at saxParser

I system.out the rs.getString(1) instead of parsing it and it prints:
null

Please try the getCharacterStream() method.

 

[Sigfried]

Mike a écrit :

Hi
I've already posted it in Oracle group but no answer. But I think the
problem is rather programming not the db.

I think I've read everything about Oracle-Java and i still didn't get
it right.
I am desperate.

I want something as simple as that:
I inserted some documents into this table:
CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);

Now i want to retrieve whole documents in my java app and parse it.
I tried lots of combinations.
First tried something that works in SQL2008 and DB2:
qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
rs = st.executeQuery(qString);

and rs has rows but if i getString(1)
while (rs.next())
{ ....
saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
handler); //works with DB2 and MSSQL2008
i get a null pointer exception at saxParser

I system.out the rs.getString(1) instead of parsing it and it prints:
null

Please try the getCharacterStream() method.

 

[Tim Slattery]

Hi
I've already posted it in Oracle group but no answer. But I think the
problem is rather programming not the db.

I think I've read everything about Oracle-Java and i still didn't get
it right.
I am desperate.

I want something as simple as that:
I inserted some documents into this table:
CREATE TABLE xmlDocuments(docId NUMBER, xmlDocum XMLType);

Now i want to retrieve whole documents in my java app and parse it.
I tried lots of combinations.
First tried something that works in SQL2008 and DB2:
qString = "SELECT XMLDOCUM FROM XMLDOCUMENTS;";
rs = st.executeQuery(qString);

and rs has rows but if i getString(1)
while (rs.next())
{ ....
saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
handler); //works with DB2 and MSSQL2008
i get a null pointer exception at saxParser

This page might help:
http://www.oracle.com/technology/sample_code/tech/java/codesnippet/jsp/xmltype/index.html

 

[Lew]

got it!!

Please do not discussion-post.

i [sic] use this query:

queryString = "select x." + column + ".getStringVal() from " + table +
" x";

This way of building SQL queries is subject to SQL service attacks and
mistakes. It is better to use 'PreparedStatement' and set meats.

--
Lew


- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
"The biggest political joke in America is that we have a
liberal press.

It's a joke taken seriously by a surprisingly large number
of people... The myth of the liberal press has served as a
political weapon for conservative and right-wing forces eager
to discourage critical coverage of government and corporate
power ... Americans now have the worst of both worlds:
a press that, at best, parrots the pronouncements of the
powerful and, at worst, encourages people to be stupid with
pseudo-news that illuminates nothing but the bottom line."

-- Mark Hertzgaard

 

[Mike]

This page might help:http://www.oracle.com/technology/sample_code/tech/java/codesnippet/js...

Hello
Thank you for your help.
I tried both solutions and both of them didn't work:

1.
I get exception when using getCharacterStream:
java.sql.SQLException: Niepoprawny typ kolumny: getCharacterStream not
implemented for class

oracle.jdbc.driver.T4CNamedTypeAccessor

2.
and there is no method:
registerOutParameter (1, OracleTypes.OPAQUE,"SYS.XMLTYPE");
in new Oracle driver (no OracleCallableStatement)

any other ideas how to solve this problem?
Mike

 

[Mike]

got it!!
i use this query:

queryString = "select x." + column + ".getStringVal() from " + table +
" x";

and then:

saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
handler);
and rs.getString(1) gives me full xml document

once again than you for all your help
Mike

 

[rajashekar.katla01]

got it!!
i use this query:

queryString = "select x." + column + ".getStringVal() from " + table +
" x";

and then:

saxParser.parse(new InputSource(new StringReader(rs.getString(1))),
handler);
and rs.getString(1) gives me full xml document

once again than you for all your help
Mike

Thank you so much for posting the solution here, it saved my time.
Take care...
-Raj