Skip to Content

XML Result from JDBC Adapter Stored Procedure

This blog entry was written in collaboration with my colleague Jørgen Michelsen.



Most of us have tried using the JDBC adapter to retrieve data from a database using the nice XML SQL message format to hold our request and response. This works well as long as we are retrieving row sets where each field contains a simple value such as a string or a number. But sometimes the data we need to retrieve is more complex. For instance, some databases (like Oracle) are able to return XML from stored procedure calls, and this is a very convenient way of returning complex data structures.

There is, however, a catch when combining XML results from stored procedures with the JDBC adapter’s XML SQL format: the XML returned by the stored procedure will be wrapped in the XML SQL response structure. More specifically, the XML will be “escaped” and appear as a single character string in the element representing the output parameter of the stored procedure.

This blog demonstrates how to recover the original un-escaped XML using a simple Java mapping.



We have a stored procedure named my_stored_procedure which takes a single input parameter and returns an XML document in the output parameter p_xml. Here is an example of the XML returned:
Example XML

Message type definitions

In order to call this stored procedure using the JDBC adapter, we first have to create a message type for the XML SQL request. The element p_xml corresponds to our output parameter. Message type 1:

Message Type 1

We must also create a message type to hold the XML SQL response from the JDBC adapter. Message type 2:

Message Type 2

In the actual response message, the element p_xml will contain an “escaped” version of the XML document that we are actually interested in. (In other words, reserved XML characters appear as XML entities such as < and >):

Example XML wrapped

Finally, we define a message type for the actual XML document which we will “unwrap”. Its structure must of course be in agreement with the XML returned by the stored procedure. Message type 3:

Message Type 3


Message Interface and Interface Mapping

Since we are getting data, a synchronous interface must be defined using message type 1 and 2 as the request and response, respectively:

Message InterfaceIn the interface mapping the target structure of the request mapping will be message type 1.

For the response mapping, we insert our Java mapping before the mapping program which will transform from message type 3 to whatever format the requester of data needs.
Interface MappingThe Java mapping takes care of the transformation from message type 2 to message type 3, i.e. it extracts and un-escapes the original XML produced by the stored procedure.


Java mapping program

It is sufficient to use a very simple SAX parser in the Java mapping, letting it wait for the appropriate element and then return all the characters in that element. The SAX parser will automatically un-escape XML entities. Here is the source code:


package dk.applicon.xi.mapping;

import java.util.HashMap;
import java.util.Map;

import javax.xml.parsers.FactoryConfigurationError;
import javax.xml.parsers.SAXParser;
import javax.xml.parsers.SAXParserFactory;

import org.xml.sax.Attributes;
import org.xml.sax.InputSource;
import org.xml.sax.SAXException;
import org.xml.sax.helpers.DefaultHandler;


public class GetResponseFromXMLSQLFormat implements StreamTransformation {

   private static final String CHARACTER_ENCODING = "UTF-8";
   private class SAXEventHandler extends DefaultHandler {

      private static final String RESPONSE_XML_ELEMENT_NAME = "p_xml";

      private static final int NOT_READY = 0;
      private static final int READY_FOR_RESPONSE_XML = 1;

      int payloadStatus = NOT_READY;
      Writer outputWriter;

      private SAXEventHandler(Writer outputWriter) {
         this.outputWriter = outputWriter;

      public void startElement(String namespaceURI, String localName, String qName, Attributes atts) throws SAXException {
         if (RESPONSE_XML_ELEMENT_NAME.equals(localName)) {
            payloadStatus = READY_FOR_RESPONSE_XML;

      public void endElement(String namespaceURI, String localName, String qName)   throws SAXException {
         if (RESPONSE_XML_ELEMENT_NAME.equals(localName)) {
            payloadStatus = NOT_READY;

      public void characters(char[] ch, int start, int length) throws SAXException {
         switch (payloadStatus) {
            try {
               outputWriter.write(ch, start, length);
            } catch (IOException e) {
               throw new RuntimeException(e);
         case NOT_READY :
            // Do nothing with any characters outside p_xml element

   private Map param = null;
   private SAXParserFactory parserFactory = null;

   public void execute(InputStream inputStream, OutputStream outputStream) throws StreamTransformationException {
      final String ERR = "Fatal parsing error.";
      try {
         // Prepare input, output and parser
         InputSource inputSrc = new InputSource(new InputStreamReader(inputStream, CHARACTER_ENCODING));
         Writer outputWriter = new BufferedWriter(new OutputStreamWriter(outputStream, CHARACTER_ENCODING));
         SAXEventHandler saxEventHandler = new SAXEventHandler(outputWriter);
         SAXParser parser = parserFactory.newSAXParser();
         // Parse XML
         parser.parse(inputSrc, saxEventHandler);
      } catch (Exception e) {
         throw new StreamTransformationException(ERR, e);

   public void setParameter(Map param) {
      this.param = param;
      if (param == null) {
         this.param = new HashMap();

   private void setSAXParserFactory() throws StreamTransformationException {
      final String ERR = "Fatal: Could not create SAXParser factory.";
      if (parserFactory == null) {
         try {
            parserFactory = SAXParserFactory.newInstance();
         } catch (FactoryConfigurationError e) {
            throw new StreamTransformationException(ERR, e);



Of course, a reverse logic can be applied if you need to post a complex structure to your stored procedure. Then the request mapping would have to “escape and wrap” your XML data into an input element in the XML SQL request.

You must be Logged on to comment or reply to a post.
  • Hi Torsten,

    Good idea this wrapping and this combination of two mappings. Could you please also detailed the script of stored procedure ? and especially the content of p_xml.

    • Hi Mickael,

      We are not at liberty to share the code of the stored procedure or the actual data returned, as this is proprietary to one of our customers. Moreover, I feel this would be irrelevant detail when presenting this generic approach.

      Best regards,

      • Hi Søbirk,

        Excellent Post. I found it to be more useful.

        But When the data from Stored Procedure(say i our case data contained in p_xml contains any special characters like &<> ” ‘ the parsing fails. How to escape them form parsing?

        It would be higly helpful if you could throw some light on this.

        Manikandan Rajendran

  • Great blog article, I’ve printed this one out and it’s going into my big bag-o-tricks!  I think I’ll have a use for this technique in the very near future.
  • Hi Søbirk,

    If the data from Stored Procedure, say, in my case ‘data’ contained in p_xml contains any special characters like &<> ” ‘ then parsing fails. How to make them escape from parsing?

    It would be higly helpful if you could throw some light on this.

    Manikandan Rajendran