Publishing XML Content From SQL Server 2008 to BizTalk Server 2009

I’m looking at the XML capabilities of SQL Server a bit this week, and it reminded me to take another look at how the new BizTalk Server 2009 SQL Adapter (WCF-based) interacts with XML content stored in SQL Server.

I’ve shown in the past (in my book, and available as a free read here) that the new adapter can indeed read/write to SQL Server’s XML data type, but it does so in a bit of a neutered way.  That is, the XML content is stuffed into a string element instead of a structured node, or even an “any” node.  That said, I want to see how to take XML data from SQL Server and have it directly published to BizTalk for routing.

First things first, I need to create a table in SQL Server with an XML data type.  I wanted to “type” this column (just for the heck of it), so I built a valid XSD schema using the BizTalk Editor in Visual Studio.

I then opened the SQL Server 2008 Management Studio and defined a new XML Schema Collection.  The definition of the XML structure consists of the XSD schema we just created in Visual Studio.

Next, I created a new table and made one of the columns (“DetailsXml”) use the xml data type.  Then, I set the XML Type Specification’s “Schema Collection” property equal to our recently defined “OrderDetailsSchema” XML definition.

To test this configuration, I ran a quick SQL statement to make sure that an insert consisting of a schema-compliant XML fragment would successfully process.

Lookin’ good.  Now I have a row in that new table.  Ok, next, I went back to my BizTalk project in Visual Studio and walked through the Consume Adapter Service wizard to generate SQL adapter-compliant bits.  Specifically, in my “connection” I had to set the client credentials, InboundId (because we’re polling here), initial catalog, server, inbound operation type (typed polling), polled data available (“SELECT COUNT([OrderID]) FROM [BlogDemo]”) and polling statement (“SELECT [OrderID] ,[DetailsXml] FROM [BlogDemo]”).   Once those connection properties were set, I was able to connect to my local SQL Server 2008 instance.  I then switched to a “service” contract type (since we’re polling, not pushing) and picked the “typed polling” contract.

As with all the WCF adapters, you end up with XSD files and binding files after the Consume Adapter Service wizard completes.  My schema shows that the “DetailsXml” node is typed as a xsd:string.  So whether you “type” the XML column in SQL Server or not, the adapter will not ever give you a structured message schema.

After deploying the BizTalk project, and importing the wizard-generated binding into my BizTalk application, I have a valid receive location that can poll my database table.  I built a quick send port that subscribed on the receive port name.  What’s the output when I turn the receive location on?  Take a look:

We have the “typedpolling” root node, and our lovely XML content is slapped into a CDATA blob inside the string node.  That’s not very nice.  Now, I have two options as to what to do next: First, I could take this message, pull it into an orchestration and leech out the desired XML blob and republish it to the bus.  This is a decent option IF you also need other data points from the SQL Server message.  However, if ALL you want is the XML blob, then we want option #2.  Here, I muck with the generated receive location and tell it to pull out the XML node from the inbound message and only publish THAT to the bus.

I do this by going to the “Messages” tab of the adapter configuration and switching the source from “body” (which is the default) to “path” which let’s me set a forward-only Xpath statement.

Note that the encoding is string.  I wasn’t sure this would work right, but when I turned my receive location back on after making this update, this is the message my send port distributed:

Well hello my lady.  Nice to see you.  To go for the home run here, I switched the receive location’s pipeline to XmlReceive (to force message typing) and set the send port’s subscription to the BTS.MessageType.  I wanted to confirm that there were no other shenanigans going on, and that I was indeed getting a typed XML message going through, not a message of type “string.”  Sure enough, I can see from the context that I have a valid message type, and it came from my SQL adapter.

So, I’m glad this capability (extract and type the nested XML) is here, or else the BizTalk Server 2009 promise of “SQL Server XML data type compatibility” would have been a bit of a sham.   Has anyone tried accessing the data from an orchestration instead?  I’m assuming the orchestration xpath function could be used to get at the nested XML.  Feel free to share experiences.

Technorati Tags: ,

Author: Richard Seroter

Richard Seroter is Director of Developer Relations and Outbound Product Management at Google Cloud. He’s also an instructor at Pluralsight, a frequent public speaker, the author of multiple books on software design and development, and a former editor plus former 12-time Microsoft MVP for cloud. As Director of Developer Relations and Outbound Product Management, Richard leads an organization of Google Cloud developer advocates, engineers, platform builders, and outbound product managers that help customers find success in their cloud journey. Richard maintains a regularly updated blog on topics of architecture and solution design and can be found on Twitter as @rseroter.

