Search code examples
sqloracle-databasexsltxmltype

XML transformation with xsl and DB query

I'm using Java to transform xml files using the Transformer class. I pass a stylesheet (xls) file to the transformer along with the input xml file and that gives me a transformed output xml file.

At the same time I also need to query a database and insert some additional data into the XML file from the database so I'm using JDBC and xQuery to connect to an Oracle database and query a relational table. In the ResultSet I get XML formatted ROWS.

My plan is to pass these rows to the Java Transformer as a parameter and during the transformation process insert this data into the appropriate place. The parameter would contain several rows and O need to pick specific rows during the transformation to be inserted. Will this work?

I tried casting the ResultSet to XMLType but that fails. Is there another object type I could cast the ResultSet to and use it as a parameter to the Transformer?

I hope it makes sense. Many thanks for the answers in advance!

Solution

  • I managed to cast the ResultSet to OracleResultset and then create an XMLType out of that.

    ResultSet rset = stmt.executeQuery();
OracleResultSet orset = (OracleResultSet) rset;
XMLType qRes = XMLType.createXML(orset.getOPAQUE(1));

    This brings up other questions which I'll ask separately.