我正在尝试使用JDBC从Oracle表中提取XMLTYPE列并遇到一些问题 [英] I am trying to extract an XMLTYPE column from an Oracle table using JDBC and having some issues

问题描述

我正在尝试使用JDBC从Oracle表中提取XMLTYPE列. 我有查询:

I am trying to extract an XMLTYPE column from an Oracle table using JDBC.
I have the query:

    select "XML_FILE" FROM "TABLE_NAME"

，当我在Oracle SQL Developer中运行查询时，它会完全返回所有XMLTYPE行.

and when I run the query in the Oracle SQL Developer, it returns back all the XMLTYPE rows completely fine.

但是，当我运行以下Java代码并运行相同的查询时，我总是为每一列返回"null".我不确定会出什么问题，并且我尝试过做很多不同的事情，但是没有任何效果.重要说明-每个XMLTYPE字段都有一个XML文件，该文件最多包含60,000行XML.

But, when I run the following Java code, and run the same query, I always get "null" returned for every column. I'm not sure what could be going wrong and I've tried doing many different things, but nothing has been working. Important note - the XMLTYPE fields each have an XML file with at most 60,000 lines of XML.

我正在使用的Java代码:

The Java code I'm using:

String query = "select \"XML_FILE\" FROM \"TABLE_NAME\"";
//creating PreparedStatement object to execute query
PreparedStatement preStatement = null;
try {
   System.out.println("QUERY: "+query);
   preStatement = con.prepareStatement(query);   //con is the Connection obj
} catch (SQLException e) {
   e.printStackTrace();  
}

ResultSet result = null;
try {
   result = preStatement.executeQuery();
} catch (SQLException e) {
   e.printStackTrace();  
}
System.out.println("RESULT SET: "+result.toString());
String dataResult = "";
while(result.next()){
   for(int i=1; i<=numCols; i++){
      dataResult += result.getObject(i)+"\t";
      //dataResult += result.getString(i)+"\t";
   }
   dataResult+="\n";
   System.out.println("RESULT: \'"+dataResult+"\'");
}   

任何帮助将不胜感激.谢谢！

Any help would be greatly appreciated. Thanks!

推荐答案

必须在查询中使用to_clob 选择

Have to use to_clob in the query select

to_clob(xmlelement ( "employee", 'Bob' ))  ) as SQLXMLCOL1 from dual 

然后使用resultSet

这篇关于我正在尝试使用JDBC从Oracle表中提取XMLTYPE列并遇到一些问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！