XML to JSON conversion [oracle.xdb.XMLType to JSON object] performance issue

Question

The application model is UI<->JavaServerside<->Oracle StoredProcedures[DB]

I retrieve the XML data received from the Stored procedure XML-Out and pass it to the UI as a JSON object.

Here's the snippet.

import oracle.xdb.XMLType;
import org.json.JSONObject;

XMLType studentsdataXML = null;
JSONObject xmlJSONObj = null;

studentsdataXML = (XMLType) callableStatement.getObject(5);

String xmlString = studentsdataXML.getString();
xmlJSONObj = XML.toJSONObject(xmlString); // using org.json library

//return xmlJSONObj ;

The above code works well, converts the XML to JSON object , BUT the performance issue is when performing the studentsdataXML.getString() It takes about 3/4th of total execution time[from UI back to UI].

Question is whether I can do a direct XML to JSON conversion? [oracle.xdb.XMLType to JSON object] or any suggestions for different library that can do this

org.json library used: http://www.json.org/java/

Update1: Updating the getString() to getStringVal() ie: String xmlString = studentsdataXML.getStringVal();

getStringVal() - http://docs.oracle.com/cd/B28359_01/appdev.111/b28391/oracle/xdb/XMLType.html#getStringVal__

This article recommends to use getStringVal() to get the string value - http://docs.oracle.com/cd/B19306_01/appdev.102/b14259/xdb11jav.htm#g1039140

Also, Time measuring snippet:

...

long stime1 = System.currentTimeMillis();
String xmlString = studentsdataXML.getStringVal();
long etime1 = System.currentTimeMillis();
log.info("Total time (in ms) for XML object to String conversion : " + (etime1 - stime1));
long stimexml = System.currentTimeMillis();
xmlJSONObj = XML.toJSONObject(xmlString);
long etimexml = System.currentTimeMillis();
log.info("Total time (in ms) for XML String to JSON conversion : " + (etimexml - stimexml));

...Total time (in ms) for execute query to retreive XML : 1308

Total time (in ms) for XML object to String conversion : 31452

Total time (in ms) for XML String to JSON conversion : 423

Update2: Another SO thread with somehwat similar issue, but unaswered- Slow to convert Oracle 11g XMLType into Java String or Document

Update3:

When I call the getStringVal() after closing the connection, I get the exception - java.sql.SQLRecoverableException: Closed Connection

is the time taken in the getString() method due to pulling from the db or converting to a String? — jtahlborn
It is when converting to a String, because I measured the time just before and after the studentsdataXML.getString(); execution — spiderman
ohhk, I updated getString() to getStringVal(). But the issue remains the same. As per the documentation, the method get the string value containing the XML data from the XMLType. I updated my post. When you specifically asked that, I noticed the exception thrown as per API, it says SQLException. So its pulling from DB!? — spiderman

Baderous Baderous · Accepted Answer · 2014-10-24T14:07:30

I faced a similar problem some months ago, and after more than one week of searching, testing and scratching through oracle.xdb package, I found a solution that works. In my scenario, I had a String representing a big XML and wanted to convert it to XMLType in order to save it to a XMLTYPE column in the database. As I needed an auxiliary oracle.sql.CLOB to pass as the argument of XMLType.createXML method, I created this method:

private CLOB createClobFromStringStreaming(String xml, Connection conn) throws SQLException, IOException {
        CLOB clob = CLOB.createTemporary(conn, false, CLOB.DURATION_SESSION);       
        BufferedReader br = new BufferedReader(new StringReader(xml));
        char[] buffer = new char[1024/*clob.getChunkSize()*/];
        int read = 0;
        Writer w = clob.setCharacterStream(0L);
        try {
            for (read = br.read(buffer); read > -1; read = br.read(buffer)) {
                w.write(buffer, 0, read);
                w.flush();
            }
        } catch (IOException e) {
            throw e;
        } finally {
            try {
                w.flush();
                w.close();
                br.close();
            } catch (IOException e1) {
                throw e1;
            }
        }

        return clob;
    }

Instead of trying to put the whole String directly into a XMLType variable, I divided it in chunks and streamed it into the variable. After trying a number of different chunk sizes, I found that the perfect size for maximum performance was 1024. I don't know why this happens but it was the best solution for me.

After that, I just need to invoke the method like this:

XMLType xml = XMLType.createXML(conn, this.createClobFromStringStreaming(this.eventXml, conn));

After this, I achieved normal times of XMLType creation, instead of 4 to 10 seconds that I was getting before. So, you should try a similar approach but in the reverse direction. Try getting a CLOB from your XMLType using, for instance, getClobVal() and work your CLOB after that into a String. I am not sure if you can do something with getInputStream() also, you'll have to try.

XML to JSON conversion [oracle.xdb.XMLType to JSON object] performance issue

1 Answers