Creating Text Output with XSLT for Dynamics AX and D365 for Finance and Operations Developers

By Karl Gunderson | February 18, 2019

Creating text output with xslt for dynamics ax and d365 for finance and operations developers

When creating text output with XSLT for Dynamics AX or Dynamics 365 Finance and Operations, developers have often had to deal with these questions:

  • How do you create text output from an XML file?
  • How do you create fixed-width text output from an XML file?
  • Why do you want to create text output from an XML file?

One example for a Dynamics AX or Dynamics 365 Finance and Operations Developer is creating output for a bank for the positive pay feature.

In positive pay, a file is created with a list of checks.  The file is sent to a bank so that the bank only pays checks listed in the file.  In other words, the bank only pays checks that can be positively identified.  This is to prevent fraudulent check cashing.  The format of the file can vary from bank to bank.  AX can readily export an XML file but it’s not likely that XML file will work with any bank.  The file needs to be “transformed” into the bank’s format: typically a different XML format, a comma delimited text format or a fixed width text format.  The perfect tool for performing these transformations is an XSLT (Extensible Stylesheet Language Transformations) program.

The Solution

The example provided on the positive pay web page is large and can be confusing. There is a lot to learn.  You need to know XML, XSL, XPATH and the bank’s format.  Just knowing the technologies is one thing but then you actually have to modify or write the XSL and test it.

If you look at XML, you may already know that it basically consists of elements and attributes.  XPath has its complications, but the basics are simple enough.  XPath is a language for querying XML.  Since XML is a nested structure of elements, much like a file system, the basic cases for XPath are pretty simple.  XSL is a little more difficult. It’s similar to most programming languages with loops and conditional statements but a few other constructs for dealing with XML. The odd part is that an XSL program is formatted as XML.  This makes XSL more verbose than most programming languages and so harder to read, at least until you know what to look for.

By providing a simple example and explaining what it does, you will have a framework on which to build – a toehold!  Let’s examine the following files, provided later in this article:

  • Transform.ps1 – a PowerShell script for executing an XSL program.  The program will take input from an XML file and produce an output file.  In this case, the output will be a text file with mostly fixed width fields.
  • T.cmd – a batch file to execute Transform.ps1 with a specific input XML, XSL and output text file and then display the output.  This is just a convenience to make repeated execution, debugging, simpler.
  • Data1.xml – the example input file consisting of two bank accounts each with a list of checks.
  • Code1.xsl – the XSL program which will read the XML file and write the output file.
  • Out.txt – the output file from the XSL program.

To make the example work, put all the files in a directory.  From a command line window, execute T.cmd.

If PowerShell says you aren’t allowed to execute script code, open a PowerShell window as administrator and execute this command:

  • Set-ExecutionPolicy Unrestricted

Now you can modify the example code and try things out.  If you make a mistake, you’ll get an error message that will tell you where and why things didn’t work – for syntax errors anyway.  Logic errors aren’t identified. If you need assistance, please reach out to us at Stoneridge Software.

Explaining the code

Let’s examine the XSLT code line by line.  If you haven’t already, load Code1.xsl into your favorite text editor with line numbers.

Line 1: The typical XML declaration.  Remember XSL is just an application of XML!

Lines 2 to 5: The beginning of the XSL program.  xmlns identifies name spaces using a prefix.  The prefix is arbitrary but must be unique.  The most interesting prefix for this example is xslthelper.  It will be used to access some C# used to extend the XSL program.

Line 7: Tells XSL we are producing text.  Other options include XML and HTML.

Line 9: The first statement of the program.  A template is a block of XSL code.  This block will execute when it matches the XML input.  For this code, it must match “/accounts” which is XPath for root (“/”) element of the XML with element tag “accounts”.  Like a file path, this is an absolute path.  Note this matches the first line of the XML input file.  This program begins execution with the first line of the input data.  Since an XSL program is XML, line 9 is closed or “scoped” by the tag on line 22.

