Reading Data from XML Strings in BizTalk Server


Recently I was working with a customer and giving a quick overview of how to retrieve data from SQL Server within BizTalk.  We had a stored procedure that would return the first "unfetched" row, lock the record, set the "fetchstatus" and return the data "for xml auto, elements".  Simple enough, right?

Turns out, the data we cared about in SQL was already stored as a string of XML.  My theory is that since the .NET (non-BizTalk) developer knew the data was going into BizTalk, someone said "hey, let's put the data in as XML since BizTalk uses XML."  Again, just my theory.  While this may sound nice, it actually makes our job a bit more complicated, because when you serialize XML to XML again, SQL will escape the XML string so it won't "break" the XML schema.

For example, the string <element> becomes &gt;element&lt;.

I think the real solution to this problem would be to re-evaluate the architectural decision to put the data into a column as an XML string, and instead alter the table to have the columns of data we care about, but we had less than a day to get an internal demo going and it would take too long to change this.

So, what do we do to make this work "for now?"

First, retrieve the data just like we would normally.  Within our orchestration we now have our SQL message that looks something like this (generated via "Add Generated Items" in our BizTalk project):

  • SqlResponse
    • MyFetchTable
      • rowid
      • MyString

...where MyString is the XML data we care about.  Good 'nuff.  But since we know this string of XML is actually a message we'll want to use, we'll also need to manually create a schema to match what will be coming out of SQL (so let's hope this data is consistent).  In this example, let's say our XmlString looks like this:

<XmlTest><Field1><Field2></XmlTest>

...or rather...

&lt;XmlTest&gt;&lt;Field1&gt;&lt;Field2&gt;&lt;/XmlTest&gt;

Then we need to create a schema to match:

  • XmlTest
    • Field1
    • Field2

So what now?  When we receive this message (let's call it msgSQLData) into our orchestration, we'll extract our XML string and store it in a string variable (strXml):

strXml = xpath(msgSqlEvent, "string(/*[local-name()='SqlResponse' and namespace-uri()='http://Romp.Demo.SqlXmlStrings.BizTalk.Schemas']/*[local-name()='MyFetchTable' and namespace-uri()='http://Romp.Demo.SqlXmlStrings.BizTalk.Schemas'][1]/*[local-name()='MyString' and namespace-uri()='http://Romp.Demo.SqlXmlStrings.BizTalk.Schemas'][1])");

Please also note that in this xpath statement, we use the xpath string() function.

Now we have a string of encoded XML data in our orchestration.  Now what?  We need to add our XmlTest message (above) to the orchestration (let's call it msgXmlTest).  In a message assignment shape (inside of a construct block for msgXmlTest, of course), I'm going to call a helper function that will need to do two things:

  • Convert our escape characters to valid XML characters; and
  • Insert a namespace.

It will return this as an XmlDocument type, which BizTalk converts to an XLANGMessage type on the fly inside of the orchestration, so we don't need to worry about it.

msgXmlTest = Romp.Demo.SqlXmlStrings.Helper.XmlStrings.GetMessageFromXmlString(strXml, "http://Romp.Demo.SqlXmlStrings.BizTalk.Schemas");

Here's the code for our helper class:

public static XmlDocument GetMessageFromXmlString(string XmlString, string Namespace)
{
    XmlDocument doc = new XmlDocument();
    XmlString = DecodeXmlString(XmlString);
    XmlString = InsertNamespaceString(XmlString, Namespace);
    doc.LoadXml(XmlString);
    return doc;
}

private static string DecodeXmlString(string XmlString)
{
    XmlString = XmlString.Replace("&lt;", "<");
    XmlString = XmlString.Replace("&gt;", ">");
    XmlString = XmlString.Replace("&quot;", "\"");
    XmlString = XmlString.Replace("&apos;", "\'");
    XmlString = XmlString.Replace("&amp;", "&");

    return XmlString;
}

private static string InsertNamespaceString(string XmlString, string Namespace)
{
    return InsertNamespaceString(XmlString, Namespace, String.Empty);
}

private static string InsertNamespaceString(string XmlString, string Namespace, string NSPrefix)
{
    int iLoc = XmlString.IndexOf(">");
    string strNamespace = String.Empty;

    if (NSPrefix.Trim() == String.Empty)
        strNamespace = String.Format(" xmlns=\"{0}\"", Namespace);
    else
        strNamespace = String.Format(" xmlns:{0}=\"{1}\"", NSPrefix, Namespace);

    XmlString = XmlString.Insert(iLoc, strNamespace);

    return XmlString;
}

Here's a simplified example of what this would look like in an orchestration:

BizTalk Orchestration

We now have a message containing the data from the string of XML we got from the SQL database.  From this point whatever you want to do with that message is up to you.

 

In summary, we're going from this:

SQL Data

To this:

SQL Response

Since this is viewed in Internet Explorer, the &lt;XmlTest&gt; is converted to <XmlTest> for display. Here's the source:

SQL Response Text

And finally, to this:

Parsed XML Message

 

If we wanted to do this same thing without orchestration (a good idea unless you need to do something requiring orchestration), we would use a custom pipeline component to convert the string of XML the same way, and put the "new" message on the wire for submission to the MessageBox.

 

Sample code:

 

Technorati tags:
Comments (7)
  1. Erik says:

    Hi,

    There is an alternative to manipulating the XML text where you call XmlStrings.GetMessageFromXmlString.

    You could create a new type (class) and add a method to it to get the information you want from the incoming string and assign that to a public property. Mark the new class Serilizable and add a declaration to change its namespace, and BizTalk will serialize the object out to a message. You can even serialize the message back out to the XmlDocument type so that you don’t have to change your port configuration – just assign your new type to the instance of the XmlDocument.

    Although more involved at first glance, this approach hides the details of acquiring the data you want and lets the Framework generate the correct XML (including the namespace). When you want to change the message’s namespace, or any other aspect, just edit your new type and BizTalk will take care of the rest.

  2. Chris Romp says:

    Hi Erik,

    That’s the approach I would take if I was getting a message from code any other way; however, in this approach I didn’t want to marry the schema into the class, but rather have something that would be re-usable.  Of course to make my version of this reusable I’d have to have a couple of different overloaded methods to the GetMessageFromXmlString method to account for wanting namespace or not, etc.

    Good idea, regardless!

    Chris

  3. Jay says:

    Chris,

    You are almost there with your quasi quote…

    "hey, let’s put the data in as XML since BizTalk uses XML."

    🙂

    Who would have thought that BizTalk, a system built to handle XML, would choke on such a simple task.  If you haven’t noticed, Microsoft has integrated more and more with XML over the past few years.  Including SQL Server 2005’s XML data type and XPATH abilities, to SSRS (Reporting Services) which is primarily a reporting system built on XML.

    Either way, not a bad post.  However, if you could include best practices and other customer implementations of the "architectural decisions" and performance tips that would be much appreciated!  There is little out there besides a basic white paper that I found published by Microsoft.

    Thanks!

  4. Chris Romp says:

    Is it choking?  They’re passing a string (which happens to be XML, but as far as .NET or SQL are concerned SQL is returning a string field), embedded in XML, so of course it should encode it! =)

    The correct way to have done this is to have SQL Server return its data encoded as XML, not to store the XML as a string in SQL.  By doing it that way, they’re giving up flexibility and power in both SQL Server and BizTalk Server.

    Chris

  5. Chris Romp says:

    And not to skip the best practices, it’s an easy find.  They needed to return the data using the SQL Server “for xml auto” or “for xml auto, elements” attribute.  Here’s a good write-up complete with sample code:

    http://msdn.microsoft.com/en-us/library/ms935658.aspx

  6. jay_p says:

    Chris,

    Thank you for the microsoft document reference.  

    However, it seems a bit out of date (May 2005, before SQL Server 2005’s release) and doesn’t include SQL Server 2005’s improved functionality.  

    For example, it might be beneficial to use the OUTPUT clause in the stored procedure (SubScriptionProcedure) that Microsoft’s documentation uses for the SQL (Receive) Adapter example.  Instead, it performs an update and then goes back to the table to select the data.  Incorporating the OUTPUT functionality would simplify and improve the execution.

    It might also not be a great idea to increase the activity like this on transaction-heavy OLTP systems where locking could be a problem.  Do you have any recommendations for this type of event tracking?  Real-world solutions that you have used/seen is much appreciated!

    Thanks!

    Jay

  7. Chris Romp says:

    You’re right that it’s a little dated; however the functionality of the SQL adapter hasn’t changed (much) since its release in BizTalk 2004.

    There’s a new BizTalk SQL adapter coming out with the release of the BizTalk Adapter Pack 2.0, scheduled to release in early 2009 (currently in TAP on Microsoft Connect).  That one will hopefully take advantage of some of the newer XML-related features in SQL 2005 and later.

    Chris

Comments are closed.

Skip to main content