sending hierarchical data through web service via soap rqst

Sandeepch19 · Post by **Sandeepch19** » Sat Dec 03, 2016 11:55 pm

Sending hierarchical data through web service via soap request in datastage.
To read a relational data from database and convert that into hierarchical data and need to send it downstream as a single or multiple hits.

For Ex: Hierarchy in the downstream is
Header
rowcount
totalcredit
totaldebit
detail
account
amount
unit

Data may look like this:

<Header>
<RowCount =2>
<Total Debit =3000>
<Total Credit =-3000>
<Detail>
<Account =1>
<Amount=1000>
<Unit=A>
<Detail>
<Account=2>
<Amount=2000>
<Unit=A>

We will have only one header record and multiple detail record per hit. how can we achieve this datastage. Can we use XML stage or XML output stage
If I am using XML stage is there any limit to the data that we can compose XML for and if it has limitation how can we overcome it. Are there any other ways to achieve this . Please let me know if in need of more details

eostic · Post by **eostic** » Sun Dec 04, 2016 3:14 pm

Hi Sandeep....... DataStage can produce the xml you want, using either Stage, but we would need a lot more detail, and let us know what you have been trying, errors you might be getting, etc.

Ernie

JRodriguez · Post by **JRodriguez** » Sun Dec 04, 2016 3:50 pm

Hello Sandeep,
You could included any number of records with both XML stages, but your real constraint will be the consumer web service. Will it be able to process a massive XML file? Will the number of records in the source database increased over time? The design of the process should be able to scale ...generating XML files of a predetermined size will be stable, dumping all records in a XML file could stress the system and it might fail as the size of the file increase. The future in DataStage XML is on the hierarchical stage but other XML alternatives are still good

If you must control the number of records or the size of your XML file one way will be inserting a wave generator stage in between the database connector and the Hierarchical stage and configured to insert a wave marker by count or by a value coming in a column to instruct or signal the hierarchical stage to generate a XML file by each wave marker instead of by the last record read from the database

Be aware of the value in the APT_DEFAULT_TRANSPORT_BLOCK_SIZE environment variable....increase it accordingly to the size of your XML files

Sandeepch19 · Post by **Sandeepch19** » Mon Dec 05, 2016 1:58 am

HI Ernie,

I am trying to use XML stage to create the XML with hierarchy and this XML will be fed to web service stage as an input.
Once we create the XML we can use XML output stage to send data to web service in a single column(lets say SOAP_OUTPUT) but I am not how much data a single column can hold.

Is there any other way where we can send header record only one once and hit the web service multiple times to send the details records.

Sandeepch19 · Post by **Sandeepch19** » Mon Dec 05, 2016 2:00 am

Hi Rodriguez,

Thank you for the response. Is there any other way where we can send header in a single request and details as a multiple request

eostic · Post by **eostic** » Mon Dec 05, 2016 6:22 am

There are a lot of moving parts here. Building your "payload" is just one of them. You will need to all the details about calling your service, and whether it is available in REST as well as SOAP.

If the service is a simple SOAP service (one row in and one row out), then you could use the WSTransformer, which is SOAP based. It sends any optional header in the same call. Each call is entirely async and not related to the next.

If you are using REST, there are ways to "re-use" a connection, but it's not the same as authenticating and then receiving a "handle", as web services often provide for......you would have to first understand what the mechanics are for your particular service (how you make independent calls with data and/or only with header), and then, perhaps in a Hierarchical Stage Assembly, have multiple steps....or perhaps call the service upstream in another instance of the Hierarchical Stage and then pass the connection string downstream for use in another invocation.

For ultimate flexibility in your calling mechanism, consider the Java Integration Stage, and write any complex calling sequences using your own java class, and then pass your data (completed XML, etc. ) into that class.

Ernie

Sandeepch19 · Post by **Sandeepch19** » Mon Dec 05, 2016 10:37 pm

eostic wrote:There are a lot of moving parts here. Building your "payload" is just one of them. You will need to all the details about calling your service, and whether it is available in REST as well as SOAP.
...

The service that we are calling is SOAP web service. Building the Payload is the challenge here since the data is not straight map and we need to build the XML because of hierarchical nature.

eostic · Post by **eostic** » Tue Dec 06, 2016 10:09 am

Build it with either the xmlOutput Stage or the Hierarchical Stage, sending the final xml out as a single "chunk" of text (single column). That either requires a single large column in the output link of the xmlOutput Stage, with nothing but a single slash "/" in the Description, or else map the "output" of your xmlComposer Step to your single large column in the output Step for your output link....

Play with it going to disk with NONE and NONE for delimiter and quote character until you have the perfect payload before trying with the WS Transformer.....

...then in the WS Transformer, notice how on the Message Tab of the input link you can check a box and pull down for "user defined messgae".

Ernie