none
BizTalk Server 2006 (SQL Adapter) : Receive Multiple XML Outputs From The Stored Procedure RRS feed

  • Question

  • I am calling a stored procedure from Receive Location using WCF SQL Adapter.

    The stored procedure has 3 SELECT statements. All 3 SELECT statements will return 1 XML each, so there will be total of 3 XMLs. So, when the stored procedure gets executed once by the SQL Adapter, the output will be 3 XMLs.

    The Receive Pipeline has only one XML Disassembler, where Document Schema is not specified.

    The WCF SQL Adapter receives only 1 XML which is the output of First SELECT statement. The rest 2 XMLs are ignored by the Adapter. Is there any way to process all the 3 XML's simultaneously in BizTalk ?

    Tuesday, September 11, 2012 5:59 AM

Answers

  • by default the adapter can only return 1 xml document into biztalk, so to solve this you need to create a envelope schema that then will contain the 3 xml documents coming in from SQL, when the envelope schema hits the xml receive pipeline it will then de-batch the envelope schema and submit the 3 contained xml documents into BizTalk.

    I is a good article from Richard about doing what you are trying to do: http://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/


    Bill Chesnut | BizTalk Server MVP | Mexia Consulting | Melbourne Australia
    http://www.biztalkbill.com
    Please indicate "Mark as Answer" if this post has answered the question.

    Wednesday, September 12, 2012 8:04 AM

All replies

  • you can create the schema from the three select statement make the three into one schema then it will work.

    Tuesday, September 11, 2012 8:52 AM
  • I don't think that Receive SQL Adapter has anything to do with schema. Is there any way to tell Adapter to use a particular schema ? It just executes the stored procedure and gets the resultset into BizTalk.

    In my case, the adapter is only picking the resultset from the First SELECT statement.

    Tuesday, September 11, 2012 9:19 AM
  • What type of polling are you doing, with a case like yours I typically create a envelope schema and in the adapter configuration I add it to here:

    The pipeline will then de-batch them and submit 3 messages to BizTalk


    Bill Chesnut | BizTalk Server MVP | Mexia Consulting | Melbourne Australia
    http://www.biztalkbill.com
    Please indicate "Mark as Answer" if this post has answered the question.

    Tuesday, September 11, 2012 11:43 AM
  • WCF SQL adapter receives only 1 XML by executing a stored procedure. That single XML contains 3 records under the root node, in your case. 1 record for each select statement. When you use TypedPolling on "InboundOperationType" in WCF SQL adapter, the following will be the structure of XML received by WCFSQL adapter. "<TypedPollingResultSet>" records' correspond to the select statements in the Stored Procedure.

    TypedPolling

             <TypedPollingResultSet0>

             <TypedPollingResultSet1>

             <TypedPollingResultSet2>

    <TypedPolling>


    Thank you! Please mark as answer or Vote as helpful, if this helps you.

    • Proposed as answer by ManojReddy Tuesday, September 11, 2012 4:15 PM
    Tuesday, September 11, 2012 3:51 PM
  • I still don't understand that why are we talking about schema here..

    Schema comes into picture only at the pipeline level. Isn't it ?

    I am only concerned here about how to get those 3 separate XMLs inside BizTalk.

    Currently only 1 XML is coming inside BizTalk Server 2006... It is not batched. The other 2 XMLS are completely ignored by WCF SQL Adapter.

    NOTE: The 2 yellow highlighted fields of RootNodeName and Namespace are just a wrapper which Adapter creates on the message returned from storedprocedure... There is no concept of schema here...

    Wednesday, September 12, 2012 7:55 AM
  • by default the adapter can only return 1 xml document into biztalk, so to solve this you need to create a envelope schema that then will contain the 3 xml documents coming in from SQL, when the envelope schema hits the xml receive pipeline it will then de-batch the envelope schema and submit the 3 contained xml documents into BizTalk.

    I is a good article from Richard about doing what you are trying to do: http://seroter.wordpress.com/2010/04/08/debatching-inbound-messages-from-biztalk-wcf-sql-adapter/


    Bill Chesnut | BizTalk Server MVP | Mexia Consulting | Melbourne Australia
    http://www.biztalkbill.com
    Please indicate "Mark as Answer" if this post has answered the question.

    Wednesday, September 12, 2012 8:04 AM