Insert xml file into XMLTable using JDBC

Question

I have created a XMLType table in Oracle. I am trying to insert an XML file into the table using JDBC. It is throwing -

ORA-00932: inconsistent datatypes: expected - got BINARY

The code is -

       OraclePreparedStatement  statement = (OraclePreparedStatement) getConnection().prepareStatement
       ("insert into person values(?)");
       FileInputStream fileinp =  new FileInputStream(file);
       statement.setBinaryStream(1, fileinp, fileLength);      
       statement.executeUpdate();

Answer 1

You're trying to insert binary data directly into your XMLType column, and there is no implicit casting for that. Assuming your file is actually text you can treat is as a CLOB rather than a BLOB:

OraclePreparedStatement statement =
  (OraclePreparedStatement) getConnection().prepareStatement(
    "insert into person values(xmltype(?))");
FileInputStream fileinp =  new FileInputStream(file);
InputStreamReader filerdr = new InputStreamReader(fileinp);
pStmt.setCharacterStream(1, filerdr, fileLength);
pStmt.executeUpdate();

Note that the statement is now using xmltype(?) (although it works without that as there is implicit casting from CLOB, but I think it's better to be explicit anyway); and I'm using an InputStreamReader to pass the text in. You can, and probably should, use a buffered reader:

FileInputStream fileinp =  new FileInputStream(file);
InputStreamReader filerdr = new InputStreamReader(fileinp);
BufferedReader filebuf = new BufferedReader(filerdr);
pStmt.setCharacterStream(1, filebuf, fileLength);
pStmt.executeUpdate();

Tested with an XMLType column in a normal table, and with an XMLType table.

Passing a text file with setBinaryStream confuses XMLType; with the same valid file, using setBinaryStream() gets error:

ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of '3'

There's probably a way around that, but I'm assuming your file is just text and won't be a problem as a CLOB.