Extraction of column from view and append to a table

[divya_ascential]

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

[Kirtikumar]

Here is one suggestion:

In a single job, if the input is coming from Seq file then it can be done as follows:

Code: Select all

Seq file---->Trans---->XML in--->copy
                   |              |(Ref)
                   |              |
                    -------------------->Lkup--------->(Output with all data)

In Transformer generate the DummyID which will be the combination of Partition number and Rownumber. Assign this DummyRowID on both links.
Send the XML col to XML stage and others to lookup directly.
XML stage will parse the XML and give the col value to copy.

Do a lookup for link with FirmID and MktCde with the copy stage data on this DummyRowID.
The output of this stage will be cols FirmId, MktCde and cols from XML.

[divya_ascential]

Thanks kirthikumar.
currently iam trying to do it as:

Code: Select all

ODBC--------->Trans---->XML in--->copy
(input view)       |              |              (Ref)
                   |              |
                    -------------------->Lkup--------->(Output with all data)

In Transformer generate the DummyID which will be the combination of Partition number and Rownumber. Assign this DummyRowID on both links.

Could you plz let me know how to produce thr Rownumber and Partition number.

Thanks
Divya