delete & update data

Five Stars

delete & update data

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

i produce this job

p.PNG

 

.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 !

Nine Stars JR
Nine Stars

Re: delete & update data

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.

Five Stars

Re: delete & update data

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 ).

 

Nine Stars JR
Nine Stars

Re: delete & update data

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.

Five Stars

Re: delete & update data

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.

Nine Stars JR
Nine Stars

Re: delete & update data

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?

Five Stars

Re: delete & update data

this my job .

 

Capturedelete.PNG

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

Nine Stars JR
Nine Stars

Re: delete & update data

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

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads