none
How to get on single datatable in response of solicit-response WCF SQL send port RRS feed

  • Question

  • Hi there,

    In order to dynamically set SP parameters on WCF SQL port I am using solicit-response WCF SQL send port instead of a WCF receive port. But the problem is the dataset returned is split into multiple <newtable> nodes there by only returning string in the first occurence of <newtable>. 

    Is there a way to just get the entire resultset from SP in one single nametable so that when us xpath under messages tab, is should get split messages. NOTE: I have st uo an envelope schema to wrap around the xml they will be there in the <newtable>, which enables further split of that xmlstring into the types i want.

    Regards

    Phanindra

    Friday, April 4, 2014 3:38 PM

Answers

  • Finally! that worked! 

    Many thanks mate, really appreciate your help.

    STAR!

    • Marked as answer by Pengzhen Song Friday, April 11, 2014 1:49 AM
    Tuesday, April 8, 2014 1:01 PM
  • The Action and namespace also have to reflect XmlProcedure:

    • XmlProcedure/<schema_name>/<procedure_name>

    Run the Wizard again and make sure to set the correct options.



    Tuesday, April 8, 2014 12:06 PM

All replies

  • Sorry, it's a little difficult to understand what you're describing.

    What you get in BizTalk is entirely dependend on the output from the Stored Procedure, so yes, but the SP is where you have to make the changes to how the results are output.

    Friday, April 4, 2014 3:41 PM
  • No worries, Following is how it's being retunred from stored proc......

    -<NewDataSet xmlns="">
      -<NewTable>
        <XML_F52E2B61-18A1-11d1-B105-00805F49916B>
          <Activities xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
            <Upsert_RenewalsActivity>
              <BusinessUnit>7777</BusinessUnit>
              ...
              <Surname>xxxxx</Surname>
            </Upsert_RenewalsActivity>
            <Upsert_RenewalsActivity>
              <BusinessUnit>xxx</BusinessUnit>
              ...
              <Surname>xxxx</Surname>
            </Upsert_RenewalsActivity>
            <Upsert_RenewalsActivity>
              <BusinessUnit>000</BusinessUnit>
              ...
              <PolicyDiscount>0.00
        </XML_F52E2B61-18A1-11d1-B105-00805F49916B>
      </NewTable>-<NewTable>
        <XML_F52E2B61-18A1-11d1-B105-00805F49916B>
          </PolicyDiscount>
          <PolicyPremium>195.31</PolicyPremium>
          ...
          <Surname>xxxx</Surname>
          </Upsert_RenewalsActivity>
          </Activities>
        </XML_F52E2B61-18A1-11d1-B105-00805F49916B>
      </NewTable>
    </NewDataSet>

    FOLLOWING IS HOW I WILL LIKE IT

    -<NewDataSet xmlns="">
      -<NewTable>
        <XML_F52E2B61-18A1-11d1-B105-00805F49916B>
          <Activities xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
            <Upsert_RenewalsActivity>
              <BusinessUnit>7777</BusinessUnit>
              ...
              <Surname>xxxxx</Surname>
            </Upsert_RenewalsActivity>
            <Upsert_RenewalsActivity>
              <BusinessUnit>xxx</BusinessUnit>
              ...
              <Surname>xxxx</Surname>
            </Upsert_RenewalsActivity>
            <Upsert_RenewalsActivity>
              <BusinessUnit>000</BusinessUnit>
              ...
              <PolicyDiscount>0.00
              </PolicyDiscount>
          <PolicyPremium>195.31</PolicyPremium>
          ...
          <Surname>xxxx</Surname>
          </Upsert_RenewalsActivity>
          </Activities>
        </XML_F52E2B61-18A1-11d1-B105-00805F49916B>
      </NewTable>
    </NewDataSet

    NOTE the <PolicyDiscount> tag towards the end and compare it with how I am getting it current ! Basically IO want the SP results to be in one single       <NewTable> node

    Make sense?

    NOTE: It looks like split the result into new <NewTable> after say every 2032 or 2033 characters

    Thanks and Regards

    Phanindra


    • Edited by criccrazy Monday, April 7, 2014 2:06 PM
    Monday, April 7, 2014 1:57 PM
  • It look like a mis-match between the Stored Procedure output type vs the settings in the Send Port.

    Does the SP return Xml or Resutl Sets?

    Monday, April 7, 2014 2:14 PM
  • Stored proc returns an XML....this is how it ends - FOR  XML PATH('activity') ,Root('Activities') , ELEMENTS

    I tried using both types of generated schemas - procedure as well as strongly typed !

    Following is the complete response that I get:

    -<Response xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">

      -<StoredProcedureResultSet0>
        -<StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/Get_CCD_Renewals">
          <XML_F52E2B61-18A1-11d1-B105-00805F49916B>
            <Activities xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
              <Activity>
                <BusinessUnit>xxx</BusinessUnit>
                ...
                <Surname>xxxx</Surname>
              </Activity>
              <Activity>
                <BusinessUnit>xxx</BusinessUnit>
                ...
                <Surname>xxxx</Surname>
              </Activity>
              <Activity>
                <BusinessUnit>xxxx</BusinessUnit>
                ...
                <PolicyDiscount>0.00
          </XML_F52E2B61-18A1-11d1-B105-00805F49916B>
        </StoredProcedureResultSet0>-<StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/Get_CCD_Renewals">
          <XML_F52E2B61-18A1-11d1-B105-00805F49916B>
            </PolicyDiscount>
            <PolicyPremium>xxxxx</PolicyPremium>
            ..
            </Activity>
            </Activities>
          </XML_F52E2B61-18A1-11d1-B105-00805F49916B>
        </StoredProcedureResultSet0>
      </StoredProcedureResultSet0><ReturnValue>0</ReturnValue>
    </Response>


    • Edited by criccrazy Monday, April 7, 2014 2:47 PM
    Monday, April 7, 2014 2:39 PM
  • Ah..ok.  The problem is the Send Port is not properly configured for an Xml Stored Procedure.  This is probably because the correct options were not set in the Wizard.

    To execute an Xml Stored Procedure, these two properties must be set on the binding properties in the Send Port:

    • XmlStoredProcedureRootNodeName
    • XmlStoredProcedureRootNodeNamespace

    This is what tells the adapter to interpret the TDS as an Xml, not a standard Result Set.

    You would also have to set these in the Wizard.  The Wizard will generate the Schema for the Request Message, but you will have to produce the Schema for the result yourself.

    More information:

    http://msdn.microsoft.com/en-us/library/dd787898.aspx

    http://www.codeproject.com/Articles/37808/How-to-invoke-Stored-Procedures-with-the-FOR-XML-c

    Monday, April 7, 2014 3:54 PM
  • Hi mate,

    Thanks, that was missing! However I just made the change to have the above two fields set but no difference. I still get the the xml split across multiple nodes. 

    I went through those articels and they relate to orchestration Note I am using pure messaging solution, stand alone solicit response send port which writes the xml response to a file location. 

    Any more ideas please?

    Thanks

    Tuesday, April 8, 2014 9:20 AM
  • The Action and namespace also have to reflect XmlProcedure:

    • XmlProcedure/<schema_name>/<procedure_name>

    Run the Wizard again and make sure to set the correct options.



    Tuesday, April 8, 2014 12:06 PM
  • Finally! that worked! 

    Many thanks mate, really appreciate your help.

    STAR!

    • Marked as answer by Pengzhen Song Friday, April 11, 2014 1:49 AM
    Tuesday, April 8, 2014 1:01 PM
  • Hi

    I am facing the same issue. CAn you please suggest what is the solution that is working for you.

    Thanks in Advance.

    Manish

    Tuesday, June 21, 2016 9:10 AM