Oracle Service Bus - Getting XML Out of Table Column

This took me a while to figure out, and ended up being pretty simple once I knew which XQuery functions were available.

Let's say you have a table you need to query from the OSB. The result set you get back from the JCA DB adapters gets turned into XML for you. If one of the columns is a LOB containing XML, then it gets escaped using CDATA. Anything in CDATA is basically ignored by XML, XSL, XPath, etc.

It is a 2 step process to extract that value and make it usable XML:

1. Use the fn:string() function to turn the value into a string. In this case, it strips off the CDATA wrapper and gives you a string that looks like XML.
2. Use the fn-bea:inlinedXML() function to parse that string into XML.

You can use an ASSIGNMENT task to jam that XML into a variable, and then run any XSL, XPath, etc on it. You can also use INSERT or REPLACE tasks to embed it back into your original XML.