Web Service (ISD Output Stage) Returning only one row

kenstorm · Post by **kenstorm** » Mon Feb 20, 2012 11:21 am

Problem: After receiving the SOAP Response, I am only getting one row of XML formated data out, I should have more. For some reason, ISD web services seems to give me two options. One, output all my XML data within one row, or Two, into an array. If I output all my XML columns out, I get one row perfectly formatted the way I want it. If I change the job to output only one column with a the XML set to column_1 /, then I get all the rows in one huge line. The calling software can't parse the one line of data. I need to have multiple rows of neatly formatted XML. How do I do it?

Note to Admins: I have already searched the current posts. Nothing fits my problem exactly. I am NOT using the any of the Web Service Client stages.

Job Design Method: I have initially coded my job using input and output files to perfect the XML output format needed. Then i replaced these file stages with ISD Input and ISD output stages so as to prepare for web service deployment.

Job Design: ISD Input >> XML Input >> Transformer >> Stored Proc >> XML Output >> ISD Output

Desired Output:
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="LookupResponse">
<xs:complexType mixed="true">
<xs:sequence>
<xs:element maxOccurs="unbounded" minOccurs="0" ref="DomesticLookup" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="DomesticLookup">
<xs:complexType mixed="true">
<xs:sequence>
<xs:element name="Human_Id" type="xs:string" />
<xs:element name="HH_Number" type="xs:string" />
<xs:element name="CN_First_Name" type="xs:string" />
<xs:element name="CN_Last_Name" type="xs:string" />
<xs:element name="LM_Address_1" type="xs:string" />
<xs:element name="LM_City" type="xs:string" />
<xs:element name="LM_State" type="xs:string" />
<xs:element name="LM_PostalCode" type="xs:string" />
<xs:element name="CNP_Number" type="xs:string" />
<xs:element name="CM_ID" type="xs:string" />
<xs:element name="LM_ID" type="xs:string" />
<xs:element name="CM_Abilitec_ID" type="xs:string" />
<xs:element name="LM_Abilitec_ID" type="xs:string" />
<xs:element name="Owner" type="xs:string" />
<xs:element name="Curr_Status" type="xs:string" />
<xs:element name="Lockout" type="xs:string" />
<xs:element name="Reservation" type="xs:string" />
<xs:element name="Good_To_Transfer_ORL" type="xs:string" />
<xs:element name="Good_To_Transfer_LV" type="xs:string" />
<xs:element name="Good_To_Transfer_NY" type="xs:string" />
<xs:element name="Good_To_Transfer_HAW" type="xs:string" />
<xs:element name="Good_To_Transfer_MRTLB" type="xs:string" />
<xs:element name="Good_To_Transfer_MAR" type="xs:string" />
<xs:element name="Good_To_Transfer_PCU" type="xs:string" />
<xs:element name="Good_To_Transfer_WAIK" type="xs:string" />
<xs:element name="Lead_Id" type="xs:string" />
<xs:element name="Income360" type="xs:string" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>

Thanks for your help! I appreciate any input that I can get.

eostic · Post by **eostic** » Mon Feb 20, 2012 11:37 am

...the key may be in your question....."output all my XML data"...

Are you running a normal output link (from some upstream transformer or other Stage type) directly into your ISDOutput Stage? ....or are you running it first into an xmlOutput Stage and then into ISDOutput?

