Mapping output columns in txmlmap based on conditions from table

One Star

Mapping output columns in txmlmap based on conditions from table

I am getting an XML payload which has many tags. I need to search one of the values from a tag and compare it with a  table in mssql database . Based on the value in the table that gets matched with this tag value I need to move one node from the xml ahead for further processing.for a different value found in table I need to map another xml node
Eg consider xml payload
<amt>12</amt>
<foreignamt>78</foreignamt>
<currency>USD</currency>
I need to compare currency with a database table and if its USD select amt to be mapped to downstream system in txmlmap else select foreignamt if its any other currency. the rule is stored in database table and can be changed in future so etl needs to refer to table to decide..what sequence of talend components would be simplest to handle this.
Community Manager

Re: Mapping output columns in txmlmap based on conditions from table

Hi 
I need to compare currency with a database table and if its USD select amt to be mapped to downstream system in txmlmap else select foreignamt if its any other currency. t

I have tried to understand your request, but can't still. Can you please show us an example of your table data? and explain what are your expected result?
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: Mapping output columns in txmlmap based on conditions from table

I am getting an XML payload which has many tags. I need to search one of the values from a tag and compare it with a  table in mssql database . Based on the value in the table that gets matched with this tag value I need to move one node from the xml ahead for further processing.for a different value found in table I need to map another xml node
Eg consider xml file i have input to txmlmap is 
...
<amt>12</amt>
<foreignamt>78</foreignamt>
<currency>USD</currency>
...
Now i need to map this xml to a different output xml with structure like
..
<currency>USD</currency>
<outputamt></outputamt>
...
(I have extracted the relevant xml structure ..the actual xml is much bigger)
However before that i need to check the currency value from the input xml.
I need to extract the currency value and compare it against a database table with structure like below
create table currency_switch
(currency char(10),
 output_value char(1) )
This table will have rows like
currency output_value
USD      A
EURO     F
GBP      F
AUD      A
Thus if the currency value from input xml is  USD/AUD i need to map <amount>12</amount> to the output column 
outputamt. If the currency value is EURO/GBP..then i need to map foreignamt which is 78 in this example to output column 
outputamt..
Community Manager

Re: Mapping output columns in txmlmap based on conditions from table

Hi 
If I understand your request well, you need to do a join base on currency column on tXMLMap and generate the value of outputamt column based on the output value column from table. For example:
....main(row1)-->tXMLMap---main--tLogRow
                                |
                             lookup(row2)
                                |
                             tMysqlOutput
write the expression of outputamt column as:
row2.output_value.equals("A")?row1.amt:row1.foreignamt
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business