Line 10: A foreach statement that repeats for “account” elements that are direct children of /accounts.  The XPath “account” is a relative path, it didn’t start with a slash like the match path on line 9 did.  That’s because there is a new context, or in the file system analogy, a new current directory which is the /accounts directory.

Line 11: An “if” test where the test expression counts the number of check elements.  Because of line 10, our context is now /accounts/account so to count the number of check elements we need to look inside the check’s element. The “if” statement does not change the context.

Line 12: If there are any check elements, this statement outputs a line of text with a hard-coded string, the value from the name element and a carriage return and line feed to start a new line in the output all put together with the string concatenation function from XPath.

Line 13: Another foreach but this time repeated for each check element found within the checks element.

Line 14: Another concatenated string is output but this time there is a call to the C# code using the prefix defined on line 5.  Note that this prefix, xslthelper, is also specified on line 24.  This is how XSL knows where to find the C# function.  The XPath expression, ../../number, refers to the bank account number because the current context is /accounts/account/checks/check.  Just as with a file systems, you can refer to a parent directory by using “..” and a grandparent using “../..”.

Lines 15 to 18: More of the same, concatenated strings, calls to C# and XPath functions mixed together.

Lines 19 to 22: Typical XML closing tags.

Lines 24 to 41: A block of script code, using the C# language and identified by the prefix xslthelper.  Again, there is nothing special about the prefix except that it is unique within this XSL program.

Lines 42: End of the XSL program.

Code1.xsl

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0"
    xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
    xmlns:msxsl="urn:schemas-microsoft-com:xslt" 
    xmlns:xslthelper="http://schemas.microsoft.com/BizTalk/2003/xslthelper">
    
    <xsl:output method="text" />

    <xsl:template match="/accounts">
        <xsl:for-each select="account">
            <xsl:if test="count(checks/check) > 0">
                <xsl:value-of select="concat('Account: ', name, '&#13;&#10;')" />
                <xsl:for-each select="checks/check">
                    <xsl:value-of select='concat(xslthelper:PadLeft(../../number, 6, " "), " ")' />
                    <xsl:value-of select='concat(xslthelper:PadLeft(number, 10, "0"), " ")' />
                    <xsl:value-of select='concat(xslthelper:PadLeft(amount,12, "0"), " ")' />
                    <xsl:value-of select='concat(xslthelper:FormatDate("11/18/2019"), " ") '/>
                    <xsl:value-of select='concat(xslthelper:ConvertToInt("00032456"), "&#13;&#10;")' />
                </xsl:for-each>
            </xsl:if>
        </xsl:for-each>
    </xsl:template>

    <msxsl:script language="C#" implements-prefix="xslthelper">
<![CDATA[
    public string PadLeft(string value, int length, string paddingchar)
    {
        return value.PadLeft(length, paddingchar[0]);
    }

    public string FormatDate(string date)
    {
        return DateTime.Parse(date).ToString("MMddyyyy");
    }
 
    public int ConvertToInt(string s)
    {
        return int.Parse(s);
    }
]]>
    </msxsl:script>
</xsl:stylesheet>

 

Data1.xml

<accounts>
    <account>
        <number>123</number>
        <name>ABC</name>
        <checks>
            <check>
                <number>123-123</number>
                <amount>123.45</amount>
                <date>1/21/2019</date>
            </check>
            <check>
                <number>123-124</number>
                <amount>110.45</amount>
                <date>1/22/2019</date>
            </check>
            <check>
                <number>123-130</number>
                <amount>10.55</amount>
                <date>1/22/2019</date>
            </check>
        </checks>
    </account>
    <account>
        <number>125</number>
        <name>XYZ</name>
        <checks>
            <check>
                <number>125-201</number>
                <amount>1000.00</amount>
                <date>1/10/2019</date>
            </check>
            <check>
                <number>125-212</number>
                <amount>110.45</amount>
                <date>1/12/2019</date>
            </check>
            <check>
                <number>125-227</number>
                <amount>1123.66</amount>
                <date>1/13/2019</date>
            </check>
            <check>
                <number>125-236</number>
                <amount>2390.81</amount>
                <date>1/13/2019</date>
            </check>
        </checks>
    </account>
