biztalk 2009 WCF SQL Adapter and correlation

Question

Hello,
I am new to correlation. So I tried a a simple correlation example with file adapter (1 message type) worked great.

Now, I wanted to do correlation with WCF sql adapter. Here is my problem
1) I receive a message (message A)

Message A looks like
<SELECTION xmlns="http://test">
<ID>101</ID>
<STAUTUS>NEW</STATUS>
</SELECTION>

2) I have a store procedure pInsertMessageA inserting message A into table T [columns ID (integer), STATUS (varchar) and XMLTEXT (xml)]
ID is 101
STATUS = NEW
XMLTEXT is entire MessageA

3) using consume adapter service, I generate a binding file for (WCFsend port_sqladapterbinding) for store procedure pInsertMessageA, it creates a XSD (say pInsertMessageA.xsd)

4) I map message A to pInsertMessageA
5) I am successfully able to drop a message in table

6) external process is suppose to update the status from NEW to DONE, I am suppose to continue my orchestration once MessageA status is set to DONE.

7) I created another store procedure pGetMessageA to retrieve message from table T. This store procedure retrieve top 1 row from the table.
return ID,STATUS and XMLTEXT

8) created another binding file from consuming adapter service for store procedure pGetMessageA. This created a TypedPolling schema (TypedPoling_A.xsd)

<

 

TypedPolling xmlns=http://schemas.microsoft.com/Sql/2008/05/TypedPolling/A>

 

<TypedPollingResultSet0>

 

<TypedPollingResultSet0> <!--- 0 to unbounded-->

<ID>101</ID>

<STATUS>DONE</STATUS>

<XMLTEXT><![CDATA[ns0:<SELECTION xmlns=http://test........]]</XMLTEXT>
</TypedPollingResultSet0>

 

</TypedPollingResultSet0>

</

 

TypedPolling>

9) I need help on how to do correlation on this situation. I tried to do correlation type on (ID) but could not go further ...

Need your help on how to acheive correlation when you have 2 different messages. Please help.



Thanks
Tushar

Answer 1

I got my answer. Thanks!