4 Replies Latest reply: Dec 11, 2012 6:46 AM by Jon-Eric RSS

    How to extract payload data from SOA database schema using Java

    Jon-Eric
      I am trying to extract the payload data and output as XML text files using Java. Seems that is stored in SOA table XML_DOCUMENT. I am trying the following Java code to get started and it's not working as I would expect. I only get a few actual lines of output and, when I do, I only get the *<?xml version ... ?>* line.

      I appreciate any advice to extract the payload data from the database. Ultimately I will want to include the composite instance ID in the SQL but for now I'm just using the code shown here:


      OracleDataSource ods = new OracleDataSource();
      ods.setURL("soa_db_connection_string");
      ods.setUser("soa_db_user_id");
      ods.setPassword("soa_db_password");

      Connection conn = ods.getConnection();

      String sql = "select document from xml_document where rownum < 10";
      OraclePreparedStatement stmt = (OraclePreparedStatement)conn.prepareStatement(sql);

      DBBinXMLMetadataProvider dbrep = BinXMLMetadataProviderFactory.createDBMetadataProvider();
      dbrep.setConnectionPool(ods);
      dbrep.associateDataConnection(conn);

      OracleResultSet rset = (OracleResultSet)stmt.executeQuery();

      XMLDOMImplementation domimpl = new XMLDOMImplementation();
      BinXMLProcessor proc = BinXMLProcessorFactory.createProcessor(dbrep);
      while (rset.next()) {
           Blob blob = rset.getBlob("DOCUMENT");
           BinXMLStream inpbin = proc.createBinXMLStream(blob);
           BinXMLDecoder dec = inpbin.getDecoder();
           InfosetReader xmlreader = dec.getReader();
           XMLDocument doc = (XMLDocument)domimpl.createDocument(xmlreader);
           doc.print(System.out);
      }
        • 1. Re: How to extract payload data from SOA database schema using Java
          vladodias
          Hi,

          This document has some samples using Java DOM API for XMLType...
          http://docs.oracle.com/cd/E11882_01/appdev.112/e16659/xdb11jav.htm

          Cheers,
          Vlad
          • 2. Re: How to extract payload data from SOA database schema using Java
            Jon-Eric
            I found a method using a slight variation of the code I originally posted. Essentially you remove the DBBinXMLMetadataProvider dbrep portion. I believe, with this included, the XML being extracted is validated against the database (which is referenced as a "metadata provider"). Since the SOA schema doesn't seem to contain the information to validate the XML it returns as blank. If you don't include the dbrep portion then the XML is extracted as desired.

            OracleDataSource ods = new OracleDataSource();
            ods.setURL("soa_db_connection_string");
            ods.setUser("soa_db_user_id");
            ods.setPassword("soa_db_password");

            Connection conn = ods.getConnection();

            String sql = "select document from xml_document where rownum < 10";
            OraclePreparedStatement stmt = (OraclePreparedStatement)conn.prepareStatement(sql);

            OracleResultSet rset = (OracleResultSet)stmt.executeQuery();

            XMLDOMImplementation domimpl = new XMLDOMImplementation();
            BinXMLProcessor proc = BinXMLProcessorFactory.createProcessor();
            while (rset.next()) {
                 BLOB blob = rset.getBLOB("DOCUMENT");
                 BinXMLStream inpbin = proc.createBinXMLStream(blob);
                 BinXMLDecoder dec = inpbin.getDecoder();
                 InfosetReader xmlreader = dec.getReader();
                 XMLDocument doc = (XMLDocument)domimpl.createDocument(xmlreader);
                 doc.print(System.out);
            }
            • 3. Re: How to extract payload data from SOA database schema using Java
              Albin
              You can refer the below blog post to get the payload using JAVA API.

              http://www.albinsblog.com/2011/10/oracle-soa-11g-retrieving-request.html#.UMYgm6ycaS0


              Regards
              Albin I
              http://www.albinsblog.com
              • 4. Re: How to extract payload data from SOA database schema using Java
                Jon-Eric
                Great article! Thank you.