none
Debatching in a polling statement RRS feed

  • Question

  • Hi,

    I am getting data (parent id) from oracle table using a polling statement. I need to implement debatching in the polling statement so that each record (parent id) is in a seperate file. How can this be acheived??


    Regards, Vivin.

    Thursday, July 17, 2014 4:19 AM

Answers

  • Hi Vivin,

    I assume that you are polling data and want to group and separate the data based on parent id, you can go through following article to see how it can be done :

     http://social.technet.microsoft.com/wiki/contents/articles/18845.biztalk-server-2010-grouping-and-debatchingsplitting-inbound-messages-typedpolled-from-wcf-sql-adapter.aspx


    Maheshkumar S Tiwari|User Page|Blog|BizTalk Server : How Map Works on Port Level

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 12:14 PM
    Thursday, July 17, 2014 6:03 AM
  • Hi Vivin,

    What you need to do is to have a typed polling in your Oracle DB which will generate a typed schema result set .

    Below is the steps for this .

    1) click the topmost Schema node(Auto generated one), and changed its Envelope property to Yes.

    2)click the TypedPolling node (which acts as the primary root that comes out of the adapter) and set the Body XPath value to the node ABOVE the eventual leaf node.

    3)select the leaf node and set its Max Occurrence from Unbounded to 1 .

     ecord gets debatched automatically by BizTalk in the XML receive pipeline, the resulting TypedPollingResultSet0 message(which matches a message type known by BizTalk) gets put in the MessageBox and routed around.

    Thanks

    Abhishek


    Thursday, July 17, 2014 7:06 AM
  • I am getting data (parent id) from oracle table using a polling statement. I need to implement debatching in the polling statement so that each record (parent id) is in a seperate file. How can this be acheived??

    Ok, don't make this more complicated that it needs to be.  You have two options:

    1. SELECT the records out 1 at a time by adding a TOP 1 clause to the SQL statement and enabling Poll While Data Available (this is much better done in a Stored Procedure since you have to mark the polled records).

    2. Debatch the records.  Here's an example with the Oracle DB Adapter but that really doesn't matter.  Debatching with Oracle is not different that any other Xml Debatching. http://dpsbali-biztalkweblog.blogspot.com/2011/10/debatch-biztalk-wcf-oracle-adapter.html

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 12:15 PM
    Thursday, July 17, 2014 2:42 PM
  • Hi Vivin,

    You can look into Richard Seroter 's blog post for the debatching

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

    Thanks

    Abhishek

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 12:15 PM
    Thursday, July 17, 2014 3:02 PM
  • Debatching cannot be done in an Adapter (let one the Oracle Adapter), but rather in an Pipeline.

    1) Configure the Oracle as intended and make sure you can poll the intended Batched XML into BizTalk, using a PassthrouReceive Pipeline.

    2) Create an Envelope Schema, matching your Root Element and Root Namespace and set set the Body XPath to POLLINGSTMTRECORD. 

    3) Deploy the Schema and use the XMLReceive Instead of the PassThrouReceive, then the Pipeline will automatically debatch the messages.

    Morten la Cour

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 12:15 PM
    Friday, July 18, 2014 5:19 AM
  • Then you have to change the .Net Type name.

    You cannot have the Root Reference set, that's why the BizTalk MessageType is not available.

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 12:49 PM
    Monday, July 21, 2014 2:38 PM

