Extraction of column from view and append to a table
Posted: Thu Aug 31, 2006 11:46 pm
Hi all
I have a job in which the input consists of 6 columns,out of which one column is in XML format. The input is a view.
Example data looks like this:
FirmId Mktcode XMLcol .....etc
0001 AB <start> <data1>ab<data1><data2>34</data2>.. ....</end>
0002 CD <start> <data1>xy<data1><data2>53</data2>.......<end>
....so on
Currently i have a job which extract the XML column into a sequential file
and then another job to parse the XML from the file above and load it into tables op1,op2,op3.
Suppose op1 table has data from <data1>
Now the problem is I need to append the FirmId from the view
to the columns in op1,op2,op3(extracted from XML and loaded in 2nd job)
to the corresponding data i.e.,
the data1=ab row of op1 table should get appended with FirmId 0001
and 'xy' row with FirmId 0002 so on.
Plz suggest which stage to use and how to proceed.....
Thanks
Divya
I have a job in which the input consists of 6 columns,out of which one column is in XML format. The input is a view.
Example data looks like this:
FirmId Mktcode XMLcol .....etc
0001 AB <start> <data1>ab<data1><data2>34</data2>.. ....</end>
0002 CD <start> <data1>xy<data1><data2>53</data2>.......<end>
....so on
Currently i have a job which extract the XML column into a sequential file
and then another job to parse the XML from the file above and load it into tables op1,op2,op3.
Suppose op1 table has data from <data1>
Now the problem is I need to append the FirmId from the view
to the columns in op1,op2,op3(extracted from XML and loaded in 2nd job)
to the corresponding data i.e.,
the data1=ab row of op1 table should get appended with FirmId 0001
and 'xy' row with FirmId 0002 so on.
Plz suggest which stage to use and how to proceed.....
Thanks
Divya