• about reply
Solidsoft Reply Logo
Menu
  • What we do
  • Pharmaceutical Sector
  • The Solid Blog
  • Newsroom
  • Contact Us
  • about Reply
Solidsoft Reply Logo

Search

Focus On

Blog

BizTalk 101 – How to consume a Complex SQL Stored Procedure XML Response, without a custom Pipeline

Author: Ahdil Maqsood

Throughout my BizTalk career, I’ve come across some ludicrously complex ways to solve problems that actually work straight out the box. It seems to be a common ‘easy fix’ for developers to just fall back on Custom Pipelines to solve any small problem. The following is one of those problems with a simple solution that seems to have been lost into the vast abyss of the web.

It should also stop developers from simply consuming a table using the Biztalk ’Add Generated Items - Consume Adapter Service’ wizard, which works, but you are stuck with a flat xml structure.

The Question!

So, How do we get a complex XML structure output from a SQL stored Procedure.

This is actually the easy part. It’s quite simple to have a Stored Procedure return an XML result set, for those of you that don’t know, google ‘For XML’.

When you view the StoredProcedure output in SQL, all looks well. The whole XML will be in a single cell, with a static Column Name ‘XML_F52E2B61-18A1-11d1-B105-00805F49916B’. Do not worry about the name, as it is static.

BizTalk 101 

The Problem!

The problem is when use a WCF adapter and consume as a TypedProcedure (or even NonTyped), it returns the following structure below; Escaping the characters and splitting the result set into two to three segments.

BizTalk 101 

This is where consensus normally splits. Someone has developed a Custom Pipeline to both unescape and combine the pieces, to give you the beautiful xml structure that most developers are used to.

This works and seems to be what is normally discovered and actioned when you try to search the complex inter-web for a solution. However, you don’t need to link to it, there is a simpler way!

The Correct Solution!

Everyone seems to have heard of TypedPolling, however, the solution here is aptly named ‘XMLPolling’.

Set the following three settings in the Send Port Bindings:

  • InboundOperationType = XmlPolling
  • XmlStoredProcedureRootnodename
  • XmlStoredProcedureRootnodenamespace

BizTalk 101 

And the action to XmlProcedure;

Action="XmlProcedure/dbo/ProcedureName"

BizTalk 101 

Now you have a complex xml stored procedure consumed into Biztalk as an XML. No Custom Pipeline needed. Just minor tweaking to a send port.

RELATED CONTENTS

Solidsoft Reply becomes a GS1 UK Partner

Solidsoft Reply becomes a GS1 UK Partner 0

Read more

Pharmaceutical Sector

Solidsoft Reply develop cloud-based solutions, integrate unconnected systems and automate business functions to provide the step change needed for Pharmaceutical businesses to thrive.

FIND OUT MORE

Pharmaceutical Sector 0

HEALTHCARE & PHARMACEUTICAL INTEGRATION SPECIALISTS

Empowering health & pharmaceutical organisations across
Europe to become more efficient and enable new levels of care.


NOT HEARD OF US?

HEALTHCARE & PHARMACEUTICAL INTEGRATION SPECIALISTS 0
 
 
 
 
Reply ©​​ 2023​ - Company Information -
 PrivacyCookie Settings​
  • About Reply​​​
  • Inves​tors​​
  • Newsroom
  • Follow Reply on
  • ​
  • ​
​
  • ​About Solidsoft Reply
  • Privacy & Cookies Policy
  • Information (Client)
  • Information (Supplier)​