Table of Contents

Scenario

The requirement is to fetch the below column details from the stored procedure and grouping then based on Session_Id and the output should look like below.

Output should be the below format:

<ns0:ScheduleDetails xmlns:ns0="http://ESMSchedulerOrchV3.SACDetails">
  <Schudule>
    <SAC Session_Id="1" />
    <MessageDetails>
      <MessageDetail>
        <Message_Code>1</Message_Code>
        <MessageType>1</MessageType>
      </MessageDetail>
      <MessageDetail>
        <Message_Code>1</Message_Code>
        <MessageType>2</MessageType>
      </MessageDetail>
      <MessageDetail>
        <Message_Code>2</Message_Code>
        <MessageType>1</MessageType>
      </MessageDetail>
    </MessageDetails>
  </Schudule>
  <Schudule>
    <SAC Session_Id="2" />
    <MessageDetails>
      <MessageDetail>
        <Message_Code>1</Message_Code>
        <MessageType>1</MessageType>
      </MessageDetail>
      <MessageDetail>
        <Message_Code>1</Message_Code>
        <MessageType>2</MessageType>
      </MessageDetail>
      <MessageDetail>
        <Message_Code>2</Message_Code>
        <MessageType>1</MessageType>
      </MessageDetail>
    </MessageDetails>
  </Schudule>
</ns0:ScheduleDetails>

Source Schema to the map:

Stored Procedure Response:

Resolution

  • Build the orchestration looks the below:

  • Create a map and debug it to get the .xsl file to be opened. 
  • Add the below outline custom code to it:

<xsl:key name="SessionIdKey" match="/s1:Get_ESMActivationWindowScheduleResponse/s1:StoredProcedureResultSet0/s0:StoredProcedureResultSet0" use="s0:Session_Id" />
 
<xsl:for-each select="s0:StoredProcedureResultSet0[generate-id(.)=generate-id(key('SessionIdKey',s0:Session_Id))]">
 
<xsl:for-each select="key('SessionIdKey',s0:Session_Id)">
  • Test the map and will get the desired output. 

See Also

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.