BizTalk Server: Teach me something new about Flat Files (or not) – Files Delimited by Symbols

by community-syndication | Oct 14, 2015 | BizTalk Community Blogs via Syndication

This post is intended to consolidate some basic principles about Flat File Schema Wizard First of all, BizTalk Flat File Schema Wizard is a template inside Visual Studio that allows you to converting Flat File into XML message, which BizTalk can understand, in a graphical a simple manner. That basically supports two types of text […]
Blog Post by: Sandro Pereira

Collection of Microsoft Integration Stencils for Visio 2013 updated #2 with new stencils

by community-syndication | Oct 14, 2015 | BizTalk Community Blogs via Syndication

After some requests from the community, I decided to update my Microsoft Integration Stencils for Visio 2013 stencils with an additional of 60 new shapes, making ate the moment a total of 361 shapes that will help you to visually represent Integration architectures and solutions diagrams in a simple and beautiful way. Some of the […]
Blog Post by: Sandro Pereira

BizTalk Server: Teach me something new about Flat Files (or not) – Introduction

by community-syndication | Oct 9, 2015 | BizTalk Community Blogs via Syndication

Following one of my last speaking engagement in the Porto.Data Community Meeting about Flat Files: How to process Flat Files documents (TXT, CSV ) in BizTalk Server where I address some of the following topics: How to process Flat Files documents (TXT, CSV ) in BizTalk Server. What types of flat files are supported? How […]
Blog Post by: Sandro Pereira

Sophie Eikelenboom | Junior Integration consultant

by community-syndication | Oct 6, 2015 | BizTalk Community Blogs via Syndication

Ik ben communicatief, analytisch, en probleemoplossend ingesteld
Blog Post by: AxonOlympus

Changing xsn prefix and grouping using XSLT with BizTalk

by community-syndication | Oct 6, 2015 | BizTalk Community Blogs via Syndication

I’m not an expert in XSLT, nor am I a fan of it. But although my feeling for XSLT (which I’m sure is mutual) are cold and hostile at best, I recognize it as sometimes the only solution to the problem. Recently, I’ve come across two scenarios where it was the only way to solve the problem, and since I spent way too much time on it, I thought it be a good idea to share it. If not to anyone else, at least to myself as I don’t want to do this again

The first scenario we had to send a file to a destination, and the consumer of the message requested that we’d use specific prefixes for the namespaces. BizTalk Server sets these namespaces for us starting from ns0, ns1ns*. There isn’t built in way for us to control this, and given the complexity of hieratical structures from different namespaces I can understand why.

However in my case, as I said, the consumer of the message required the namespace to be set to “tns”. My first approach was to create a pipeline component and use the XmlTranslatorStream which comes as part of the Microsoft.BizTalk.Streaming. The XmlTranslatorStream allows derived classes to intercept XML node translation through virtual methods such as TranslateStartElement.

    public class XmlNamespaceHandlerStream : XmlTranslatorStream
    {
        string _fromPrefix;
        string _xmlNamespace;
        string _toPrefix;

        protected override void TranslateStartElement(string prefix, string localName, string nsURI)
        {
            if (prefix == _fromPrefix && nsURI == _xmlNamespace) 
                base.TranslateStartElement(_toPrefix, localName, nsURI);
            else if (nsURI == _xmlNamespace) 
                base.TranslateStartElement(null, localName, null);
            else
                base.TranslateStartElement(prefix, localName, nsURI);
        }
        
        protected override void TranslateAttribute()
        {
            if (this.m_reader.Prefix != "xmlns")
                base.TranslateAttribute();
        }
        /// <summary>
        /// Intercepts the processing of the XML stream and changes prefixes from 
        /// a specific namespace to a new prefix.
        /// </summary>
        /// <param name="input">Inbound message stream. Eg inmsg.BodyPart.Data</param>
        /// <param name="fromPrefix">The prefix to be changed. Eg ns0</param>
        /// <param name="xmlNamespace">whatever namespace is associated with the fromPrefix</param>
        /// <param name="toPrefix">Name of the new prefix</param>
        public XmlNamespaceHandlerStream(Stream input, string fromPrefix, string xmlNamespace, string toPrefix)
            : base(new XmlTextReader(input), Encoding.Default)
        {
            this._fromPrefix = fromPrefix;
            this._toPrefix = toPrefix;
            this._xmlNamespace = xmlNamespace;
        }
    }

The TranslateStartElement is called for every XML element, and if the prefix (and namespace) is the same as the one I like to change, I proceed with changing the prefix. To process the message in a pipeline component, I simply use like this:

inmsg.BodyPart.Data = new XmlNamespaceHandlerStream(
    inmsg.BodyPart.GetOriginalDataStream(),  // Inbound stream
    this.FromPrefix,  // Property of pipeline component
    this.XmlNamespace,  // Property of pipeline component
    this.ToPrefix);  // Property of pipeline component

