BizTalk splitting stored procedure result

Question

When I call a stored procedure from BizTalk the result is split across multiple nodes. The stored procedure returns XML which is mangled by being split.

Multiple sources state the correct configuration will automatically assemble the result but I'm not able to make it work.

Example result:

<bsp_FA_StatementOutputResponse xmlns="http://schemas.microsoft.com/Sql/2008/05/TypedProcedures/dbo">
  <StoredProcedureResultSet0>
    <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/bsp_FA_StatementOutput">
      <XML_F52E2B61-18A1-11d1-B105-00805F49916B>&lt;Root...</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
      </StoredProcedureResultSet0>
    <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/bsp_FA_StatementOutput">
      <XML_F52E2B61-18A1-11d1-B105-00805F49916B>...</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
    </StoredProcedureResultSet0>
    <StoredProcedureResultSet0 xmlns="http://schemas.microsoft.com/Sql/2008/05/ProceduresResultSets/dbo/bsp_FA_StatementOutput">
      <XML_F52E2B61-18A1-11d1-B105-00805F49916B>sactionDate&gt;2020-10-6&lt;/...;&lt;/Root&gt;</XML_F52E2B61-18A1-11d1-B105-00805F49916B>
    </StoredProcedureResultSet0>
  </StoredProcedureResultSet0>
  <ReturnValue>0</ReturnValue>
</bsp_FA_StatementOutputResponse>

The stored procedure works correctly when executed manually.

• I created the send port using the 'Add generated items' wizard.
• I chose 'strongly typed' from the wizard.
• I used the bindings provided by the wizard.

Any help is appreciated.

Answer 1

Sorry, I misunderstood first, see below.

If you don't want to split resultsets from a stored procedure, just remove the is_enelope="yes" from the typed procedure schema.

Answer 2

If you've had this issue I've documented the solution I came up with here. I used a manual XSLT map to reassemble the fragmented result.