using a value from an xml file as a parameter in tsmsqlinput

[Anonymous]

I am calling a DI job from a camel route, and passing an xml file structured like this: <patient action="insert" id="30"/>

What I need to do is pull the data for the id (30) from a SQL Server database and put to Salesforce.

I am having a difficult time using the id value and setting a dynamic sql query in the tMSSQLinput.

How do I set variables and assign them from the xml file to parameters in the query of the tMSSQLinput?

[Anonymous · Accepted solution]

So I guess you are using a tRouteInput to receive the XML into the Talend Job. Is that right? Do you have the Simple Expression you are using? Is this working? Can you see the XML using  a tLogRow?

If the answer to the above is yes, then can you tell me if the XML is held as a String or as an XML Document? It shouldn't matter too much, but it is good to know.

The next thing to do is to extract your id. Do you know XPath? You can use this with either XML stored as a Document or a String. You would use a tExtractXMLField component for this (take a look here: https://help.talend.com/reader/hm5FaPiiOP31nUYHph0JwQ/Pc9RZi6IiWw1Kb~Jf0bE0Q). 

Once you have your id in a row you can use a tSetGlobalVar component to set a globalMap value. The globalMap is a hashmap that can be used (like any Java hashmap) in your database query.

One last thing to remember. Your DB query is essentially a Java String. You are using Java to build a SQL query. SO if you want the following query.....

Select
id,
name,
address1,
address2
From person
Where name = 'Richard'

....but want to make the Where clause dynamic, you need to build it in your DB component like this (assuming the name is stored in the globalMap with a key of "name")....

"Select
id,
name,
address1,
address2
From person
Where name = '" + ((String)globalMap.get("name")) + "'"

Pay attention to the single quotes inside the double quotes!!

[Anonymous]

Thank you Rhall - very helpful.

yes I am using a tRouteInput to receive the XML into the Talend Job, and the XML is there.

Your solution works great, and I also went another route which also works:

1: added a tXMLMap to the right of the tRouteInput, imported the XML structure to the input side, then created an output table and mapped the id field to the the field "patient_id" on the output side.

2: Added a tJavaRow to the output of the tXMLMap & set a context variable like so: context.patient_id = out1.patient_id.toString();

3: once that sub-routine completes, used onSubJobOK to goto tMSSQLInput and set the query:

"SELECT PatientID,Gender ...
  FROM Patients where PatientID = " + context.patient_id

Thank you for the quick response!