MAPPING MULTIPLE ELEMENTS TO A SINGLE RECORD

[suresh]

Hi ,

I am using a datastage job, which receives input from an XML which is structured in the following way.

<Address id>
<contact-type>
<contact>
<details></details>
</contact>
</contact-type>
</Address>

Here contact and contact types are repeating elements.

I would like to load the values in the fields of output file in the following way

Contact1-details | Contact2-details | Contact3-details

I decide to load the corresponding contact details based on the value assigned to contact-type in the xml input.

I am able to load these values in the output in three different records at the corresponding fields, But i would like to store all the contact details in a single record.

If i try to use the Address-id to try load these values in a single record, the contact1 details only gets loaded.

Can anyone suggest me how to go about doing this.

Thanks and regards,
Suresh



Edited by - suresh on 12/07/2002 00:09:18

[ray.wurlod]

Replace any NULL contact ID with "".
Run your stream through an Aggregator stage, grouping on address ID and taking the MAX of each contact details field.
This approach is appropriate for a known maximum number of contact details fields.

For a large and variable number of contact details fields there is another approach using a temporary UV table and its capability for dealing with multivalued data, but I don't think - from your problem description - that this is your situation.

[suresh]

Hi Ray,

Thanks a lot for your suggestions.
I tried out the first option and it worked fine.
I would like to know more about the second option you have given.

Thanks in advance,
Suresh