UPDATE: I did make some progress.  I added a script inside the PO1Loop1

<arc:set attr="db.conn" value="server=connstring" />
<arc:set attr="db.driver" value="System.Data.CData.Sql" />
<arc:set attr="db.sp" value="MyStoredProc" />
<arc:set attr="db.paramname#" value="UNIQUEID" />
<arc:set attr="db.paramvalue#" value=""xpath(UNIQUEID)]" />
<arc:call op="dbCall" item="db">
	<arc:set attr="_map.ItemDescription">;db.db:ItemDescription | def]</arc:set>
</arc:call>

However, it returns the result for the first procedure call for all PID05 elements.  I suppose this code needs expanded to loop over each UNIQUEID?

Any ideas would be appreciated.

Hi Eric, 

The best designer-driven way to approach this is to leverage a feature that was added in the most recent quarterly update to CData Arc 2023 at https://arc.cdata.com/support/builds/ which now includes a new Lookup Stored Procedure action in the SQL connector: 

https://cdn.cdata.com/help/AZJ/mft/SQL-Server-LSP.html

This was recently added, but it’s a nice designer driven way to enrich an existing XML document with values that would be returned from the lookup to a stored procedure. 

With that said, the way that you are doing this now will also work just as well - you can use code script to execute the stored procedure call as you are looping over each element of the Detail table.

As long as you are entering the code block for the dbCall operation within the Foreach Detail so that: 

1. You are issuing that call n times for each detail and
2. xpath(UNIQUEID)] is giving you a unique value for each detail

That should work just fine. 

I see that you are storing the return in the _map item, but if you’re doing no further manipulation with that variable, you could also use a custom script in PO105 and set the value in result.text, like this:

<arc:set attr="db.conn" value="server=connstring" />
<arc:set attr="db.driver" value="System.Data.CData.Sql" />
<arc:set attr="db.sp" value="MyStoredProc" />
<arc:set attr="db.paramname#" value="UNIQUEID" />
<arc:set attr="db.paramvalue#" value="axpath(UNIQUEID)]" />
<arc:call op="dbCall" item="db">
	<arc:set attr="result.text">rdb.db:ItemDescription | def]</arc:set>
</arc:call>