How to dynamically insert rest xml records in MsSql

[Anonymous]

Hello,

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

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

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. 

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

Thank you in advance for your help and advice.

Regards,

Rémy

[Anonymous]

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