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