Web Service implementations, especially row-oriented ones, can be “chatty”. If you have a special web service that you’d like to invoke as part of your data integration job, it could be very expensive and time consuming to call it individually for every single row that passes by. If you have 1000 rows, you aren’t only running those 1000 rows thru some remote Web Service…you are also communicating a request and response via SOAP, 1000 times “on the wire.” See if the author of the Service is able to accept and return an “array”. Then you can minimize the traffic, calling the Service only once in this example of 1000 rows, and then processing the single response after it is returned. It could still be problematic, as now your SOAP Envelope to grow to large proportions, but odds are it will be better than individual calls.
To do this in DataStage requires that you be a bit creative. You will need to construct the SOAP Body for the input request prior to invoking the Web Service, and then parse out the details of the SOAP Body that is returned.
I’ve included a .dsx in this post, along with the WSDL that was used to create it. This is a web service I published locally on my own system, so it’s not available to be called externally and tested on your systems, but there are detailed annotations in the Job that outline the necessary steps for this technique. The Web Service it calls still conforms nicely to a row orientation (single relational set of columns in, and single set of relational columns out)…the only difference is that I’m sending a whole array of such rows to the service at one time.
One thing to consider as you build one of these — walk before you run. Address the input side, and just send the output to a flat file instead of XMLInput as I’ve done here in this completed Job. Once you have reviewed the flat file and can read it successfully with XMLInput, then you can put everything together.
Enjoy.
-e
Tracing Enterprise Data Footsteps! ......celebrating the journey of data! 
May 25, 2009 at 5:05 am
Hi Erine,
Thanks a lot for such a nice article.
But I could nor able to download the dsx mentioned by you in this blog regarding array of input and output for web service.
Could you please provide us the correct link from where we can download the sample dsx.
Thanks
Santosh Rai
May 26, 2009 at 11:26 am
I’ll check, but see if you can retrieve the files and then rename them…..this site doesn’t let me post pure txt files or in this case, .dsx files, so I have to rename them first as .doc or .pdf, etc.
June 16, 2009 at 4:08 pm
To load these files down, right-click and select “Save As” – then rename the first one to a .dsx suffix, rename the second one as a .wsdl suffix.
September 11, 2009 at 7:06 am
Hi Ernie,
I have a doubt, please help me with that. I am generating an array of records to be pushed in a single row to the webservice transformer. By seeing your dsx i have been able to get the desired results. But i encountered a roadblock when I came across a secific situation while trying to covert a set of records to an xml file and then passing it to the web service.
Here, is the records which I tried to convert to xml using xml output stage.
1) “100256”,”Dsm”,”410″,”E”,”A”
2) “100257”,”Dsm”,”410″,”E”,”A”
I am also attaching the xml generated, seeing which you will understand the problem.
100256
100257
Dsm
410
E
A
Here it has clubbed together both code values in on tag as the rest of the values for the department a same except code.
Also, my xpath expression from the imported wsdl is as follows.
/ns1:UpdateDepartment/ns1:updateRequest/ns1:departmentList/ns1:department/ns1:code/text()
Similar xpath is for description, salaryPayGrade,flsaStatus etc.
How could I modify my Xpath for all the column so that the xml that is being generated is making different tags for all the code values even when they have there associated other column values are same.
100256
Dsm
410
E
A
100257
Dsm
410
E
A
If you can help please provide any suggestions.
Thanks,
Tanmaya
September 11, 2009 at 7:23 am
Hi Tanmaya…
I’ve seen this happen before, and in fact, under the exact same circumstances — when trying to construct a SOAP body for array input. I found two oddities and resolved them the same way. In one case, where the element values were identical, XMLOutput aggregates “too agressively”, and in another case, there is an interesting behavior when you have too few elements (I think 2, 3, or 4 are the issue there). In both, the solution was to create my own additional unique column, assign it a unique counter value in an upstream stage, and include it into your link (perhaps in between the 100256 code and “Dsm”) and into your xmlOutput. Call it something like , give it perfectly valid xpath, and then send the resulting xml to another transformer where you can edit out the new and un-necessary element. Tedious — but it will work. —ernie
September 11, 2009 at 7:20 am
I tried pasting the xml file contents in this space but i believe they have been truncated. But consider this to be data within xml tags and metadata definition for this xml could be found out form the xpath expression.
September 11, 2009 at 7:53 am
Thanks a lot for the response Ernie. I have tried the approach you mentioned by applying an additional column as surrogate key on top of all the input columns. In this case the xml format was perfectly generated. But as you have mentioned to edit out the new and unnecessary elements by passing the xml generated to a transformer, i have some concerns as to how to remove all the element tags 1 for the surrogate key from my single column output(longvarchar datatype) from the xml output stage.
September 12, 2009 at 7:30 am
…probably just a couple of well placed ereplace()’s will work great. Use a BASIC transformer, or a Server Job and regular transformer.
September 13, 2009 at 11:18 pm
Thanks Ernie. I was able to rectify the problem.