XML repeating elements

[boxtoby]

I need to create some xml output which contains an embedded one to many relationship and I can't see how to do it.

Here's the simplified source data

Pr_Key,Name,Friend_Number,Friend_Name
1000,Bob,1,Peter
1000,Bob,2,David
1000,Bob,3,Fred
2000,Chris,1,Mary
2000,Chris,2,Sarah
2000,Chris,3,Erica
3000,Roger,1,Sam
3000,Roger,2,Rupert


Here's the xml meta data
/Friends/Owner/Pr_Key/text()
/Friends/Owner/Name/text()
/Friends/Owner/Contacts/Friend_Number/text()
/Friends/Owner/Contacts/Friend_Name/text()


What I want is create xml output like this:

<Friends>
<Owner>
<Pr_Key>1000</Pr_Key>
<Name>Bob</Name>
<Contacts>
<Friend_Number>1</Friend_Number>
<Friend_Name>Peter</Friend_Name>
</Contacts>
<Contacts>
<Friend_Number>2</Friend_Number>
<Friend_Name>David</Friend_Name>
<Contacts>
<Friend_Number>3</Friend_Number>
<Friend_Name>Fred</Friend_Name>
</Contacts>
</Owner>
.
.
.
.
</Friends>

But what I am getting is the owner being repeated:

<Friends>
<Owner>
<Pr_Key>1000</Pr_Key>
<Name>Bob</Name>
<Contacts>
<Friend_Number>1</Friend_Number>
<Friend_Name>Peter</Friend_Name>
</Contacts>
</Owner>
<Owner>
<Pr_Key>1000</Pr_Key>
<Name>Bob</Name>
<Contacts>
<Friend_Number>2</Friend_Number>
<Friend_Name>David</Friend_Name>
</Contacts>
</Owner>
<Owner>
<Pr_Key>1000</Pr_Key>
<Name>Bob</Name>
<Contacts>
<Friend_Number>3</Friend_Number>
<Friend_Name>Fred</Friend_Name>
</Contacts>
</Owner>
.
.
.
.
</Friends>


Any help grately appreciated!
Bob.

[eostic]

Be sure you have aggregation as one of the checked options in the XMLOutput Stage, and try making one of the lowest elements (such as FriendName your repetition element (key).

Ernie

[boxtoby]

Hi Ernie,

Thanks for the response, it worked!!

Bob.