smpa01
New Contributor
Joined 5 years ago
User Profile
User Widgets
Contributions
Urgent Help req: DataSink step to return each response with all the children
I learnt very recently how to use data-driven testing in Ready API and loop calls based on the data. My goal is to run the steps in loop and at the end create an auto-export facility with DataSink so that the results get auto exported. Now when I try go to DataSink, as I understood I need to create column headers as below to store the corresponding child values It would work well, if the soap response for each of the siteId has the same XML structure. But in my case each of the 2000+ response that I get has different number of children within <retrun> </return> For e.g. please take a look at the response 1 and response 2. Both these responses have different number of children. Response 1 <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <ns2:getSiteInfoResponse xmlns:ns2="http://billing.xyz.cc/"> <return> <address1>A</address1> <city>B</city> <closeDate>2018-10-15T00:00:00-05:00</closeDate> <contact1/> <contact2>TBD</contact2> <country>X1</country> <customerNbr>288</customerNbr> <emailAddr1/> <emailAddr2/> <fax1>0</fax1> <fax2>0</fax2> <gps>C</gps> <grouping2>Leased</grouping2> <grouping4>D</grouping4> <jobTitle1/> <jobTitle2/> <phone1>0</phone1> <phone2>0</phone2> <siteId>862578</siteId> <siteName>D</siteName> <squareFoot>0.0</squareFoot> <state>E</state> <weatherStation>D</weatherStation> <zip4>4</zip4> <zip5>F</zip5> </return> </ns2:getSiteInfoResponse> </soap:Body> </soap:Envelope> Response 2 <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"> <soap:Body> <ns2:getSiteInfoResponse xmlns:ns2="http://billing.xyz.cc/"> <return> <address1>1202</address1> <city>QA</city> <contact1/> <contact2>BL</contact2> <country>A</country> <customerNbr>288</customerNbr> <emailAddr1/> <emailAddr2/> <fax1>0</fax1> <fax2>0</fax2> <gps>LTE</gps> <grouping1>1345</grouping1> <grouping2>Leased</grouping2> <grouping3>ZX</grouping3> <grouping4>AA</grouping4> <grouping5>2000</grouping5> <jobTitle1/> <jobTitle2/> <phone1>0</phone1> <phone2>0</phone2> <services> <accountNbr>11099942</accountNbr> <liveDt>2013-07-01T00:00:00-05:00</liveDt> <service>2</service> <serviceType>gas</serviceType> <vendorAddr1/> <vendorAddr2>M</vendorAddr2> <vendorCity>N</vendorCity> <vendorName>O</vendorName> <vendorNbr>P</vendorNbr> <vendorPhone>Q</vendorPhone> <vendorState>R</vendorState> <vendorZip>S</vendorZip> </services> <services> <accountNbr>13064944</accountNbr> <liveDt>2018-05-20T00:00:00-05:00</liveDt> <service>2</service> <serviceType>gas</serviceType> <vendorAddr1/> <vendorAddr2>A</vendorAddr2> <vendorCity>B</vendorCity> <vendorName>C</vendorName> <vendorNbr>677</vendorNbr> <vendorPhone>D</vendorPhone> <vendorState>E</vendorState> <vendorZip>F</vendorZip> </services> <siteId>101567</siteId> <siteName>X</siteName> <squareFoot>4226.0</squareFoot> <state>Y</state> <weatherStation>Z</weatherStation> <zip4>0</zip4> <zip5>L</zip5> </return> </ns2:getSiteInfoResponse> </soap:Body> </soap:Envelope> Now, I need to further create a table using the whole response to be utilized in business intelligence. If I have to create matching headers in DataSink I need to go through each and every responses to ensure that I have created a corresponding property in datasink. It is not humanly possible without compromising the accuracy. Is there any way to program Ready API to store individual XML response by each looping call in a file specified by me (2000+ XML responses) or to store all the values by children of the response node without needing me to specify all the header names in the DataSink window. If it happens, i will be fine in both cases to utilize a BI tool to create a corresponding table from there. Thank you in advance.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.
