how to extract data from xml tags which have same tag name and add into one column.?

[McJingles]

Hi.!

The input is 

<root> <root> <id>1</id> <FN>aa</FN> <roles>bb</roles> <roles>cc</roles> <roles>dd</roles> <roles>ee</roles> </root> </root>

but I need to get add the roles in the single column like this 

id  FN  roles

1  aa   bb,cc,ee,dd

Thanks in advance.!

[Anonymous · Accepted solution]

Do you know how many roles elements you will have in your XML? If so you can get them using the following method.

Your XPath loop will be.....

"/root/root"

You can get the first "roles" value using this....

"roles[1]"

....the second using this....

"roles[2]"

....etc, etc...

You would then merge them together, maybe using a tMap

[McJingles]

Thanks, @rhall  It worked...