Two Stars

How to dynamically insert rest xml records in MsSql

Hello,

 

I'm a beginner in Talend and a non native english speaker, so thank you for forgiving me for my mistakes Man Embarassed

I explain the context: I have already created a job, which allows me to dynamically generate all the different tables with their columns from a WebService (Microsoft CRM) but now I would like to register each entity received in the existing table.


I have a rest xml webservice, who sent me this result (I simplified everything for a better understanding) :

Heberger image

The "logicalname" is the MSSQL table.

The "key" attribute is the MSSQL column and the "value" attribute the corresponding value.

 

How can I do that?

Indeed, at the moment I tried to generate the request Sql via "tXMLMap" and "tNormalize" but without success. I now think of trying to fill a csv file, and use this one to import it into database. Is this a good way to do it?

 

If it can help you, here is all my job:


The first part here works, it allows me to create all the tables and their specific columns thank to another webservice call. 

Heberger image

 

 

It's this loop that causes me problems, for each table created, I try to recover the associated data (GetAllEntityDate send me the XML above & it missing MsSQL insert part):

Heberger image

 

 

Thank you in advance for your help and advice.

 

Regards,

Rémy

1 REPLY
Community Manager

Re: How to dynamically insert rest xml records in MsSql

Hi Remy
If I understand the request well, you need to use a tExtractXMLField to extract the records from response if the webservice returns a String response or tXMLMap to extract the records from reponse if the webservice returns a Document, and then iterate each records and use a tMssqlRow to execute an insert statement for inserting each record one by one.

Regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business