DSXchange

I believe you have 'REP_TYPE' as the repitation (Key) in your XMLoutput stage. You should change it to 'S_NO' from what I see in your sample data.

I am answering my own question.
I added these columns as primary keys in XML output stage_TYPE, PROCESS_OFFICE, PACKAGE _LEVEL.

Did you add all of these columns as key? I dont think its possible. You can have only one column

Yes I did added Key for all columns except one column in the XML output Stage. my table had 29 columns and I added 28 columns as Key.
Is this the correct way of designing the job ??? but I got correct results as excepted.

I'm surprised that even ran as only one 'repetition element' is allowed and I could have sworn it errors out when multiple are detected. What exact version of DataStage are you running?

There are no errors/warnings for the job and the version of Datastage I am using is 8.5

Beats me....it definitely throws an error when you don't supply any repetition element, but I haven't tried providing many in a very long time...I suspect it finds the first one and is happy. Only one is needed....provided that it's the correct one... ...quite clearly, one of the ones that was selected is the "right" one.... Congrats on getting it working.

Ernie

Thank You Ernie, I am working on a project which requires generating XML output for more than 50 tables. after generating more than 20 tables till now what I understood is that we can add multiple repetition elements.

While you can specify multiple, please understand that only one is actually needed. And I found an example of the error I recalled, one such example is noted here: "Error: The link contains more than one repetition rule". Looks like either only the XML Input stage has the error reporting logic built in or perhaps it is broken in the 8.5 XML Output stage. [shrug]