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()='https://Romp.Demo.SqlXmlStrings.BizTalk.Schemas']/*[local-name()='MyFetchTable' and namespace-uri()='https://Romp.Demo.SqlXmlStrings.BizTalk.Schemas'][1]/*[local-name()='MyString' and namespace-uri()='https://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, "https://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:

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:

To this:

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

And finally, to this:

 

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: BizTalk

Comments

• Anonymous
  May 16, 2008
  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.

• Anonymous
  May 16, 2008
  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

• Anonymous
  August 13, 2008
  The comment has been removed

• Anonymous
  August 13, 2008
  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

• Anonymous
  August 13, 2008
  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

• Anonymous
  August 13, 2008
  The comment has been removed

• Anonymous
  August 13, 2008
  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