delete & update data

[hiba1]

i try to insert data from xml file to sql server table .

i produce this job

.it's work ...then i do same testes on this job like update and delete ...

what i want now to insert modified data from the xml into the sql table ...

this is my xml file :

<?xml version="1.0" encoding="UTF-8"?>
<envelope>
<header>
<version port="4002" host="ns001tow.netstorming.net">1.6.1</version>
<timestamp>20190814143828</timestamp>
</header>
<response type="countries" product="hotel">
<countries>
<country>
<code value="MW"/>
<names>
<name language="ru" value="МАЛЬТА"/>
<name language="fr" value="MALTE"/>
<name language="en" value="MALTA"/>
<name language="it" value="MALTA"/>
<name language="pt-br" value="MALTA"/>
<name language="es" value="MALTA"/>
</names>
</country>

<code value="GE"/>
<names>
<name language="ru" value="ГРУЗИЯ"/>
<name language="fr" value="GEORGIA"/>
<name language="en" value="GEORGIA"/>
<name language="it" value="GEORGIA"/>
<name language="pt-br" value="GEORGIA"/>
<name language="es" value="GEORGIA"/>
</names>
</country>
<country>
<code value="D"/>
<names>
<name language="ru" value="ГЕРМАНИЯ"/>
<name language="fr" value="ALLEMAGNE"/>
<name language="en" value="GERMANY"/>
<name language="it" value="GERMANIA"/>
<name language="pt-br" value="GERMANIA"/>
<name language="es" value="ALEMANIA"/>
</names>
</country>
<country>
<code value="CR"/>
<names>
<name language="ru" value="ХОРВАТИЯ"/>
<name language="fr" value="CROATIE"/>
<name language="en" value="CROATIA"/>
<name language="it" value="CROAZIA"/>
<name language="pt-br" value="CROAZIA"/>
<name language="es" value="CROACIA"/>
</names>
</country>
<country>
<code value="I"/>
<names>
<name language="ru" value="ИТАЛИЯ"/>
<name language="fr" value="ITALIE"/>
<name language="en" value="ITALY"/>
<name language="it" value="ITALIA"/>
<name language="pt-br" value="ITALIA"/>
<name language="es" value="ITALIA"/>
</names>
</country>
<country>
<code value="ES"/>
<names>
<name language="ru" value="ЭСТОНИЯ"/>
<name language="fr" value="ESTONIE"/>
<name language="en" value="ESTONIA"/>
<name language="it" value="ESTONIA"/>
<name language="pt-br" value="ESTONIA"/>
<name language="es" value="ESTONIA"/>
</names>
</country>
<country>
<code value="US"/>
<names>
<name language="ru" value="США"/>
<name language="fr" value="ETATS-UNIS"/>
<name language="en" value="UNITED STATES"/>
<name language="hr" value="UNITED STATES"/>
<name language="ca" value="ESTATS UNITS D\'AMÈRICA"/>
<name language="de" value="VEREINIGTE STAATEN"/>
<name language="it" value="STATI UNITI"/>
<name language="pt-br" value="STATI UNITI"/>
<name language="sl" value="UNITED STATES"/>
<name language="es" value="ESTADOS UNIDOS DE AMÉRICA"/>
</names>
</country>
<country>
<code value="C"/>
<names>
<name language="ru" value="ЧЕХИЯ"/>
<name language="fr" value="REPUBLIQUE TCHEQUE"/>
<name language="en" value="CZECH REPUBLIC"/>
<name language="it" value="REPUBBLICA CECA"/>
<name language="pt-br" value="REPUBBLICA CECA"/>
<name language="es" value="REPÚBLICA CHECA"/>
</names>
</country>

<country>
<code value="R"/>
<names>
<name language="ru" value="РУМЫНИЯ"/>
<name language="fr" value="ROUMANIE"/>
<name language="en" value="ROMANIA"/>
<name language="hr" value="ROMANIA"/>
<name language="ca" value="ROMANIA"/>
<name language="de" value="ROMAENIEN"/>
<name language="it" value="ROMANIA"/>
<name language="pt-br" value="ROMANIA"/>
<name language="sl" value="ROMANIA"/>
<name language="es" value="RUMANIA"/>
</names>
</country>
</countries>
</response>
</envelope>

If i delete the country with the code='MW' from the xml file .....and set the action to "update" in the tDB_output  ..i get the same data in my table ...so haw can i do this  !!! 

any help please !

[JR1]

It seems to me as if you will have to dive a bit deeper into how databases work. If you want to update data, you need a key defined in your DB table by which the Talend job (better: the resulting UPDATE SQL) can identify the record you want to update. Deleting a record from your input file does not automatically delete the record in the table. The record simply stays untouched because it is not in the input. Tables do not simply get cleared before you execute any INSERT or UPDATE action.

[hiba1]

Hi JR,

Thanks for the response. 

I understand that the table  must have a primery key to update data ... 

But i am still unable to understand how do I do the job to get the correct result (delete data from the xml file and update in the sql table ).

[JR1]

I do not know if I understand you correctly but if you want to delete records in your table, you will have to determine first which records should be deleted and then send delete statements for those records to your DB (Action on data = "Delete" in the tDBOutput component). If you need more information, I am afraid you will have to give us more details about what exactly you want to achieve.

[hiba1]

how can i deleting rows before inserting into SQL Server . 

i use tMSSqlRow  and i want to delete  for example the country with the  countryCode='MW' from the table countries.

[JR1]

You create a component tDBRow, set the DB type to "MS SQL" and then put the statement "DELETE FROM YOUR_TABLE_NAME_HERE WHERE COUNTRYCODE = 'MW'". This can be connected to your other job parts via Triggers (OnSubJobOK). 

Does this help?

[hiba1]

this my job .

i can't connected  tDBRow  with the job ....

[JR1]

Have you tried connecting "parse_xmlcountries" via an OnSubjobOK trigger to tDBRow_1? Triggers always link the starting components of subjobs.