DSXchange

We have a datastage job which uses xml output stage. We are facing an issue while generating the output.

input data

Cust_id Cont_usage Cont_usg_text Cont_no
0001 2 Telephone 723652845894
0001 2 Telephone 893282983484
0001 3 Mobile 089148948329
0002 2 Telephone 231341414144
0002 4 Fax 231341414144

output from XML stage

<Service>
<cust_id>0001</cust_id>

<contactmethodtype>
<cont_usage>2</cont_usage>
<contactmethod>
<cont_no>723652845894</cont_no>
</contactmethod>
</contactmethodtype>

<contactmethodtype>
<cont_usage>2</cont_usage>
<contactmethod>
<cont_no>893282983484</cont_no>
</contactmethod>
</contactmethodtype>

<contactmethodtype>
<cont_usage>3</cont_usage>
<contactmethod>
<cont_no>089148948329</cont_no>
</contactmethod>
</contactmethodtype>

</Service>

<Service>
<cust_id>0002</cust_id>

<contactmethodtype>
<cont_usage>2</cont_usage>
<cont_usage>4</cont_usage>
<contactmethod>
<cont_no>231341414144</cont_no>
</contactmethod>
</contactmethodtype>
</Service>

when the cont_no is same (231341414144) with a different cont_usage (2, 4) for cust_id - 0002 we are getting only one row it should have 2 output rows for cont_no similar to output of cust_id - 0001.

I have specified cont_no as "Key" and specified output as "Single row" in XML output stage and are using 2 xml stage to get the output

Any suggestions

Magesh S

maheshsada wrote:I have specified cont_no as "Key" and specified output as "Single row" in XML output stage and are using 2 xml stage to get the output

I don't know your business rules but looks like you have to Specify cont_no and Cont_usage as Keys. if you specify only cont_no as key, it will reject duplicate records with same cont_no.

specifying cont_no and cont_usage as Keys does not solve the issue. We are getting issue in the cust_id 0001 and cust_id 0002 is output correctly

Magesh S

specifying cont_no and cont_usage as Keys does not solve the issue. We are getting issue in the cust_id 0001 and cust_id 0002 is output correctly

Magesh S

It looks like it may be something to do with the aggregation.... put <cont_usage> inside the <cont_method> element (meaning, change the xpath in the Description property for the colum on your input link...make <cont_no> and <cont_usage> look the same, both following the /cont_method/ element).

What happens?

Ernie

It works fine when i place the cont_usage inside contactmethod. But the target system will not accept this xml as the schema defnition validation will fail.
Hence the format should be in the way as i have stated.

Magesh S

alas, XMLOutput aggregates under these circumstances.....

Here's what I would do:

a) manually build the cont_method node in a prior transformer, and then bring that completed "chunk" into the final xml stage (this is done by using the "XML" data element). If you only have a couple of columns, this is fairly easy.

b) put the cont_usage or other element in the cont_method element (or other dummy unique column), and then edit it out of the xml downstream. This is done by sending the xml content down an output link as a single colum (one large column called something like myXMLcontent with a longvarchar and long length and a single '/' in the Description property).

Ernie

Thanks for providing the solution. I have tried the approach you have specified and its working fine. I have used two xml stages one for contmethod and another contmethodusage and used join stage to join the two chunks

Magesh S