Debatching records from WCF-SQL using LOOP and XPath


I will continue on my sample which demonstrated records polling from SQL using WCF-SQL adapter

How I wrote in my last post it is not the best solution , it will be much slower then disassembling
data in pipeline (which I will post in 2-3 days)

So what I want to do.
First I will count the number of records retrieved from SQL
then using LOOP shape I will iterate through records and send each to a file adapter

If you did not read my two last posts , read them and create BizTalk project , install SP and create table in MSSQL , create application and ports in BizTalk

I will continue on the same orchestration

Add new variable in orchestration and set
Identifier: RecordsCount
Type: System.Int32

After Send shape insert Expression shape
Set expression :
RecordsCount = (System.Int32)(xpath("count(/*[local-name()='TypedPolling' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedPolling/ID1']/*[local-name()='TypedPollingResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedPolling/ID1']/*[local-name()='TypedPollingResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedPolling/ID1'])"));

Then insert Loop shape
Set expression for Loop:
RecordsCount > 0

Create new Message in orchestration, rename to Record
set type = System.Xml.XmlDocument

In loop insert Scope, which is required to construct new message
Set transaction to None
Add strPath new variable for scope, set type = string
Drop to scope shape new Constract Message shape
and set property of the Message Constructed to Record

to Construct Message shape add Assign message
Set expression:
strPath = System.String.Format("/*[local-name()='TypedPolling' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedPolling/ID1']/*[local-name()='TypedPollingResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedPolling/ID1']/*[local-name()='TypedPollingResultSet0' and namespace-uri()='http://schemas.microsoft.com/Sql/2008/05/TypedPolling/ID1'][{0}]", RecordsCount );
Record = xpath(Message_1.parameters, strPath);

After scope create new Send shape
Set message to Record

insert new Expression shape
Set expression:
RecordsCount = RecordsCount - 1;

Create new Send Port with Wizard and connect it with second Send shape.

At the end your orchestreation should look like

Now in build and deploy the project
In BizTalk Manager Create new Send Port

Set binding for orchestration.

Start application.

Check in output folder you should have one file with all records
and many files which will contain disassembled records.






Comments

OldBeno said…
Armin,

Been trying to get this to work all night. Can get the full message to write to xml however I don't get any split messages, i've done it a number of times now and i'm positive I have copied everything in correctly. It all runs with no errors so i'm presuming maybe the count isn't getting set or something? Tried outputting to the event log using System.Diagnostics.WriteEntry but that doesn't seem to work either.

Any help appreciated.
Cheers,
OldBeno..
OldBeno said…
Hey Armen,

Worked out my problems - no how to debug better now ;)

The RecordCount is initialized incorrectly:
RecordsCount = (System.Int32)(xpath("count(/*[local-name()='TypedPolling' and namespace.........

Should be:
RecordsCount = (System.Int32)(xpath(Message1.parameters,"count(/*[local-name()='TypedPolling' and namespace.........

Hope this helps people trying to get your example working ;)
Anonymous said…
For those who were trying to do this, you can also watch this...

http://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/

Popular posts from this blog

How to poll database using WCF-SQL adapter

SQL query timeout from application but works fast from SSMS