3 Replies Latest reply on Aug 6, 2012 10:30 AM by tanmoypalit

    Unable to read data from a SOAP response

    tanmoypalit Newbie

      Hi Everyone,

       

      I am trying to consume a SOAP web service in Teiid and create a view model out of it. My SOAP response is like the following:

      <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">

        <soap:Body>

          <ns2:getHeaderResponse xmlns:ns2="http://services.data.com.report/">

            <return>

              <response requestid="12345678">

                <report name="DataReport" recordsExceeded="false">

                  <recordcount>2</recordcount>

                  <record datasource="SOURCEA" recid="100000000999999" recordno="1">

                    <attribute contextkey="" dbcolumnname="COUNTY" display="true" groupname="Location" mapuse="false" name="County" referencecolumn="" tablename="DATA_TABLE" title="false" type="string" value=""/>

                    <attribute contextkey="" dbcolumnname="STATE_OR_PROVINCE" display="true" groupname="Location" mapuse="false" name="State or Province" referencecolumn="" tablename="DATA_TABLE" title="false" type="string" value=""/>

                    <attribute contextkey="" dbcolumnname="COUNTRY_NAME" display="true" groupname="Location" mapuse="false" name="Country" referencecolumn="" tablename="DATA_TABLE" title="false" type="string" value="UNITED KINGDOM"/>

                    <attribute contextkey="" dbcolumnname="CURRENT_PURPOSE" display="true" groupname="General" mapuse="false" name="purpose" referencecolumn="" tablename="DATA_TABLE" title="false" type="string" value="DEVELOPMENT"/>

                    <attribute contextkey="" dbcolumnname="CURRENT_STATUS" display="true" groupname="General" mapuse="false" name="Status" referencecolumn="" tablename="DATA_TABLE" title="false" type="string" value="UNKNOWN"/>

                  </record>

                  <record datasource="SOURCEB" recid="100000000999991" recordno="2">          

                    <attribute contextkey="" dbcolumnname="COUNTY" display="true" groupname="Location" mapuse="false" name="County" referencecolumn="" tablename="DATA_TABLE" title="false" type="string" value=""/>

                    <attribute contextkey="" dbcolumnname="STATE_OR_PROVINCE" display="true" groupname="Location" mapuse="false" name="State or Province" referencecolumn="" tablename="DATA_TABLE" title="false" type="string" value=""/>

                    <attribute contextkey="" dbcolumnname="COUNTRY_NAME" display="true" groupname="Location" mapuse="false" name="Country" referencecolumn="" tablename="DATA_TABLE" title="false" type="string" value="UNITED KINGDOM"/>            

                    <attribute contextkey="" dbcolumnname="CURRENT_PURPOSE" display="true" groupname="General" mapuse="false" name="purpose" referencecolumn="" tablename="DATA_TABLEBORE" title="false" type="string" value="DEVELOPMENT"/>

                    <attribute contextkey="" dbcolumnname="CURRENT_STATUS" display="true" groupname="General" mapuse="false" name="Status" referencecolumn="" tablename="DATA_TABLE" title="false" type="string" value="UNKNOWN"/>           

                  </record>

                </report>

              </response>

            </return>

          </ns2:getHeaderResponse>

        </soap:Body>

      </soap:Envelope>

       

      and my procedure looks like this:

      CREATE VIRTUAL PROCEDURE

      BEGIN

                DECLARE string VARIABLES.INPUT_ENDPOINT;

                VARIABLES.INPUT_ENDPOINT = ServiceManagerServiceView.getDataReport.soapstring;

                SELECT DataHeader.* FROM (EXEC ServiceManagerService.invoke(binding => 'SOAP11', action => '', request => XMLPARSE(DOCUMENT VARIABLES.INPUT_ENDPOINT))) AS ws, XMLTABLE('/*:getHeaderResponse/return/response/report/record' PASSING ws.result COLUMNS ATTRIBUTES XML PATH 'attribute') AS DataHeader;

      END


       

      I call the procedure, it runs without any error but I don't see any data....

      SELECT * FROM (EXEC getDataReport('<entire SOAP Request as String>')) as X

       

      Any ideas, whats is going wrong here?

       

      Regards

      Tanmoy

        • 1. Re: Unable to read data from a SOAP response
          Ramesh Reddy Master

          Try..

           

          CREATE VIRTUAL PROCEDURE

          BEGIN

                    DECLARE string VARIABLES.INPUT_ENDPOINT;

                    VARIABLES.INPUT_ENDPOINT = ServiceManagerServiceView.getDataReport.soapstring;

                    SELECT DataHeader.* FROM (EXEC ServiceManagerService.invoke(binding => 'SOAP11', action => '', request => XMLPARSE(DOCUMENT VARIABLES.INPUT_ENDPOINT))) AS ws, XMLTABLE('/*:getHeaderResponse/return/response/report/record' PASSING ws.result COLUMNS ATTRIBUTES XML PATH 'attribute/@value') AS DataHeader;

          END

          1 of 1 people found this helpful
          • 2. Re: Unable to read data from a SOAP response
            Steven Hawkins Master

            Ramesh using an XML type and path to an element is perfectly valid.  It doesn't appear Tanmoy is looking for a particular attribute value.

             

            Tanmoy, you'll first want to validate "EXEC ServiceManagerService.invoke(binding => 'SOAP11', action => '', request => XMLPARSE(DOCUMENT VARIABLES.INPUT_ENDPOINT)))" to make sure you are getting back the document you expect. If for example you are using the MESSAGE web service mode, then the response will contain the SOAP envelope as well.  You can also use XMLPARSE(DOCUMENT doc WELLFORMED) in situations like this to skip the overhead of validating the document as part of XMLPARSE.

             

            Iterating on record though may not be what you want.  It probably makes more sense to iterate on attribute and use the PATH '.'

             

            Steve

            1 of 1 people found this helpful
            • 3. Re: Unable to read data from a SOAP response
              tanmoypalit Newbie

              Steven and Ramesh,

               

              Both of your answers helped me out!!

               

              I was looking for the /@value eventually and I am using MESSAGE instead of PAYLOAD so response did contain the SOAP envelope as well.

               

              Thanks a lot!!!

              Tanmoy