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

Unable to read data from a SOAP response

tanmoypalit Aug 2, 2012 5:17 PM

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/">

</record>

</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

rareddy Aug 2, 2012 11:32 PM (in response to tanmoypalit)

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
Actions
2. Re: Unable to read data from a SOAP response

shawkins Aug 3, 2012 8:12 AM (in response to rareddy)

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
Actions
3. Re: Unable to read data from a SOAP response

tanmoypalit Aug 6, 2012 10:30 AM (in response to tanmoypalit)

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
Actions

Go to original post