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, ' ')" />
<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"), " ")' />
</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
(
[parameter(Mandatory=$true)][alias("x")][string]$XmlFile,
[parameter(Mandatory=$true)][alias("t")][string]$XslFile,
[parameter(Mandatory=$true)][alias("o")][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
Under the terms of this license, you are authorized to share and redistribute the content across various mediums, subject to adherence to the specified conditions: you must provide proper attribution to Stoneridge as the original creator in a manner that does not imply their endorsement of your use, the material is to be utilized solely for non-commercial purposes, and alterations, modifications, or derivative works based on the original material are strictly prohibited.
Responsibility rests with the licensee to ensure that their use of the material does not violate any other rights.