XML output for combination of keys

[rajadommeti]

Hi I have a requirement like this. I am using Server Job and trying to generate XML's using XML output stage

Hash Files(3)
.
.
design Seqfile -->transformer --> XML output

input file has 5 columns and 3 are key columns

input data

policy_eff_dt policy_exp_dt policy_no region_id vehicle_id

1/1/2011 1/1/2025 12334 edison vin1234
1/1/2010 1/1/2012 23443 mutach TESTVIN
8/8/2011 7/1/2013 11212 trento LESSEEV
1/1/2011 1/1/2025 12323 woodbr TSTPLAT
1/1/1998 1/1/2014 18656 edison VINNUMB


lookup file (all 3 are key columns)

policy_no region_id vehicle_id



There might be only policy_no match or (policy_no and region_id) match or any combinations can match.

for policy_no match entity type 'P' entity_value policy_no
for region_id match entity type 'R' entity_value region_id
for vehicle_id match entity type 'V' entity_value vehicle_id

Based on the matches I have to generate XML's like below and send it to MQ.

Each record should generate 1 XML.

eg: if there are policy_no and Vehicle_id matched in the first record

XML for first record will be

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Data xmlns="http://teswebsite.test.net">
<Header>
<policy_eff_dt>1/1/2011</policy_eff_dt>
<policy_exp_dt>1/1/2025</policy_exp_dt>
<entity_Type>P</entity_Type>
<entity_Key>12334</entity_Key>
</Header>
<Header>
<policy_eff_dt>1/1/2011</policy_eff_dt>
<policy_exp_dt>1/1/2025</policy_exp_dt>
<entity_Type>V</entity_Type>
<entity_Key>vin1234</entity_Key>
</Header>
</Data>

if there are all matches in the second record
XML for the second match(record) will be

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Data xmlns="http://teswebsite.test.net">
<Header>
<policy_eff_dt>8/8/2011</policy_eff_dt>
<policy_exp_dt>7/1/2013</policy_exp_dt>
<entity_Type>P</entity_Type>
<entity_Key>12334</entity_Key>
</Header>
<Header>
<policy_eff_dt>8/8/2011</policy_eff_dt>
<policy_exp_dt>7/1/2013</policy_exp_dt>
<entity_Type>R</entity_Type>
<entity_Key>edison</entity_Key>
</Header>
<Header>
<policy_eff_dt>8/8/2011</policy_eff_dt>
<policy_exp_dt>7/1/2013</policy_exp_dt>
<entity_Type>V</entity_Type>
<entity_Key>vin1234</entity_Key>
</Header>
</Data>

if there is only 1 matches in the third record (vehicle_id) 3rd row xml will be


<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Data xmlns="http://teswebsite.test.net">
<Header>
<policy_eff_dt>1/1/1998</policy_eff_dt>
<policy_exp_dt>1/1/2014</policy_exp_dt>
<entity_Type>V</entity_Type>
<entity_Key>VINNUMB</entity_Key>
</Header>
</Data>



I am having difficulty because there are lot of combinations. I tried with 3 XML lookups ,same and each made 1 key
and could not produce like above scenario. Can any one please let me know how i can proceed/try?

Thanks in Advance

[chulett]

Since there's (apparently) no "RTI" component to your Server job, moved your post from that forum to here.

[rajadommeti]

Thanks Craig. Can anyone please give me some idea on this?

[eostic]

It's important that you separate the problems...first, figure out how to group the rows the way that you want based on those values. For each "combination", create a "set" of rows (using pivots, transformers, and whatever other logic you need).....get the number of rows that you want in each "grouping" for each "document" you wish to create.

Then the aggregate functions in the xmlOutput Stage will work as you desire. The initial goal needs to be a regular DataStage one. Go into flat files until you have the groupings you need based on the evalution logic that you have described.

Ernie