Processing results from SQL Server table in a batch

I have a scenario in which sql table results have to be read.

I tried a sample scenario in that i provided a simple “Select * from table for xml auto, xmldata” stored procedure and built the sql schema. The send end is a file but in the business process i have to send the data to SAP through BAPIs.

After i tested the scenario, i found that only the first row entry is processed in Biztalk and again and again, it was the same row. It was only after I deleted the frst row data that the second row was processed.

I even tried using the loop shape in the orchestration, but to no avail.

How can the batch processing be achieved? Can it be done through an orchestration or is it that something has to be done with the stored procedure through which the SQL schema is built? Also, i found that unlike files – which are moved to the destination in a Cut – Paste fashion, table entries aren’t. So, can something be done by which the data entries once processed can be removed from SQL table?

I’m a newbie, so kindly bear my naive posts.