Sunday, August 26, 2012

Reading XMLType with DB Adapter

My Oracle table has a column with XMLType. I have found out it can be very tricky when reading the XMLType column with DB Adapter.

First of all, if you use TopLink to map the data, BPEL will read in the XMLType column as a string. 

That string variable is no longer an XML element inside BPEL, and you can no longer process it. In fact, it's very trickery to the eyes if you view your DB read output in the EM console, it appears that the XML payload is read in successfully, but that's not true. If you check the raw XML view, you'll see the "fake" XML element is a string with special escaped characters "<". So you can not use this scrambled string for much of anything.

<messagePayload>&lt;cws:cwsPaymentEventService xmlns:cws="http://xmlns.calwater.com/cwsPaymentEvent.xsd"> &lt;cws:cwsPaymentEventDetails PayorAccountID="0956488888" PaymentAmount="12" PaymentDate="2012-08-12T01:02:03" TenderSource="KUBRA-RT"> &lt;/cws:cwsPaymentEventDetails> &lt;/cws:cwsPaymentEventService></messagePayload>

One way to tell if your variable is an XML structure or a string (with "fake" XML structure) is to see if the XML elements are shown as expanded or on one line.

So what if I need to read in the XMLType column and still want to process it as an XML structure? The work around is to use "pure SQL" to read the element in. Then it preserves the XML structure. You can assign the "pure SQL" read output into a variable of any XML structure. It's up to you to make sure that DB field (XMLType) and your BPEL variable have the same structure! Great flexibility, but use with care.

What if you need to do a DB poll with DBAdapter? DB poll doesn't have an option of "pure SQL". My solution: do two reads. First the poller loads the row in, then you need do another regular DB read for the same row (with your self defined DB key), this type you use "pure SQL". This is definitely inefficient to say the least, but it's one way to get around the limitation of DBAdapter and toplink.

I think the alternative to avoid two reads is to use parseEscapedXML() function. My colleague reminded me on that today, I vaguely remember we used that function on a previous project. But I haven't tried it this time.  I believe it should work as the function name indicated. You parse that string, and place the result into your XML element.

Additionally, if it is an option for you, you can use AQAdapter. AQ adapter can load in XMLType just fine. It's a one step solution.


No comments:

Post a Comment