Forum Discussion

smpa01's avatar
smpa01
New Contributor
4 years ago

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

  • appliedMagic's avatar
    appliedMagic
    Occasional Contributor

    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:

     

     

    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.