none
Looping in BizTalk RRS feed

  • Question

  • On an Oracle database I got a customer table with at Id and Name and so, and a number of customerstuff tables all related a key. With the Oracle WCF adapter in BizTalk a need to create a file with all the customers and there stuff Like this:

    <Root>
      <Customers>
        <Customer>
          <Id>1</Id>
          <Name>A</Name>
          <Stuff1>
            <data>
             .
             .
            </data>
          </Stuff1>
          .
          .
          <Stuff6>
            <data>
            .
            .
            </data>
          </Stuff6>
       </Customer>
       <Customer>
         <Id>2</Id>
          <Name>B</Name>
          <Stuff1>
          .
          .
          .
          </Stuff6>
        </Customer>
      </Customers>
    </Root>

    I started out with a select of all the customers, and in Orchestration I loop over them, selecting from each customerstuff table with the key from the current customer getting me all the data ready for the first customer node in the result file.

    Question: how do I build the result file?   I have to add the current customer/customerstuff data to the result file, then do a new loop while still holding on to the result file, how can I do that in BizTalk ?


    Torben

    Friday, February 21, 2014 12:47 PM

Answers

  • The complicating factor here is that Oracle/WCF combination doesn't support Xml so you can't poll a hierarchy.

    So, you have a couple of options.

    1. Poll all the data out using a Stored Procedure which returns multiple results sets, Customers, Stuff1, Stuff2, etc. Then, use a customized Map to resequence the data by id. You can't do that all in the Mapper, but it can get you pretty far.

    2. First, get a list of Customer ID's that you need to group, then in an Orchestration loop through the list and call Oracle to return that one customer. You can then batch manually using XmlDocument or a running a Send Pipeline.

    There's some other options is these don't work out.

    • Marked as answer by Pengzhen Song Monday, March 3, 2014 1:16 AM
    Friday, February 21, 2014 1:57 PM
  • The return type would be whatever is generated by the Wizard.  The result sets will appear in the same order as they are returned by the stored procedure.

    It's a little hard to explain but once you try it, generating the schema, you'll see it's pretty straight forward.

    • Marked as answer by Pengzhen Song Monday, March 3, 2014 1:15 AM
    Wednesday, February 26, 2014 2:59 PM

All replies

  • The complicating factor here is that Oracle/WCF combination doesn't support Xml so you can't poll a hierarchy.

    So, you have a couple of options.

    1. Poll all the data out using a Stored Procedure which returns multiple results sets, Customers, Stuff1, Stuff2, etc. Then, use a customized Map to resequence the data by id. You can't do that all in the Mapper, but it can get you pretty far.

    2. First, get a list of Customer ID's that you need to group, then in an Orchestration loop through the list and call Oracle to return that one customer. You can then batch manually using XmlDocument or a running a Send Pipeline.

    There's some other options is these don't work out.

    • Marked as answer by Pengzhen Song Monday, March 3, 2014 1:16 AM
    Friday, February 21, 2014 1:57 PM
  • Thank you I'm going for nr 2.

    If I had a Stored Procedure which returns multiple results sets, what would the return type be ?


    Torben

    Wednesday, February 26, 2014 8:34 AM
  • You should get it back as Xml file, with multiple records having repeatable nodes.

    One node for each row of table

    Wednesday, February 26, 2014 8:40 AM
  • The return type would be whatever is generated by the Wizard.  The result sets will appear in the same order as they are returned by the stored procedure.

    It's a little hard to explain but once you try it, generating the schema, you'll see it's pretty straight forward.

    • Marked as answer by Pengzhen Song Monday, March 3, 2014 1:15 AM
    Wednesday, February 26, 2014 2:59 PM