Recently, we faced the challenge to perform complex transformations in Logic Apps. We had an EDIFACT D96A ORDER parsed into XML, that had to be transformed into a generic JSON Order format. Let’s have a look what issues that we faced!
Liquid templates are insufficient
The first reflex was to go for Liquid templates, because the expected output format was JSON. Pretty soon, we realized that Liquid has too many limitations for our scenario:
- Input needs to be transformed into JSON first
- Unreadable syntax if your input XML has complex namespace structures
- Conditional select (e.g. Party with qualifier = BY) not possible without a for each
- No way to inject custom .NET code into Liquid templates
Let’s go with XSLT
As a second attempt, we decided to go for XSLT! We leveraged the Transform XML action, which executes an XSLT mapping. We transformed the EDI XML into the XML representation of the JSON Order, so we could use the built-in json() function to convert the output XML into its JSON format.
A simplified version of the XSLT can be found here. Remark that the output XML does not contain any namespaces and is already in the camelCasing that we expect for the resulting JSON Order:
<?xml version="1.0" encoding="utf-8"?> <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:s0="http://schemas.microsoft.com/BizTalk/EDI/EDIFACT/2006"> <xsl:output omit-xml-declaration="yes" method="xml" version="1.0" /> <xsl:template match="/"> <xsl:apply-templates select="/s0:EFACT_D96A_ORDERS_EAN008" /> </xsl:template> <xsl:template match="/s0:EFACT_D96A_ORDERS_EAN008"> <order> <reference> <xsl:value-of select="s0:BGM/BGM02" /> </reference> <customer> <xsl:value-of select="s0:NADLoop1[s0:NAD/NAD01='BY']/s0:NAD/s0:C080/C08001"/> </customer> <xsl:for-each select="s0:LINLoop1"> <orderLines> <productCode> <xsl:value-of select="s0:LIN/s0:C212/C21201"/> </productCode> <quantity> <xsl:value-of select="s0:QTY_3/s0:C186_3/C18602"/> </quantity> <price> <xsl:value-of select="s0:PRILoop1/s0:PRI/s0:C509/C50902"/> </price> </orderLines> </xsl:for-each> </order> </xsl:template> </xsl:stylesheet>
Issue with XML declaration
Eventhough we’ve set the omit-xml-declaration attribute to yes, the resulting XML still contained the XML declaration. Apparently this was a bug that already existed for a long time. Fortunately, the product team has solved this bug. In order to remain backwards compatible, they’ve added the “Apply XSLT output attributes” setting to activate the bugfix:
Issue with arrays
The above XSLT mapping, combined with the json() function, worked fine until we faced a transformation of an order that contains a single order line. When there’s only one child record, the json() function has no way to know that we’re dealing with an array. This resulted in invalid JSON Orders that have the orderLines property not defined as an array.
The observed behavior and a corresponding solution is described over here, in the Newtonsoft knowledge base. Assuming that Logic Apps uses Newtonsoft behind the scenes, we tried the solution:
- Declare the following namespace prefix: xmlns:json=”http://james.newtonking.com/projects/json”
- Add the following Array attribute to the orderLines XML record:
And guess what!? It worked like a charm!
As you could read, quite some pitfalls when executing complex transformations in Logic Apps. I hope this blog post will save you some headaches! Special thanks to my helpful colleagues Maxim, Iain and Pim for their assistance on this matter!