19 thoughts

  1. Nice read Richard, as always!

    Must say i definitely prefer the WCF Message manipulation approach as opposed to using an orchestration. I agree the only time i would consider using the orchestration approach is when i needed the other metadata.

    Thanks again
    Ryan CrawCour

  2. One word – “Wicked”

    I was looking for this type of extract and came across this blog. Very clever and elegant, I was trying to avoid orchestration processing as I needed a way to call a receive pipeline from within the orchestration. This solution now allows me to directly call the pipeline from the Wcf-SQL receive port.

    Another tid-bit. I modified the XPath and added a wildcard search for the XML. I wanted to extract two types of root nodes with similar structures. It too worked as designed.

    Thanks again
    Nic Trajkovic

  3. How can i implement this in a wcf-sql request response scenario.In my scenario i send a request by passing an application number to the storedprocedure and get a response from sql with the requestedxml.My requestxml is coming to biztalk enclosed in a CDATA section.I need exactly what you mentioned in the above post. The request xml should be publisthed to message box where it will be subscribed by a separate MSMQ send port.I followed the above approach by giving bodyxpath of the request xml and encoding to string.In my tracked message events if i see the orchestartion debugger it is stopped at the receive shape that is supposed to receive the response from the sql.i see this exception Exception of type ‘Microsoft.BizTalk.Message.Interop.BTSException’ was thrown

  4. Richard,

    Thanks for a great post. Even thought it’s two years old there are obviously many people still reading this and using it. That is the sign of a great piece of information.

    I followed along in this and I am still not getting the correct data out of it.

    When I get to the “half way point” where I export the full message to a file drop I get what I was expecting. Which is a XML Document with all of the content I really wanted dropped into a XML Element as a large text string.

    This is from a stored procedure on a SQL Server 2008 R2 server where it builds the XML using a For XML Explicit statement on the fly. (No table to insert it into.)

    I followed your tutorial here all the way to here

    “I do this by going to the “Messages” tab of the adapter configuration and switching the source from “body” (which is the default) to “path” which let’s me set a forward-only Xpath statement.”

    I guess I have no idea what a “Forward-only XPath” should be when trying to get data from a single node. I tried to Google it but ended up with more questions that I could not answer. I am currently working on my second week of learning BizTalk and could really use a good pointer right about now.

    Thanks for all your postings and keeping up with this Technology.


    1. Well, it looks like WordPress stripped out my XML content. This is what I get in the file when it try it before setting the path information.

      Order OrderNumber=”1000019571″
      LineItem ItemNumber=”a3900NOTCQ” Quantity=”1″ UnitMeasure=”EACH”/
      LineItem ItemNumber=”a3900NOYS1″ Quantity=”1″ UnitMeasure=”EACH”/
      Order OrderNumber=”1000019572″
      LineItem ItemNumber=”a3900NQ68P” Quantity=”1″ UnitMeasure=”EACH”/
      LineItem ItemNumber=”a3900NQ9R0″ Quantity=”1″ UnitMeasure=”EACH”/

      1. Hi Tim. Apologies for the confusing wording about the XPath. By “forward only XPath statement” I mean that you can only add XPath statements which extract a given set of nodes and doesn’t require navigating backwards up the XML tree. Just something that goes straight through the document and extracts what is needed on a single pass.

        Hopefully in your case, you could go to the XSD Editor, snag the XPath statement (in the Properties window) for the given node (XMLOutput?) and plug that into the adapter Messages tab.

  5. Hi Richard, another excellent and straightforward article. However, if I use an XPath expression to extract the relevant part of the message body into BizTalk, the debatching that I set up ( no longer works and I only receive the first message in the batch into BizTalk.

    Is there a way these two methods can work together, or do I need to do the body extracting in the orchestration rather than in the receive adapter?

    1. Good question. Not sure if you can “double xpath” in the receive pipeline and you might be better off debatching the messages in the pipeline and using an orchestration (or custom pipeline component) to extract the specific fields.

      1. Thanks for quick repsonse. I tried with Body path expression as …/text() its not returning xml content from CDATA. you mentioned in the blog that with xpath body expression you were able to retried CDATA conent. I would like to know how you were able to extract content from CDATA node.

  6. Hi Richard, can you please provide me a sample to get xml content from CDATA node from WCF receive location

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.