Debatching multi-record messages can happen at 3 levels from here, as I see it. By 'multi-record message' I mean a message in the Biztalk sense that contains data which in SQL would be a table with multiple rows in it. For example a Message might look like this inside:
<ns0:Top xmlns:ns0="http://BlogStoredProc.SmallMsg">
<Record><FirstName>Jason</FirstName><LastName>Slemons</LastName></Record>
<Record><FirstName>Zeta</FirstName><LastName>Function</LastName></Record>
<Record><FirstName>Galois</FirstName><LastName>Group</LastName></Record>
</ns0:Top>
The 'PollingStatement' level is the first, where you can 'select TOP(1) ...', grabbing only one record(in the binding tab of the receive location in Biz). The second level is where you use debatching at the schema level by selecting an envelope and then the receive port will split messages up inside for it. This is explained very well at
Lastly, it can happen inside an orchestration where you split up your record elements by assigning each one to a different variable with xpath. One thing I will say about this last method is that all the gorey detail of looping through each record is then exposed. In general I think debatching is a good idea since often your records are going to want to succeed or fail on their own and they don't depend on other records in the Poll, typically.
Still, suppose you don't want to debatch at a higher level, but instead want the whole multi-record message coming into the generated orchestration. Then suppose we only want some of the elements in in those records, for example first and last name.
1)Go into your generated Orchestration, which I have renamed 'GenOrch', create a Message named 'GenMsg', and another Message named 'SmallGenMsg'. 'GenMsg' should be based on the TypedPolling Root Node of the generated Schema. Next add a receive, send and transform shape to GenOrch as shown below:
The 'TypedPolling' node is the root node for the whole message coming from the 'PollingStatement' output. Notice that 'TypedPollingResultSet0', its child node, has a min/max occurs of 0/1 and its child node, 'TypedPollingResultSet0', has a min/max occurs of 0/unbounded. This is because it must accommodate mutiple records. With GenMsg set correctly we can now do whatever we like with it inside the orchestration.
2)Add a schema, called SmallMsg, to hold just the first and last names of the records returned from our 'PollingStatement'. Be sure it has a middle child node with a min/max occurs of 0/unbounded (see above). Associate the SmallMsg schema with the Message SmallGenMsg.
3)Add two ports, a send port and a receive port. The first should be 'we receive messages on this port' and the second one, below that, should be 'we send messages on this port' specify both later. If you like you can use the predefined port for the receive port. The top receive port and the receive shape next to it should use GenMsg and the schema associated to it. The send port and the send shape next to it should use the SmallGenMsg and its schema.
Connect up the two ports and Transform from 'GenMsg', TypedPolling, to 'SmallGenMsg', SmallMsg, in the obvious way;
At this point we simply have a shape that yields the message to a receive port, that message is then transformed into another message that is slightly simpler, and the simpler message is then spit out to a send port.
4)Now you can deploy your project(be sure to mark the top receive shape's activate property to true, sign it, and give the project an application name). When you configure the ports, make the send port just out to a file, pass through receive. The receive location should be set up the same way as the binding was in Part 1 of this post. I will assume the reader is largely familiar with how to deploy a project, if not entirely sure how to configure the receive port location in Biz with the WCF-SQL Adapter.
5)Create a new receive port, with a name of 'SQLPollReceive', and a location for it with the name 'SQLPollReceiveLoc'. The Receive pipeline should be set to XMLReceive and the Type should be WCF-SQL. Click Configure:
Here the Address(URI) should be the same as before. You can use the configure button to remind you how to generate it. Be sure to use the same InboundId as before(I use ID2). Under the Binding tab be sure to change InboundOperation Type to TypedPolling, and set the PolledDataAvailable to something like 'select COUNT(*) from Test' and PollingStatement to something like 'select * from Test'. Most of the fields here are explained in documentation like that found here:
6)Start your application. In the folder that you configured the send port to dump to you should see messages piled up something like this:
Notice there are a number of files in my send folder, that's because the Polling interval i picked was every thirty seconds and my polling statement didn't bother deleting or modifying any records so it just continues to poll the same 3 records every 30 seconds and send them out!
