CODEWITHSUNDEEP
wcfbiztalkbiztalk-2013
Jay
Jay

Reputation: 14441

BizTalk splitting stored procedure result

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.

Any help is appreciated.

Upvotes: 0

Views: 302

Answers (2)

Martin Bring
Martin Bring

Reputation: 1246

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.

Upvotes: 1

Jay
Jay

Reputation: 14441

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.

Upvotes: 0

Related Questions