tMysqlRow is not intended to return data but execute a SQL action for each incoming row; the outgoing row will be identical to the incoming row.

A better approach is tFileInputXML --> tXMLMap --main--> tMap -->
                                                 tmySqlInput --lookup-->

In the tMap, change the lookup model to "Reload at each row (cached)" and use the globalMap key in the tmySqlInput SQL.  Read scenario 6 of the tMap help for a detailed example of how this works.

You can do it using contexts. In the SQL refer to the context variable and set a value in the context variable using tJavaRow.

Maybe alevy is right re: tMysqlRow is not intended to return data

Anyway, I intended to use a tMySQLInput, just not thinking :-).

Here's something that might do the trick. Think this is the same as janhess is suggesting.

I'm getting the IDs from the XML file, denormalizing into a comma separated string, then setting a globarVar to this value.

Then, set the SQL query in the tMySQLInput as something like:

"select id, order_date from orders where id in ("+((String)globalMap.get("K1"))+")"

Where K1 is the globalVar.

This seems to work OK, although you might not want to use this if you had very large numbers of IDs returned from the XML file. There's probably better ways to this if that's the case.

alevy wrote:

tMysqlRow is not intended to return data but execute a SQL action for each incoming row; the outgoing row will be identical to the incoming row.

That is just plain wrong, there is even an option to propagate the query's record set in the t***Row components.

True but the option to propagate the query's record set is intended more to return the results of stored procedures; it requires an existing Object column in the incoming schema to t***Row that will be populated with the record set and then the actual return value desired needs to be extracted using tParseRecordSet.

I stand by my previous suggestion that tMap lookup each row is the easiest way to go.