If the former, then you will get an array that contains "n" rows of data in your Web Service response (which happens to be xml in SOAP, but that's a different issue)....

If the latter, then the answer should be in the logic of your xmlOutput Stage....if you are aggregating rows, then you will only be getting "one row" sent to the ISDOutput Stage....

The best way to check/design/debug these jobs is by using Sequential File Stages in place of the ISD stages..... send in "one" row with your "input"....and then see what you get on the output. If it's one row in a Sequential file, then you don't need array, because you only have one row. If you have many rows in the sequential target then you will need "array"...

...and if you have array, and you indeed have multiple rows, and it still looks like one big giant row is being retrieved, then it's more likely that the SOAP client isn't reading the array correctly....make sure you get a tool like SOAPui or Actional Diagnostics and test the service yourself...to determine exactly what you are getting back, and not rely on the developer of the web service client when doing this level of unit testing.

Ernie

kenstorm · Post by **kenstorm** » Mon Feb 20, 2012 1:11 pm

Erinie,

Thanks for the reply. I was hoping to hear from you.

Here is the job design in a nutshell: ISD Input >> XML Input >> Transformer >> Stored Proc >> XML Output >> ISD Output

The consumer of the data is using Cast Iron, an IBM product to parse the SOAP Response. There skill level is not at the advanced level so they might not know how to parse the array. I will pursue that with them.

I selected the box for the formatted data section in the Output tab of the XML Output stage. This has given me a closer output that I want but it includes the beginning and ending parent tags for each column or XML element. How do I only show the parent tags (<DomesticLookup>) at the top and bottom of the whole message instead of around each element?

Currently getting this:
<DomesticLookup>
<Human_Id>
147756207
</Human_Id>
</DomesticLookup>
<DomesticLookup>
<HH_Number>
416473695
</HH_Number>
</DomesticLookup>
<DomesticLookup>
<CN_First_Name>
DONMARK
</CN_First_Name>
</DomesticLookup>

I want this:
<DomesticLookup>
<Human_Id>
147756207
</Human_Id>
<HH_Number>
416473695
</HH_Number>
<CN_First_Name>
DONMARK
</CN_First_Name>
</DomesticLookup>

eostic · Post by **eostic** » Mon Feb 20, 2012 1:55 pm

ok.. a couple of things....

a) if you are using "aggregate rows" option in the xmlOutput Stage, then you will (you should) always get only one row. There will never be an array. So no need to worry about that. They should be prepared to receive only one big column (I would expect you to have just one column on the output link from xmlOutput, unless you are passing thru a bunch of other cols). ...that big column will be the WHOLE xml document. That is by design of your Job.

b) you should always use "unformatted" output. Uncheck that box. It wastes space on the wire.

c) the issue you have below looks to be one of which column you have selected as the "key".....change the repetition element and it should change the level of aggregation.... ...hard to tell at this point, as there could be other things going on, but make something like Human_Id the "key".

Ernie

kenstorm · Post by **kenstorm** » Tue Feb 21, 2012 10:57 am

I am now getting the needed rows outputted.

My end user is now complaining that they can't parse the output from the Web Service because there are &lt instead of < symbols. They are using a product called Cast Iron. Is it possible to change the output in ISD to output <'s instead of &lt's?

I have coded a separate test job to output the same job logic into a file and the output looks fine, meaning no &lt's.

chulett · Post by **chulett** » Tue Feb 21, 2012 11:49 am

See if this helps.

eostic · Post by **eostic** » Tue Feb 21, 2012 3:42 pm

It won't. The xml stage isn't doing the escaping. ISD is. It's proper.....in order for xml to go inside of xml (which is what is happening on the wire), the content HAS to be escaped.

The tool at the other end, if it does normal de-serialization of the incoming response SOAP envelop, will bring those back...

PLEASE, as noted above, be sure to test this with a well accepted SOAP client such as Actional or SOAPui. You will see your xml content, as it should be.

I can only guess that the particular cast iron program is doing something under the covers with the SOAP envelope that is returned, perhaps not passing it thru the usual xml parsing libraries.

Ernie

chulett · Post by **chulett** » Tue Feb 21, 2012 7:01 pm

eostic wrote:It won't.

kenstorm · Post by **kenstorm** » Wed Feb 22, 2012 9:03 am

I found a work around. Since the consuming party and software are demanding that the &lt's be removed, I recoded the job. Instead of using the XML Output Stage, I replaced it with a Transformer and added the XML definitions in the Description field. By so doing, I am getting the desired XML format outputted from the Web Service with '<' characters and individual rows of records. ISD doesn't think it is XML so it doesn't try to 'Help' me by encoding the '<' symbols to &lt's. Everybody is happy. When the client decides to upgrade their XML parsing software which would initiate an epiphany, I will also get paid to correct it. Everybody is happy.

Ernie - Yes, I am painfully aware that ISD is adding the &lt's. Thanks for the explanation, it is helping retain some of my sanity. The Cast Iron product most likely has the ability to parse the &lt's but the operator doesn't know about it. I am just a consultant, they are the client, client sets the requirements. Insert frowny face here...

eostic · Post by **eostic** » Wed Feb 22, 2012 10:19 am

No problem...congrats. Glad you found a solution.

Ernie