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.

Good XSL And XPath Advice

A few months ago I came across a post that I've gotten a lot of mileage out of while working on the Oracle Service Bus (OSB). I can't remember where I saw it or who said it, otherwise I would cite it, but to paraphrase:

If your XPath query is not returning any results (and you think it should), it is almost always a namespace issue.

OK, so maybe it is common knowledge, common sense, or whatever for everyone else, but for me, remembering that one statement makes XSL, XPath, and XQuery much less frustrating to deal with.

Take that statement along with the issues with the default namespace, and it can be a lifesaver.

If you have an XML with a default namespace and try searching for, say "//somenode", it won't be found, because "//somenode" will only match somenode's without any namespace. The somenode's in an XML doc with a default namespace does have a namespace.