Sorting subentities in a complex XML in BizTalk

Question

I get some data from a WebService, which looks like this

Building
     Address
     -> Office
         Name
         CreationDate
         -> Worker
              Name
              HiringDate 

Before doing further processing and transforms on this message, I need to sort the sub-nodes ("Office" and "Worker") so that all Offices are sorted by CreationDate, and then all Workers are sorted by HiringDate inside their Office.

So far the only solutions I've seen for sorting in BizTalk have been XSLT based. There are some samples that show how to sort on a simpler structure (http://www.biztalkgurus.com/newsletter/TheBizTalker-Volume-03.html) but those won't work as-is since my message has several levels.

If have no idea how to (if that is even possible) write an XSLT expression that will do this sorting while preserving the structure of the XML.

Is it possible to write such an XSLT expression? What would it look like?

Answer 1

You need a XSLT copy templates for all tags exclude Office and Worker, and sorting templates for that tags. eg Copy templates:

<xsl:template match="node()">
    <xsl:if test="name()">
        <xsl:element name="{name()}">
            <xsl:apply-templates select="@*|node()"/>
        </xsl:element>
    </xsl:if>
</xsl:template>

<xsl:template match="text()">
    <xsl:value-of select="."/>
</xsl:template>

<xsl:template match="@*">
    <xsl:copy>
        <xsl:value-of select="."/>
    </xsl:copy>
</xsl:template>

And sorting templates:

<xsl:match="office_parent_tag_as_i_understand_Building">
    <xsl:apply-templates select="Office">
        <xsl:sort select="CreationDate" data-type="text" order="ascending"/>
    </xsl:foreach>
</xsl:template>

& analog for Worker tag

<xsl:match="worker_parent_tag_as_i_understand_Office">
    <xsl:apply-templates select="Worker">
        <xsl:sort select="HiringDate" data-type="text" order="ascending"/>
    </xsl:foreach>
</xsl:template>

Don't forget that xsl:sort works only with numeric and text data, for sorting by date you need to convert date to int or string

Answer 2

XSLT is one option, however if you want more flexibility processing you could try using a BizTalk Script Adapter like the one at http://www.powershellinside.com/powershell/biztalk/. That adapter allows you to use PowerShell script to format the message data and provides flexibility to make changes dynamically.