cancel
Showing results for 
Search instead for 
Did you mean: 

Consuming SOAP web service dynamically in loop

New Contributor

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.

1 REPLY 1
Occasional Contributor

Re: Consuming SOAP web service dynamically in loop

Hi,
you could achieve this with a bit of groovy scripting like this.

Create a new project, add a test suite and then add 3 new test cases (script, getSiteList and getSiteInfo).

Right-click on getSiteList and getSiteInfo and deactivate the test case.

Add a Soap-Request to getSiteInfo and getSiteList, set the appropriate soap-endpoint for each.

Add a groovy script to the test case "script".

It should look like this now:

 

screenshot.png

 

Add the following code to the groovy script:

def getSiteInfo = context.testCase.testSuite.getTestCaseByName('getSiteInfo').getTestStepByName("request")
def getSiteList = context.testCase.testSuite.getTestCaseByName('getSiteList').getTestStepByName("request")

//add code here to change userName, password, custNbr if necessary
def userName = 'username'
def password = 'pwsd'
def custNbr = '288'

//set userName, password, custNbr 
getSiteList.setPropertyValue('request',
"""<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>""" + password + """</arg1>
			<!--custNbr-->
			<arg2>""" + custNbr + """</arg2>
			<!--lastModifiedDt-->
			<!--<arg3>?</arg3>-->
		</bil:getSiteList>
	</soapenv:Body>
</soapenv:Envelope>""")

// fire request
getSiteList.run(null, context)

// get response
def root = new XmlSlurper().parseText(getSiteList.getPropertyValue('response'))

// iterate over all siteIds and fire request based on the siteId
root.'**'.findAll() {node -> node.name() == 'siteId'}.each { id ->
	getSiteInfo.setPropertyValue('request',
"""<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>""" + password + """</arg1>
			<!--custNbr-->
			<arg2>""" + custNbr + """</arg2>
			<!--siteId-->
			<arg3>""" + id + """</arg3>
		</bil:getSiteInfo>
	</soapenv:Body>
</soapenv:Envelope>""")

	//fire request for getSiteInfo
	getSiteInfo.run(null, context)

	//show result
	log.info getSiteInfo.getPropertyValue('response')
	
	// add more code here
	// ...
}

 

If you either run the project, test suite or groovy script it should first request the site list and then iterate of every siteId and request further information for each.

 

New Here?
Join us and watch the welcome video:
Announcements
Top Kudoed Authors