locked
Mapping Stored Procedure Schema RRS feed

  • Question

  • Here is my default Inbound message structure which I need to map to my DB:

    Inbound Xml Document:

    <?xml version="1.0" encoding="utf-8"?>
    <Root>
     <ElementA> <!-- unbounded -->
      <ElementB> <!-- unbounded -->
       <Estate></Estate> <!-- maxOccur = 1 -->
       <Contact></Contact> <!-- maxOccur = 1 -->
      </ElementB>
     </ElementA>
    </Root>
    As you can see I need to map Estate and Contact to the relevant DB tables "Estates" and "Contacts".

    I would like to try the mapping within one step. My idea is to map immediately against the StoredProcedure-Schema. Therefore I created a composite schema which looks like this:

    Target schema for StoredProcedure-Calls:

    <?xml version="1.0" encoding="utf-8"?>
    <Request>
     <ElementA> <!-- unbounded -->
      <ElementB> <!-- unbounded -->
       <SP1_Name></SP1_Name> <!-- referenced Data Type Structure (imported xsd) -->
       <SP2_Name></SP2_Name> <!-- referenced Data Type Structure (imported xsd) -->
      </ElementB>
     </ElementA>
    </Request>
    

    And a possible output could look like this:

    Outbound Xml Document (after mapping):

    <?xml version="1.0" encoding="utf-8"?>
    <Request>
     <ElementA>
      <ElementB>
       <SP1_Name whatItdoes="StoredProcedure_Update_or_Insert_ForEstate">
        <param1></param1>
        <param2></param2>
        <param3></param3>
        <param4></param4>
       </SP1_Name>
       <SP2_Name whatItdoes="StoredProcedure_Update_or_Insert_ForContact">
        <param1></param1>
        <param2></param2>
        <param3></param3>
       </SP2_Name>
      </ElementB>
      <ElementB>
       <SP1_Name whatItdoes="StoredProcedure_Update_or_Insert_ForEstate">
        <param1></param1>
        <param2></param2>
        <param3></param3>
        <param4></param4>
       </SP1_Name>
       <SP2_Name whatItdoes="StoredProcedure_Update_or_Insert_ForContact">
        <param1></param1>
        <param2></param2>
        <param3></param3>
       </SP2_Name>
      </ElementB>
     </ElementA>
     <ElementA> <!-- unbounded -->
      <ElementB> <!-- unbounded -->
       <SP1_Name whatItdoes="StoredProcedure_Update_or_Insert_ForEstate">
        <param1></param1>
        <param2></param2>
        <param3></param3>
        <param4></param4>
       </SP1_Name>
       <SP2_Name whatItdoes="StoredProcedure_Update_or_Insert_ForContact">
        <param1></param1>
        <param2></param2>
        <param3></param3>
       </SP2_Name>
      </ElementB>
     </ElementA>
     <!-- and so on (many other following <ElementA> nodes with different number of <ElementB> childNodes)-->
    </Request>

    Would you say it is possible what I am trying to do? Is it possible to send this output message to WCF-Custom SQL Adapter with "CompositeOperation" action? I tried it out but I encountered a problem. When running Test Map in VS2010 everything gets mapped like it should but after deployment in BizTalk 2010 Beta the mapped output document is not complete. At the end of the output document some tags are missed. The output XmlDocument looks then like this (for example):

    Corrupt Outbound Xml Document:

    <?xml version="1.0" encoding="utf-8"?>
    <Request>
     <ElementA>
      <ElementB>
       <SP1_Name whatItdoes="StoredProcedure_Update_or_Insert_ForEstate">
        <param1></param1>
        <param2></param2>
        <param3></param3>
        <param4></param4>
       </SP1_Name>
       <SP2_Name whatItdoes="StoredProcedure_Update_or_Insert_ForContact">
        <param1></param1>
        <param2></param2>
        <param3></param3>
       </SP2_Name>
      </ElementB>
      <ElementB>
       <SP1_Name whatItdoes="StoredProcedure_Update_or_Insert_ForEstate">
        <param1></param1>
        <param2></param2>
        <param3></param3>
        <param4></param4>
       </SP1_Name>
       <SP2_Name whatItdoes="StoredProcedure_Update_or_Insert_ForContact">
        <param1></param1>
        <param2></param2>
        <param3></param3>
       </SP2_Name>
      </ElementB>
     </ElementA>
     <ElementA> <!-- unbounded -->
      <ElementB> <!-- unbounded -->
       <SP1_Name whatItdoes="StoredProcedure_Update_or_Insert_ForEstate">
        <param1></param1>
        <param2></param2>
        <param3></par

    Would be cool if you could give me an advise how to solve this problem and if my idea is practical and realizable...

    Thank you in anticipation!

    Saturday, September 18, 2010 11:41 AM

Answers

  • It seems I have limited knowledge about xslt mapping. It´s enough if you take this as a request schema for stored procedures:

    <?xml version="1.0" encoding="utf-8"?>
    <Request>
     <SP1_Name></SP1_Name>
     <SP2_Name></SP2_Name>
    </Request>
    
    

    There is no need for looping between source and destination schema. This was my mapping before I realized there is no need for looping:

    (Inbound) <ElementA> ----> Looping Functoid ----> <ElementA> (Outbound)
    
    (Inbound) <ElementB> ----> Looping Functoid ----> <ElementB> (Outbound)
    
    (Inbound) <Estate> -----> Mapping -----> <SP1_Name> (Outbound)
    
    (Inbound) <Contact> -----> Mapping -----> <SP2_Name> (Outbound)
    

    You should create SP1_Name and SP2_Name as unbounded records with data structure type of your stored procedure schema generated by consume adapter service. Thats all you need to do.

    Ok problem SOLVED! Goodbye...

    Saturday, September 18, 2010 6:25 PM