Monthly Archives: October 2012

Search with criteria for value in different XML node in an XSLT Template

There have been a few instances where I have had to filter the incoming XML to look for a value from a different node in the XML. For the example in this article I had the parent node id but I wanted to know the name of that parent node.

This excellent article gave me a starting point for the principle of searching for values in different nodes using an XSLT template. This worked spendedly in one map I was working where I was trying to sum up all values in the current list of node items.

<xsl:template name="OutputSum">
  <xsl:param name="param1" />
  <xsl:element name="TransactionAmount">
    <xsl:variable name="var:vTranAmt"
    select="sum(//ExpenseItem[ReportEntryID=$param1]/TransactionAdjustmentAmount" />

    <xsl:value-of select="$var:vTranAmt" />


However, when I tried to use the same principle to select the name value of a parent node for which I only knew the ID, it just wouldn’t work. I kept gettiing blank values no matter what I tried.

The source schema was from the MS Dynamics CRM v4 web services using the RetrieveMultiple method and is much more complex than one I normally use. After a lot of headscratching I noticed that it was the namespaces that were causing the problems. When I right clicked the map, selected validate and viewed the .xsl that Visual Studio was generating I noticed in my other mappings that BizTalk was adding some bizarre namespaces to the nodes, e.g. s6:RetrieveMultipleResult/s4:BusinessEntities . Eventually I got it to work using these namespaces.

<xsl:template name="ParentCompanyCode">
  <xsl:param name="param1" />
  <xsl:element name="Code">

                <xsl:value-of select="//s4:BusinessEntity[s6:new_geographyid=$param1]/s6:new_name" />


Unfortunately I can’t explain exactly why this was required, but my advise is that if you can’t work out why custom xsl isn’t working then create something similar using functoids and have a look through the xsl that it generates via the Validate map functionality.

Flat File Assembler Encoding and Charset

I’ve had lots of problems recently with file encodings including foreign and special characters not appearing properly in target flat files. For example ø being written as ? and even worse an accented é translated as 2 characters é which meant that the column aligned file got messed up.

The reason is that the source system provides the data in UTF-8 (which is the best encoding because it should in theory deal with all character sets) but the files were being created as ANSI which is far more limited and unless you specify the charset will cause the issues I have described. However the target system I am creating files for is a little archaic and does actually require files with an ANSI charset.

BizTalk can handle this issue quite well out the box but you have to specify the charset in the flatfile component otherwise it will take a guess which will in all likelyhood be wrong. I have also found that you have to specify this at design time in visual studio otherwise the change will not be effective. So although you have the option of specifying the charset in the send port properties, this does not work properly – must be a bug in BizTalk.

In visual studio open up your pipelene and select the Flat file assembler component.

Then select the file encoding which matches what your target system expects. This might be UTF-8 but may also be ANSI in which case you should select the charset that matches e.g. Western-European (1252).

You should use a similar process when reading files using the flat file disassembler component. And also be careful when using external tools such as an otherwise excellent PGP pipeline component. I had an issue where the pipeline component was writing a file to a temporary location but did’t take the encoding into account and the encoding was lost. Here is the code I used to change the EncryptStream method int he component source code.

        /// <summary>
        /// Encrypts the stream.
        /// </summary>
        /// <param name="inStream">The in stream.</param>
        /// <param name="pubKeyFile">The pub key file.</param>
        /// <param name="sourceFilename">The source filename.</param>
        /// <param name="extension">The extension.</param>
        /// <param name="armorFlag">if set to <c>true</c> [armor flag].</param>
        /// <param name="integrityCheckFlag">if set to <c>true</c> [integrity check flag].</param>
        /// <param name="strCharset">The STR charset.</param>
        /// <param name="targetCharset">The target charset.</param>
        /// <returns></returns>
        public static Stream EncryptStream(Stream inStream, string pubKeyFile, string sourceFilename, string extension, bool armorFlag, bool integrityCheckFlag, String strCharset, String targetCharset)
            string tmpEncryptedFile = sourceFilename + "." + extension;

            // View debug lines with Debugview.exe
            //System.Diagnostics.Debug.WriteLine(strCharset + " is the charset");


                using (StreamWriter sourceStream = new StreamWriter(sourceFilename, true, Encoding.GetEncoding(targetCharset)))
                    using (StreamReader sr = new StreamReader(inStream, Encoding.GetEncoding(strCharset), true))

                using (FileStream encryptedStream = File.Create(tmpEncryptedFile))
                    PgpPublicKey publicKey = ReadPublicKey(File.OpenRead(pubKeyFile));
                    EncryptFile(encryptedStream, sourceFilename, publicKey, armorFlag, integrityCheckFlag);

                    encryptedStream.Seek(0, SeekOrigin.Begin);
                    int myCapacity = Int32.Parse(encryptedStream.Length.ToString());
                    Byte[] byteArray = new Byte[myCapacity];

                    MemoryStream memStream = new MemoryStream();

                    encryptedStream.Read(byteArray, 0, myCapacity);
                    memStream.Write(byteArray, 0, myCapacity);


                    memStream.Seek(0, SeekOrigin.Begin);
                    return memStream;
            catch (Exception ex)
                //System.Diagnostics.EventLog.WriteEntry("PGPWrapper[Encrypt] Error", ex.ToString());
                throw ex;
                // Clean-up temp files
                if (File.Exists(sourceFilename)) { File.Delete(sourceFilename); }
                if (File.Exists(tmpEncryptedFile)) { File.Delete(tmpEncryptedFile); }

                //FileInfo fileInfo = new FileInfo(sourceFilename);
                //System.Diagnostics.EventLog.WriteEntry("PGP_Debug", fileInfo.FullName + " was deleted");

                //fileInfo = new FileInfo(tmpEncryptedFile);
                //System.Diagnostics.EventLog.WriteEntry("PGP_Debug", fileInfo.FullName + " was deleted");