none
BizTalk 2013 R2 - Generate strongly typed store procedure response message RRS feed

  • Question

  • I have a request / response stored procedure and want to generate a strongly typed schema.  The request message is generated fine but the response message is not coming strongly typed.  

    Generated Schema

    Here is a stored procedure (removed the business logic to make it simple):

    ALTER PROC [dbo].[GetParticipantIdNumberAsXML]
        @XML AS XML
    AS
    BEGIN
        SET NOCOUNT ON;

    --DECLARE @xml AS XML
    DECLARE @LastParticipantIdNumber BIGINT = 9543485387

    ;WITH cteIds AS
    (
    SELECT x.Members.value('IdentificationNumber[1]', 'VARCHAR(15)') AS IdentificationNumber
    ,x.Members.value('GroupNumber[1]', 'VARCHAR(15)') AS GroupNumber
    FROM @XML.nodes('/Members/Member') x(Members)

    )
    SELECT
       MemberTable.participantIDNumber
      ,cteIds.IdentificationNumber
      ,cteIds.groupNumber
    FROM
    MemberTable
    INNER JOIN cteIds ON RIGHT('0000000000' + cteIds.IdentificationNumber, 12) = Membertable.identificationNumber
    AND cteIds.GroupNumber = memberTable.groupNumber
    UNION
    SELECT
    RIGHT('00000000' + @LastParticipantIdNumber + ROW_NUMBER() OVER (ORDER BY cteIds.IdentificationNumber), 12) ParticipantIdNumber
      ,cteIds.IdentificationNumber
      ,cteIds.groupNumber
    FROM
    MemberTable
    RIGHT OUTER JOIN cteIds ON RIGHT('0000000000' + cteIds.IdentificationNumber, 12) = Membertable.identificationNumber
    AND cteIds.GroupNumber = memberTable.groupNumber
    WHERE MemberTable.participantIDNumber IS NULL
    --FOR XML RAW ('Member'), ROOT('Members'), ELEMENTS;
    --FOR XML RAW ('Member'), ROOT('Members'), ELEMENTS, XMLSCHEMA;
    FOR XML AUTO, XMLDATA, ELEMENTS;
    --FOR XML AUTO, ELEMENTS;
    END;

    --------------------------------------------------------------------------------------------------------------------------------

    I tried with XMLSCHEMA and XMLDATA but to no avail.

    I also used Strongly Type Procedure in the Consume Adapter Service window.  Nothing seems to be working.

    Any help will be truly appreciated.

    Thanks

    Thursday, March 22, 2018 6:14 PM

Answers

All replies

  • For the WCF-SQL adapter, you don't need to return it as XML using FOR XML etc.   Just return them as ordinary record sets and the adapter takes care of it correctly.
    Thursday, March 22, 2018 11:08 PM
  • Strongly Typed in the Wizard refers to responses that are SQL DataSets.

    It doesn't mean Schema Typed.  If you want to return Xml, you will have to create the Schema yourself.

    Thursday, March 22, 2018 11:34 PM
    Moderator
  • The strongly typed procedure in means that the data set would be strongly typed  when your stored procedure outputs a xml in response, then the rule of thumb is you create one more schema which matches the xml sent by the BizTalk , you extract the message from stored proc and create a message of in inhouse schema

    Mandar Dharmadhikari

    Friday, March 23, 2018 5:41 AM
    Moderator
  • Below is the stored procedure response.  It is sending the actual response (Members node) as a string.  Is there a possibility for the stored procedure to return the Member node as regular xml and not xmlAsString.

    <GetParticipantIdNumberAsXMLResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
    <StoredProcedureResultSet0>
    <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/GetParticipantIdNumberAsXML">
    <XML_F52E2B61-18A1-11d1-B105-00805F49916B>&lt;Members&gt;&lt;Member&gt;&lt;participantIDNumber&gt;00159&lt;/participantIDNumber&gt;&lt;IdentificationNumber&gt;356&lt;/IdentificationNumber&gt;&lt;groupNumber&gt;0000012150&lt;/groupNumber&gt;&lt;/Member&gt;&lt;Member&gt;&lt;participantIDNumber&gt;00165&lt;/participantIDNumber&gt;&lt;IdentificationNumber&gt;757&lt;/IdentificationNumber&gt;&lt;groupNumber&gt;0000012150&lt;/groupNumber&gt;&lt;/Member&gt;&lt;Member&gt;&lt;participantIDNumber&gt;00163&lt;/participantIDNumber&gt;&lt;IdentificationNumber&gt;384&lt;/IdentificationNumber&gt;&lt;groupNumber&gt;0000012150&lt;/groupNumber&gt;&lt;/Member&gt;&lt;Member&gt;&lt;participantIDNumber&gt;00167&lt;/participantIDNumber&gt;&lt;IdentificationNumber&gt;083&lt;/IdentificationNumber&gt;&lt;groupNumber&gt;0000012150&lt;/groupNumber&gt;&lt;/Member&gt;&lt;Member&gt;&lt;participantIDNumber&gt;00501&lt;/participantIDNumber&gt;&lt;IdentificationNumber&gt;749&lt;/IdentificationNumber&gt;&lt;groupNumber&gt;0000012150&lt;/groupNumber&gt;&lt;/Member&gt;&lt;Member&gt;&lt;participantIDNumber&gt;00162&lt;/participantIDNumber&gt;&lt;IdentificationNumber&gt;936&lt;/IdentificationNumber&gt;&lt;groupNumber&gt;0000012150&lt;/groupNumber&gt;&lt;/Member&gt;&lt;Member&gt;&lt;participantIDNumber&gt;00164&lt;/participantIDNumber&gt;&lt;IdentificationNumber&gt;416&lt;/IdentificationNumber&gt;&lt;groupNumber&gt;0000012150&lt;/groupNumber&gt;&lt;/Member&gt;&lt;Member&gt;&lt;participantIDNumber&gt;00168&lt;/participantIDNumber&gt;&lt;IdentificationNumber&gt;204&lt;/IdentificationNumber&gt;&lt;groupNumber&gt;0000012150&lt;/groupNumber&gt;&lt;/Member&gt;&lt;/Members&gt;</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
    </StoredProcedureResultSet0>
    </StoredProcedureResultSet0>
    <ReturnValue>0</ReturnValue>
    </GetParticipantIdNumberAsXMLResponse>

    Friday, March 23, 2018 1:41 PM
  • Friday, March 23, 2018 1:45 PM
    Moderator
  • Thanks everyone for replying to my post.

    I posted the solution which worked in my case at https://wp.me/p9KCCP-2f

    • Marked as answer by theman' Wednesday, March 28, 2018 9:01 PM
    Wednesday, March 28, 2018 9:01 PM