DSXchange

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.

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

Hi Ernie,

Thanks for the response, it worked!!

Bob.