Execute SELECT statement and get rows of data from SQL Table using BizTalk

Question

My requirement is to get the input XML file from source and based on one particular input field we need to retrieve multiple rows from database and create those values as segments and append into input XML file and sent to destination folder.

I tried to retrieve the multiple rows from SQL using WCF-SQL adapter but want to know how to append the retrieved values in input xml again

Answer 1

1. Create a send port using consume adapter service.
2. Use the input xml as a parameter to your select statement which passes into this send port.
3. Use an orchestration which initiates from your input xml and correlates to the response from the send port.
4. Use a dual input map which inputs
   • the input xml
   • the response message which maps to your final message.
5. Send to your final destination.