Thank you everyone for your help!

it works!

yes I just removed some hard coded data to simplify it..

Can we create this format where I can show like this:
different tag names in same root tag..(with different property?)
like file...

thanks I thought so too!!
but I am not sure why the format was to be expected like this.

basically we cannot produce this format from XML RAW statement...

thanks @anthony.green
it works..

it's giving error
'LAST_VALUE' is not a recognized built-in function name.

Thanks everyone!! it works..
I appreciate your help !

@Joe Torre 
Thanks a lot Joe..

Your code works like a charm..
You are a genius..

Thanks again..

Thank you Lynn Pettis

I can't believe this came with such simple condition..i was breaking and fixing for last 2...

thanks

i was just looking for this..........

well was able to do using for each loop container...

Yea

I don't have hands on on Dot net programming but was thinking to do it using a CURSOR instead..

I am still trying:unsure:

Thanks for the reply

I am able to split the 4 type of records using header Footer primary and detail file connections

but the thing is how to put them back in...

Thanks...works gr8...

Hi..

Thank you

it was a great help and on time..

i will implement this on my complete code now...

Any help...............