How to dynamically insert rest xml records in MsSql

Four 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

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

What’s New for Talend Spring ’19

Watch the recorded webinar!

Watch Now

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.

Download

Tutorial

Introduction to Talend Open Studio for Data Integration.

Watch

Downloads and Trials

Test drive Talend's enterprise products.

Downloads