none
Debatching stored procedure that returns multiple result sets

    Question

  • Scenario:

    I have an SQL stored procedure that returns two different result sets. The first result set is customer data, and the 2nd result set is order data. For example, the stored procedure might return 5 rows of customer data in the 1st result set, and 10 rows of order data in the 2nd result set. I have done the following:

    I used the "Consume Adapter Service" wizard to consume the stored procedure for TypedPolling. This generates a binding file and a single schema. This schema has 5 root nodes:

    1. TypedPollingResultSet0
    2. ArrayOfTypedPollingResultSet0
    3. TypedPollingResultSet1
    4. ArrayOfTypedPollingResultSet1
    5. TypedPolling

    Problem:

    What I am trying to do is debatch this schema, so that I will end up with an individual message for each row of data. In the example I provided above, I would end up with 5 customer messages and 10 order messages after debatching. I have managed to split the initial message returned by the stored procedure into 2 messages by marking the schema as an envelope, and setting the BodyXPATH property on the "TypedPolling" node to itself. This separates out the customer data from the order data. However, I still end up with messages that contain more than occurrence of the data. I.e. the output customer message contains data for 5 customers, and the output order message contains data for 10 orders.

    Can anyone think of a way to do this? I have tried fiddling with the "BodyXPath" setting on the other root nodes of the schema, but have come up with nothing but errors. Any help is greatly appreciated.

    Thursday, March 16, 2017 2:54 PM

All replies

  • Well, I was going to do an Article about this as well but...never did :().

    Anyway, The XmlDisassembler will do chain debatching (there's not actual term for this, that's just what I call it).

    Meaning, if the schema of an individual debatched message is also marked as an Envelope, the Disassembler will then try to debatch that.

    So (from memory) you debatch TypedPolling (Envelope) into TypedPollingResultSet0 (Envelope) and TypedPollingResultSet1 (Envelope) which then debatch into the individual Rows.

    Thursday, March 16, 2017 4:37 PM
    Moderator
  • Hi John. Thanks so much for your reply. I understand the concept of what you're suggesting, but I am struggling to figure out how to implement it. After I've debatched "TypedPolling", how would I mark the TypedPollingResultSet0, TypedPollingResultSet1, etc as enveloped?

    It occurred to me to generate new schemas in my project for TypedPollingResultSet0, TypedPollingResultSet1, etc. using the well-formed XML that is output by the first debatching of the TypedPolling schema. I tried doing this, but even when I set the XPATH to the top node on the 2 new schemas, it seems to be debatching down to the element level (I get an error that it can't find the document specification type "....#CustomerID"), as opposed to one level above it.

    Thursday, March 16, 2017 5:40 PM
  • But you don't need to generate from Well Formed Xml.  You just use the schemas generated by the Adapter Wizard.

    Thursday, March 16, 2017 6:31 PM
    Moderator
  • That's my problem I guess, is that the adapter wizard only generates one schema (with 5 root nodes). I set the envelope property to "Yes" on the schema that is generated, and the XPATH on the TypedPolling node, but I don't know how to make BizTalk aware that the unbatched messages are also envelope schemas. Hopefully this makes sense.

    Thanks again for your help.

    Thursday, March 16, 2017 7:11 PM
  • Ah, right. Sometime it separates the root schemas and references the Result Set types. I hate to say, I've never been sure what influences this, but...it doesn't matter...

    What you need to is use two copies of the schema then, one marked as Envelope, the other not.

    When configuring the XmlDisassembler, explicitly specify the three Roots from the Envelope version in the Envelope schemas and the two from the non-envelope version in the Document schemas list.

    It's perfectly fine to deploy essentially two copies of the same schema since you are explicitly setting them in the XmlDisassembler.

    Thursday, March 16, 2017 8:14 PM
    Moderator
  • You can achieve this by setting Customers and Orders Record elements Max Occur to 1

    Friday, March 17, 2017 3:13 AM
  • I tried this, but I hit the issue that I expected to hit, which is now having multiple schemas with the same namespace and root node, which causes the receive pipeline to throw an error about having multiple schemas that match the message type. Any ideas?
    Friday, March 17, 2017 11:49 AM
  • I've tried this, to no avail. In the screenshot you provided, you only have one result set. Have you been able to get this to work using more than 1 result set? Thanks.
    Friday, March 17, 2017 11:50 AM
  • To remind:

    When configuring the XmlDisassembler, explicitly specify the three Roots from the Envelope version in the Envelope schemas and the two from the non-envelope version in the Document schemas list.

    It's perfectly fine to deploy essentially two copies of the same schema since you are explicitly setting them in the XmlDisassembler.

    As an aside, I don't rely on automatic resolution and always explicitly specify the Schemas in the XmlDisassembler.
    Friday, March 17, 2017 12:10 PM
    Moderator
  • I must be missing something. Here's what I did:

    1. Created a copy of my original 5-noded schema that was generated by the wizard
    2. The original schema has the envelope property set to Yes, and the XPATH expression on the TypedPolling node set to itself.
    3. The copy schema has the envelope property set to No.
    4. Created a new receive pipeline. Added the XML disassembler to the pipeline. Added the "TypedPolling", "TypedPollingResultSet0" and "TypedPollingResultSet1" nodes from the original schema to the Envelope Schemas list on the disassembler.
    5. Added the TypedPollingResultSet0 and TypedPollingResultSet1 nodes from the copy schema to the Document Schemas list.
    6. Deploy the assembly. Configure the receive location to use the new pipeline.

    When I start the application, I still get the error that multiple schemas match the message. Am I missing something?

    As always, thanks so much for your help, it's greatly appreciated.

    Friday, March 17, 2017 12:38 PM
  • With two exceptions, one minor, one huge, Schemas are all identified not by the ns#root combination, but by .Net Type name.

    So, for the two copies of the .xsd, be careful that they have distinct and meaningful .Net Type Names.  Then, be equally careful to select the correct ones when configuring the XmlDisassembler.

    Since it hasn't been mentioned, you do need to create, deploy and select a custom Receive Pipeline using your configured XmlDisassembler.  You cannot use the default Pipelines for this.

    Friday, March 17, 2017 12:46 PM
    Moderator
  • Thanks for your reply. I did create a custom receive pipeline, to which I added the XMLDisassembler component, then added the individual schema nodes to the Envelope and Document schema lists. 

    Regarding your comment on distinct .NET Type names, where/how would I set these names on the schemas in order to differentiate them? My apologies if this is a stupid question. I come from a T-SQL background and don't have much knowledge on .NET or XML.

    Thanks again.

    Friday, March 17, 2017 12:53 PM
  • It's on the Properties of the .xsd file itself.
    Friday, March 17, 2017 1:15 PM
    Moderator
  • The two schemas do have different type names, and I still receive the "multiple schemas..." error. I guess I'm at a bit of an impasse right now.
    Friday, March 17, 2017 1:24 PM
  • I don't think this is gonna work.

    But since your gonna split the message anyway, can't you split the procedure in two procedures with one resultset and a seperate receivelocation foreach?

    This will save you a lot of work.

    Sunday, March 19, 2017 8:59 PM
  • Can you provide more details about your requirement? Where you want to use these different data sets? Are you going to combine them in Orchestration? Or Are you going to send this to different destinations?

    Combine them in stored procedure if you are going to combine them in Orchestration.

    Or use different procedures as Rex van der Iaan suggested.

    Monday, March 20, 2017 3:22 PM