All replies

  • Hi Vivin,

    I assume that you are polling data and want to group and separate the data based on parent id, you can go through following article to see how it can be done :

     http://social.technet.microsoft.com/wiki/contents/articles/18845.biztalk-server-2010-grouping-and-debatchingsplitting-inbound-messages-typedpolled-from-wcf-sql-adapter.aspx


    Maheshkumar S Tiwari|User Page|Blog|BizTalk Server : How Map Works on Port Level

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 12:14 PM
    Thursday, July 17, 2014 6:03 AM
  • Hi Vivin,

    What you need to do is to have a typed polling in your Oracle DB which will generate a typed schema result set .

    Below is the steps for this .

    1) click the topmost Schema node(Auto generated one), and changed its Envelope property to Yes.

    2)click the TypedPolling node (which acts as the primary root that comes out of the adapter) and set the Body XPath value to the node ABOVE the eventual leaf node.

    3)select the leaf node and set its Max Occurrence from Unbounded to 1 .

     ecord gets debatched automatically by BizTalk in the XML receive pipeline, the resulting TypedPollingResultSet0 message(which matches a message type known by BizTalk) gets put in the MessageBox and routed around.

    Thanks

    Abhishek


    Thursday, July 17, 2014 7:06 AM
  • My Polling statement looks as below and <POLLINGSTMT xmlns="http://Microsoft.LobServices.OracleDB/2007/03/POLLINGSTMT">
    <POLLINGSTMTRECORD>
    <POLLINGSTMTRECORD>
    <PUIH_PID>165a3ff8-56f2-44b9-ad2e-cb0e28279a19</PUIH_PID>
    <PUIH_SYNTAX>UNOA</PUIH_SYNTAX>
    <PUIH_SYNTAX_VERSION>1</PUIH_SYNTAX_VERSION>
    <PUIH_SENDER>AEJEA</PUIH_SENDER>
    <PUIH_RECPT>MSC</PUIH_RECPT>
    <PUIH_DATE>140430</PUIH_DATE>
    <PUIH_TIME>915</PUIH_TIME>
    <PUIH_CONTROL_REFERENCE>231633</PUIH_CONTROL_REFERENCE>
    <PUIH_CREATED_ON>2014-07-16T10:03:11</PUIH_CREATED_ON>
    <PUIH_FILE_NAME>D:\Shared Files\Codecoin\CODECO_MSL_111111_20140430091515.63714611.edi</PUIH_FILE_NAME>
    </POLLINGSTMTRECORD>
    <POLLINGSTMTRECORD>
    <PUIH_PID>a0a6ca94-acb0-46a6-b2c0-7650ccfea73a</PUIH_PID>
    <PUIH_SYNTAX>UNOA</PUIH_SYNTAX>
    <PUIH_SYNTAX_VERSION>1</PUIH_SYNTAX_VERSION>
    <PUIH_SENDER>AEJEA</PUIH_SENDER>
    <PUIH_RECPT>MSC</PUIH_RECPT>
    <PUIH_DATE>140430</PUIH_DATE>
    <PUIH_TIME>915</PUIH_TIME>
    <PUIH_CONTROL_REFERENCE>231633</PUIH_CONTROL_REFERENCE>
    <PUIH_CREATED_ON>2014-07-16T10:03:11</PUIH_CREATED_ON>
    <PUIH_FILE_NAME>D:\Shared Files\Codecoin\CODECO_MSL_111111_20140430091515.63714611.edi</PUIH_FILE_NAME>
    </POLLINGSTMTRECORD>
    <POLLINGSTMTRECORD>
    <PUIH_PID>f45526d3-6ef1-4552-9bc3-4ae36345c64e</PUIH_PID>
    <PUIH_SYNTAX>UNOA</PUIH_SYNTAX>
    <PUIH_SYNTAX_VERSION>1</PUIH_SYNTAX_VERSION>
    <PUIH_SENDER>AEJEA</PUIH_SENDER>
    <PUIH_RECPT>MSC</PUIH_RECPT>
    <PUIH_DATE>140430</PUIH_DATE>
    <PUIH_TIME>915</PUIH_TIME>
    <PUIH_CONTROL_REFERENCE>231633</PUIH_CONTROL_REFERENCE>
    <PUIH_CREATED_ON>2014-07-16T10:03:11</PUIH_CREATED_ON>
    <PUIH_FILE_NAME>D:\Shared Files\Codecoin\CODECO_MSL_111111_20140430091515.63714611.edi</PUIH_FILE_NAME>
    </POLLINGSTMTRECORD>
    </POLLINGSTMTRECORD>
    </POLLINGSTMT>

    And the PUIH_PID in the polling statement is used as a input for the oracle stored procedures to fetch the data.

    Kindly advice how debatching can be handled in this case.(debatching the polling statement to map the id to stored procedures)


    Regards, Vivin.

    Thursday, July 17, 2014 7:24 AM
  • Hi Abhishek,

    Can  explain me in the terms of nodes available in my message so that i could understand better.


    Regards, Vivin.

    Thursday, July 17, 2014 7:47 AM
  • Also as in the example there no typed polling node in the schema in my application and there is onlu pollingstmtrecord.

    Regards, Vivin.

    Thursday, July 17, 2014 12:48 PM
  • I am getting data (parent id) from oracle table using a polling statement. I need to implement debatching in the polling statement so that each record (parent id) is in a seperate file. How can this be acheived??

    Ok, don't make this more complicated that it needs to be.  You have two options:

    1. SELECT the records out 1 at a time by adding a TOP 1 clause to the SQL statement and enabling Poll While Data Available (this is much better done in a Stored Procedure since you have to mark the polled records).

    2. Debatch the records.  Here's an example with the Oracle DB Adapter but that really doesn't matter.  Debatching with Oracle is not different that any other Xml Debatching. http://dpsbali-biztalkweblog.blogspot.com/2011/10/debatch-biztalk-wcf-oracle-adapter.html

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 12:15 PM
    Thursday, July 17, 2014 2:42 PM
  • Hi Vivin,

    You can look into Richard Seroter 's blog post for the debatching

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

    Thanks

    Abhishek

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 12:15 PM
    Thursday, July 17, 2014 3:02 PM
  • Thanks Abhishek and boatseller,

    In my application we are using the Oracle adapter and there is only two types of polling. (Polling and Notification) Therefore it is not possible to use the typedpolling. Kindly advice how debatching can be done in the oracle adapter.


    Regards, Vivin.

    Friday, July 18, 2014 4:49 AM
  • Debatching cannot be done in an Adapter (let one the Oracle Adapter), but rather in an Pipeline.

    1) Configure the Oracle as intended and make sure you can poll the intended Batched XML into BizTalk, using a PassthrouReceive Pipeline.

    2) Create an Envelope Schema, matching your Root Element and Root Namespace and set set the Body XPath to POLLINGSTMTRECORD. 

    3) Deploy the Schema and use the XMLReceive Instead of the PassThrouReceive, then the Pipeline will automatically debatch the messages.

    Morten la Cour

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 12:15 PM
    Friday, July 18, 2014 5:19 AM
  • As specified in the above links i have specified the envelope as 'YES' for the polling statement schema and followed the steps in the link but still i am not getting the debatched messages.

    Regards, Vivin.

    Friday, July 18, 2014 6:01 AM
  • Did you also set the Body XPath pointing at the POLLINGSTMT element?

    If so, try using the XmlDasm.exe Pipeline Tool for debugging your Envelope Schema towards some sample XML.


    Morten la Cour

    Friday, July 18, 2014 6:06 AM
  • i have set the Body XPath of the POLLINGSTMT element to the next child node POLLINGSTMTRECORD. Then the leaf node's max occurance has been set to 1.

    Regards, Vivin.

    Friday, July 18, 2014 6:12 AM
  • Slow down and follow the examples closely.  Debatching with the WCF Oracle adapter works fine but it's easy to miss one of the steps.
    Friday, July 18, 2014 11:24 AM
  • Kindly advice if any of the steps is missing in the above polling statement schema.


    Regards, Vivin.

    Friday, July 18, 2014 1:06 PM
  • Also there is only one schema in the application.

    Regards, Vivin.

    Friday, July 18, 2014 1:07 PM
  • Are you using the XmlDisassembler in the Pipeline?

    Also, remove the Root Reference setting, both definitions need to be available.

    Friday, July 18, 2014 1:33 PM
  • I am not using any custom pipeline. I using the default xml receive pipeline. Also i have added the root reference since i was receiving the below error

    "This schema file has a TypeName that collides with the RootNode TypeName of one of its root nodes. Make sure that they are different."


    Regards, Vivin.

    Monday, July 21, 2014 4:36 AM
  • Then you have to change the .Net Type name.

    You cannot have the Root Reference set, that's why the BizTalk MessageType is not available.

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 12:49 PM
    Monday, July 21, 2014 2:38 PM
  • I changed the typename of the schema now it is compiled but still the file is not getting debatched.

    Regards, Vivin.

    Tuesday, July 22, 2014 5:57 AM
  • Hi adding to the previous steps i used a receive pipeline with a XML disassembler component and chose the envelope and document schemas. Then i used the custom receive pipeline in the admin console and the file got debatched.

    Regards, Vivin.

    • Marked as answer by Vivin Muthu Monday, July 28, 2014 10:07 AM
    • Unmarked as answer by Vivin Muthu Monday, July 28, 2014 12:18 PM
    Monday, July 28, 2014 10:07 AM
  • Those steps were provided by other posters.  You really should mark the original answers and helpful posts as such.
    Monday, July 28, 2014 11:48 AM
  • Hi Vivin,

    I am also going through the same.

    Could please write it in more details?

    The scenario is same , i need to debatch the data by customer code.

    Please provide details? its for polling data only.

    Thanks & Regards,

    Abhinav

    Thursday, November 10, 2016 6:39 AM
  • Hi Abhinav,

    can you please open a new thread in the forum as this question has been answered and the thread is really old. I would request you to create a thread and details your issue there so that we can help you in a better way

    Regards


    Mandar Dharmadhikari

    Friday, November 11, 2016 3:32 AM