Tallan Blog

Tallan’s Experts Share Their Knowledge on Technology, Trends and Solutions to Business Challenges

Use BizTalk XML Polling to Pull SQL Server Generated XML

Introduction

BizTalk has long been one of the most complex and powerful integration platforms out there. With BizTalk, developers and business users alike have been able to create powerful inter-organizational integrations to automate complex business processes. While most BizTalk developers should be familiar with the basic features of the core BizTalk components such as schemas, maps, orchestrations, pipelines, and receive/send ports the same cannot be said for some of the more hidden and/or complex features. If used properly these features can greatly reduce the amount of development overhead for more complex integrations. One of these features is the XmlPolling option in the WCF-SQL receive configurations.

What is Xml Polling in BizTalk?

The XmlPolling feature is similar to the (Typed)Polling feature in BizTalk. Both options can be found in the configurations for the WCF-SQL adapter and both options can be used to pull data from a SQL Server database. Where the two differ is that the (Typed)Polling option will pull queried data from a collection of database tables whereas the XmlPolling option will pull queried data from a collection of database tables in XML format utilizing the “FOR XML” clause in SQL Server.

When and Why Would I Use Xml Polling?

For many common scenarios there may not be a need to use xml polling. But in certain situations, xml polling may be exactly what is needed in order to avoid adding unnecessary and complicated workarounds (or hacks). There are many different scenarios where one may need to use this kind of functionality. To name a few:

  • When there is a need to generate an XML file with a hierarchical structure from a set of SQL data
  • When there is a need to implement the Scatter Gather/Aggregator pattern where all items that need to be gathered are stored in a SQL Server database
  • When you need to implement the canonical pattern and must pull data from multiple database servers

How to Implement Xml Polling in BizTalk

The first thing you should do is generate the SQL stored procedures that will be used for the pollingStatement and polledDataAvailableStatement for a WCF-SQL receive location. The stored procedure that will be used for the pollingStatement should contain the “FOR XML”  SQL query that is responsible for generating the SQL XML.

Next, in your BizTalk project create a schema that correlates to the SQL XML that was created in the previous step. You can auto-generate the schema for the SQL XML by adding the “XML SCHEMA” clause to your “FOR XML” query. **Please note that in order to use the “XML SCHEMA” clause the “FOR XML” query cannot have a “PATH” clause.**

SQL_XSD

In your BizTalk project create the below schema. In the image below you should see that there is a root node called “SqlEnvelope” and it has a child an “Any” child element. The below schema will act as an envelope for the SQL XML and will help BizTalk determine the payload contents to import into an orchestration.SqlEnvelope

 

Next, create an orchestration and configure it to have a receive shape with a logical receive port. Set the receive message type to match the SQL XML schema that was created during the earlier step (please make note that this must be the SQL XML schema and not the SQL envelope schema).

RetrieveSQLXMLOrch

Once you have finished developing your orchestration/s build and deploy the BizTalk application.

After the deployment has been completed create a one-way receive port. For the receive port create a new receive WCF-Custom (or WCF-SQL) receive location and click the Configure button.

WCF_Custom

In the receive location General tab populate the URI in the following format: mssql://{database server name}//{database name}?InboundId={Unique Inbound ID}

WCF_Custom_General_Tab

In the Binding tab choose sqlBinding as the binding type. Then populate the following fields:

  • inboundOperationType: XmlPolling
  • PolledDataAvailableStatement: EXEC {Name of the stored procedure}
  • pollingStatement: EXEC {Name of the stored procedure that generates the SQL XML}
  • xmlSotredProcedureRootNodeName: SqlEnvelope
    • This should be the name of the root node of the Sql Envelope schema that was generated in a previous step. If you followed the steps exactly until this point then “SqlEnvelope” should be the name of the root node.
  • xmlStoredProcedureRootNodeNamespace: {The target namespace of the Sql Envelope schema that was created in one of the previous steps}

WCF_Custom_Binding_Tab

Lastly, configure the orchestration to have the logical receive port bound to the newly create receive port.

Bound_Orchestration

And that is it. With a few simple steps, you can easily configure your BizTalk application to import SQL generated XML from a SQL Server database.


Learn more about Tallan or see us in person at one of our many Events!

Share this post:

No comments

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

\\\