Query XML Payload of a SOA Composite

Although SOA has a user-friendly enterprise manager console to view the instances/flows/payloads, sometimes we would end up wanting to programmatically fetch the payload from the backend Soa-infra tables.

The following code would be useful in such cases. 

Following query on the SOA INFRA tables will fetch the xml payload as BLOB.

SELECT A.DOCUMENT FROM XML_DOCUMENT A,INSTANCE_PAYLOAD B,COMPOSITE_INSTANCE C 

WHERE A.DOCUMENT_ID = B.PAYLOAD_KEY  

AND B.INSTANCE_ID = C.ID  

AND B.INSTANCE_TYPE='COMPOSITE'  

AND A.DOCUMENT_TYPE = 2  

AND B.INSTANCE_ID = <<InstanceNumber>>;  

We are querying against the following tables to fetch xml payload. We are querying against the following tables to fetch the xml payload

XML_DOCUMENT - Table in which the actual payload xml is stored as BLOB

INSTANCE_PAYLOAD - Table which provides the payload metadata of the instance

COMPOSITE_INSTANCE - Table which provides the composite instance details

Now, the next step is to convert the BLOB into actual xml payload.

Following Java class code can be used to convert and print the xml payload of the instance.

package client;

import java.io.IOException;

import java.sql.*;

import oracle.xml.parser.v2.XMLDOMImplementation;

import oracle.xml.binxml.BinXMLStream;

import oracle.xml.binxml.BinXMLDecoder;

import oracle.xml.binxml.BinXMLException;

import oracle.xml.binxml.BinXMLProcessor;

import oracle.xml.scalable.InfosetReader;

import oracle.xml.parser.v2.XMLDocument;

import oracle.xml.binxml.BinXMLProcessorFactory;

public class QueryPayload {

    public QueryPayload() {

        super();

    }

    

    public static void main(String args[]) throws BinXMLException, IOException {

        try{

            Class.forName("oracle.jdbc.OracleDriver");            

            Connection con=DriverManager.getConnection( 

            "jdbc:oracle:thin:@test/dbconnection","userid","password");

            Statement stmt=con.createStatement();

           

            String sqlstmt = " select a.document from xml_document a,

            "            instance_payload b,composite_instance c \n" +

            "            where a.document_id = b.payload_key  \n" +

            "            and b.instance_id = c.id  \n" +

            "            and b.instance_type='composite'  \n" +

            "            and a.DOCUMENT_TYPE = 2  \n" +

            "            and b.instance_id = 123456    ";

            ResultSet rs = stmt.executeQuery(sqlstmt);

            while(rs.next()) {          

               XMLDOMImplementation domimpl = new XMLDOMImplementation();

               BinXMLProcessor proc = BinXMLProcessorFactory.createProcessor();

               BinXMLStream inpbin;

            

                inpbin = proc.createBinXMLStream(rs.getBlob(1));

                BinXMLDecoder dec = inpbin.getDecoder();

                InfosetReader xmlreader = dec.getReader();

                XMLDocument doc = (XMLDocument)domimpl.createDocument(xmlreader);

                doc.print(System.out);               

            }

            con.close();

        } catch (ClassNotFoundException e) {

        } catch (SQLException e) {

        }

    } }

Hope this blog was helpful.