Unable to read data from a SOAP response
tanmoypalit Aug 2, 2012 5:17 PMHi 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