Consuming SOAP web service dynamically in loop
Hi,
I am a business intelligence analyst and I have no prior experience in SOAP. I work with SQL server, POWER BI and SSAS for my role.
The current scenario is I need to obtain the data from one of our service providers (e.g. XYZ) through web service calls. XYZ has provided me a list of service calls and they only use SOAP based protocols.
The problem I am facing is to loop values obtained in call1 to a subsequent call2 to obtain all the info at once.
Call1 is called getSiteList (with 4 parameters- userName, password, custNbr,lastModifiedDt) which returns the XML containing all the sites.
Sample Call
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bil="http://billing.xyz.cc/">
<soapenv:Header/>
<soapenv:Body>
<bil:getSiteList>
<!--userName-->
<arg0>username</arg0>
<!--password-->
<arg1>pswd</arg1>
<!--custNbr-->
<arg2>288</arg2>
<!--lastModifiedDt-->
<!--<arg3>?</arg3>-->
</bil:getSiteList>
</soapenv:Body>
</soapenv:Envelope>
username, password and custNbr are static value.
The call returns the info which is like this
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<soap:Body>
<ns2:getSiteListResponse xmlns:ns2="http://billing.xyz.cc/">
<return>
<city>city1</city>
<customerNbr>288</customerNbr>
<siteId>0862578</siteId>
<siteName>site1</siteName>
<state>state1</state>
</return>
<return>
<city>city33</city>
<customerNbr>288</customerNbr>
<siteId>101567</siteId>
<siteName>site94</siteName>
<state>state12</state>
</return>
<return>
<city>city1</city>
<customerNbr>288</customerNbr>
<siteId>108952</siteId>
<siteName>site85</siteName>
<statestate1C</state>
</return>
</ns2:getSiteListResponse>
</soap:Body>
</soap:Envelope>
Now, once I am done with this, I want to run a subsequent call named getSiteInfo (with 4 parameters- userName, password, custNbr,siteID) which looks like this
<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bil="http://billing.xyz.cc/">
<soapenv:Header/>
<soapenv:Body>
<bil:getSiteInfo>
<!--userName-->
<arg0>username</arg0>
<!--password:-->
<arg1>pswd</arg1>
<!--custNbr-->
<arg2>288</arg2>
<!--siteId-->
<arg3>0862578</arg3>
</bil:getSiteInfo>
</soapenv:Body>
</soapenv:Envelope>
Now, my question is how can I call getSiteInfo to run in loop for all the siteId obtained in getSiteList at once. My end goal is to run this call in SQL server. I have already tested getSiteList in SQL server and succeeded. The sample code is following.
USE TestDB
DECLARE
@Url varchar(1024),
@HttpMethod varchar(10),
@SoapAction varchar(8000)
--@response xml,
--@status varchar(50)
DECLARE @t table (strxml xml)
set @Url = 'https://portal.xyz.com'
set @HttpMethod = 'soap'
set @SoapAction = '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:bil="http://billing.xyz.cc/">
<soapenv:Header/>
<soapenv:Body>
<bil:getSiteList>
<!--userName:-->
<arg0>username</arg0>
<!--password:-->
<arg1>pswd</arg1>
<!--custNbr:-->
<arg2>288</arg2>
<!--lastModifiedDt:-->
<!--<arg3>?</arg3>-->
</bil:getSiteList>
</soapenv:Body>
</soapenv:Envelope>'
declare @obj int
,@responseText xml
,@statusText varchar(1024)
,@method varchar(10) = (case when @HttpMethod in ('soap','SOAP') then 'POST' else @HttpMethod end)
exec sp_OACreate 'MSXML2.ServerXMLHTTP', @obj out
exec sp_OAMethod @obj, 'Open', null, @method, @Url, false
declare @host varchar(1024) = @Url
--if @SoapAction is null
-- raiserror('@SoapAction is null', 10, 1)
/*declare @host varchar(1024) = @Url
if @host like 'http://%'
set @host = right(@host, len(@host) - 7)
else if @host like 'https://%'
set @host = right(@host, len(@host) - 8)
if charindex(':', @host) > 0 and charindex(':', @host) < charindex('/', @host)
set @host = left(@host, charindex(':', @host) - 1)
else
set @host = left(@host, charindex('/', @host) - 1)*/
exec sp_OAMethod @obj, 'setRequestHeader', null, 'Content-Type', 'text/xml; charset=utf-8'
--exec sp_OAMethod @obj, 'setRequestHeader', null, 'Host', @host
--exec sp_OAMethod @obj, 'setRequestHeader', null, 'SOAPAction', @SoapAction
exec sp_OAMethod @obj, 'send', null, @SoapAction
Insert into @t (strxml) --strxml is the column name
exec sp_OAGetProperty @obj, 'responseXML.xml'--, @responseText out
--exec sp_OAGetProperty @obj, 'Status', @status out
exec sp_OADestroy @obj
declare @X xml
select @X = strxml from @t
--SELECT @X
select * from @t
It returns one response and it can be converted into a SQL table. I want to be able to do the same with getSiteInfo provided I know how to loop all the siteId in the getSiteInfo call at once.
Thank you in advance.