</accounts>

Transform.ps1

param
(
    [string]$XmlFile,
    [string]$XslFile,
    [string]$OutputFile
)

Write-Host "XML: $XmlFile"
Write-Host "XSL: $XslFile"
Write-Host "OUT: $OutputFile"

try
{
    $xml = New-Object System.Xml.XPath.XPathDocument($XmlFile);

    $xsltsettings = New-Object System.Xml.Xsl.XsltSettings;
    $xsltsettings.EnableScript = $true;

    $xslt = New-Object System.Xml.Xsl.XslCompiledTransform;
    $xslt.Load($XslFile, $xsltsettings, $null);

    $out = New-Object System.IO.StreamWriter($OutputFile);

    try 
    {
        $xslt.Transform($xml, $null, $out);
    }
    finally
    {
        $out.Close();
    }
}
catch
{
    Write-Host $_.Exception
}

T.cmd

@if exist Out.txt del Out.txt
@powershell -Command ./transform.ps1 Data1.xml Code1.xsl Out.txt
@if exist Out.txt type Out.txt

Out.txt – skip this, create your own!

Account: ABC
   123 000123-123 000000123.45 11182019 32456
   123 000123-124 000000110.45 11182019 32456
   123 000123-130 000000010.55 11182019 32456
Account: XYZ
   125 000125-201 000001000.00 11182019 32456
   125 000125-212 000000110.45 11182019 32456
   125 000125-227 000001123.66 11182019 32456
   125 000125-236 000002390.81 11182019 32456

Related Posts

Recommended Reading:

Manage U.S. Use Tax on Purchase Orders in Dynamics 365 Finance and Operations

  Managing sales tax requirements on your business purchase can be complicated, but Dynamics 365 Finance and Operations can help […]

Read the Article
5.19.22 Dynamics CRM

How to Write a Great Support Ticket in the Stoneridge Support Portal

Submitting a support ticket through the Stoneridge Support Portal is a quick and effective way to get assistance for any […]

Read the Article

Managing Your Business Through Uncertain Times Using Dynamics 365 Finance and Operations

  Dynamics 365 Finance and Operations (F&O) can help you make informed decisions on how to move your business forward. […]

Read the Article
5.13.22 Power Platform

Using Power BI Object Level Security

  The following article will demonstrate how to use Power BI Object Level Security to disable column data based on […]

Read the Article
5.12.22 Dynamics CRM

How to Use the Stoneridge Support Portal

Stoneridge Software’s support portal is an intuitive and useful function that makes it easy for you to access resources to […]

Read the Article
5.6.22 Dynamics GP

Dynamics GP Transaction Removal: Purchase Orders

  Are you having performance issues with Purchase Orders?  Do you find that there are old Purchase Orders on your […]

Read the Article
5.5.22 Dynamics GP

The Real Story about the Long-Term Future of Dynamics GP Support

I’ve seen a number of people put forward comment that Dynamics GP is going away and you have to get […]

Read the Article

New Features in Dynamics 365 Business Central 2022 Wave 1 Release – Financial Enhancements

The Dynamics 365 Businses Central 2022 Wave 1 Release has a lot of new and exciting features to help your […]

Read the Article
4.29.22 Dynamics GP

Dynamics GP Transaction Removals: Bank Reconciliation

  This is part 2 of a 3 part series on Dynamics GP Transaction Removals. These quick tips will hopefully […]

Read the Article

Start the Conversation

It’s our mission to help clients win. We’d love to talk to you about the right business solutions to help you achieve your goals.

Subscribe To Our Blog

Sign up to get periodic updates on the latest posts.

Thank you for subscribing!

X