The good thing with this approach is that it’s done with a streaming pattern. However, the problem was that it moves all namespace declarations except the one I want to change from the root element to each element using it. This might not be a problem if the message is small, but in my case the message contained 100.000+ person elements. The inbound flat file format was about 20Mb and the original output using ns0 as prefix was close to 80Mb. After I changed the namespace prefix using the XmlTranslatorStream it was more than 140Mb. The additional namespace declarations added approximately 75%.

So although my intensions were good, I was forced to fall back to sworn enemy, mr XSLT:

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var"
                exclude-result-prefixes="msxsl var s0 userCSharp" version="1.0"
                xmlns:cmn="http://somenamespace/CommonInformationInt"
                xmlns:s0="http://schemas.microsoft.com/BizTalk/2003/aggschema"
                xmlns:ns0="http://somenamespace/Person"
                xmlns:tns="http://somenamespace/Person"
                xmlns:fi="http://somenamespace/FileInfo"
                xmlns:ci="http://somenamespace/CustomerInfo"
                xmlns:userCSharp="http://schemas.microsoft.com/BizTalk/2003/userCSharp">

  <xsl:output method="xml" indent="yes"/>

  <xsl:template match="ns0:PersonInfo">
    <tns:PersonInfo
        xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
        xmlns:xsd="http://www.w3.org/2001/XMLSchema">
      <xsl:apply-templates select="@*|node()"/>
    </tns:PersonInfo>
  </xsl:template>

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

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

  <xsl:template match="@ns0:*">
    <xsl:attribute name="tns:{local-name()}">
      <xsl:value-of select="."/>
    </xsl:attribute>
  </xsl:template>
  <xsl:template match="node()">
    <xsl:copy>
      <xsl:apply-templates select="@* | node()"/>
    </xsl:copy>
  </xsl:template>
  <xsl:template match="@*">
    <xsl:copy/>
  </xsl:template>
</xsl:stylesheet>

I then used a pipeline component for do the transformation using the XslCompiledTransform class:

// Using a VirtualStream to limit memory resources from being used.
var outStream = new VirtualStream(VirtualStream.MemoryFlag.AutoOverFlowToDisk);

XmlTextReader xmlTextReader = new XmlTextReader(inmsg.BodyPart.Data);
XslCompiledTransform xsl = new XslCompiledTransform(false);

MemoryStream stream = new MemoryStream(Encoding.Unicode.GetBytes(Resources.[YOUR EMBEDDED XSLT DOC]));

XmlTextReader xsltTextReader = new XmlTextReader(stream);
XsltSettings settings = new XsltSettings(true, true);
xsl.Load(xsltTextReader, settings, new XmlUrlResolver());

xsl.Transform(xmlTextReader, new XsltArgumentList(), outStream);

outStream.Position = 0;
inmsg.BodyPart.Data = outStream;

inmsg.BodyPart.Charset = "utf-8";
return inmsg;

The second scenario was about sending information to a destination where the receiver of the message could not handle the entire message at once. So we had to split the message in chunks of 50.000 person records per message.

Again I turned to my sworn enemy. This time I created two templates with identical match attribute setting and a template mode attribute (“group” or “person”) depending on depending on if I was going to create a new group element or add the person element to the existing group.

I used the position() XPath function to determine the current count of Person elements, and a modular expression to determine if a group should be created.

<?xml version="1.0" encoding="utf-16"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
                xmlns:msxsl="urn:schemas-microsoft-com:xslt"
                xmlns:var="http://schemas.microsoft.com/BizTalk/2003/var"
                exclude-result-prefixes="msxsl var s0" version="1.0"
                xmlns:ns0="http://schemas.microsoft.com/Sql/2008/05/Types/Views/dbo"
                xmlns:ns1="http://p.PersonInserts"
                xmlns:s0="http://schemas.microsoft.com/Sql/2008/05/ViewOp/dbo/Person">

  <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" />

  <xsl:template match="/">
    <ns1:PersonInserts>
      <xsl:apply-templates select="/s0:SelectResponse" />
    </ns1:PersonInserts>
  </xsl:template>

  <xsl:template match="/s0:SelectResponse">
    <xsl:apply-templates select="//ns0:Person[position() mod 50000 = 1]" mode="group" />
  </xsl:template>

  <xsl:template match="//ns0:Person" mode="person">
    <ns0:Person>
      <ns0:customId>
        <xsl:value-of select="./ns0:customId"/>
      </ns0:customId>
      <ns0:Identifier>
        <xsl:value-of select="./ns0:Identifier"/>
      </ns0:Identifier>
      <ns0:firstName>
        <xsl:value-of select="./ns0:firstName"/>
      </ns0:firstName>
      <ns0:lastName>
        <xsl:value-of select="./ns0:lastName"/>
      </ns0:lastName>
    </ns0:Person>
  </xsl:template>

  <xsl:template match="//ns0:Person" mode="group">
    <Group>
      <xsl:apply-templates select=". | following-sibling::ns0:Person[position() &lt; 50000]" mode="person"/>
    </Group>
  </xsl:template>
</xsl:stylesheet>

HTH

Mikael

Blog Post by: wmmihaa