Following on from my previous posts this time I show how I use XML to JSON Liquid templates. In particular I give examples of converting a JSON value to decimal.
The problem
One of the BizTalk solutions that we are migrating to Azure converts XML to JSON. The input XML looks something like this;
In the first place, the poreference element is alphanumeric and is of string type. The itemsize and price elements are decimals and the trailing zeroes are important to our destination system. Thus the XSD schema looks like;
Deploying the XML schema to BizTalk Server and using the JSON encoder pipeline component we get something like.
See one of my previous blogs if you are not familiar with how the BizTalk JSON encoder works.
In contrast posting this to a logic app and using the json workflow conversion function yields this output.
Notably the price and item size formats are not correct. In fact both cases they are of type string and should be in decimal format. The rest of this blog focusses on how we persuade Azure to give us the same JSON.
First attempted solution – XML to JSON Liquid templates
The liquid transform action XML to JSON promises to allow you to specify the JSON format in a liquid template.
Firstly I had teach myself how to write a liquid template. There are extensions in Visual Studio code to help you. I chose Shopify Liquid preview and Liquid Languages. My simple liquid template is below.
{ “product”:
{
“poreference”: “{{content.product.poreference}}”,
“itemsize”: {{content.product.itemsize | round:1 }},
“name”: “{{content.product.name}}”,
“price”: {{content.product.price | round: 2 }},
}
}
Secondly importing this liquid template into an integration account as a map sets us up for the next step. Finally after adding the liquid transform action to the logic app we achieve this output.
Gratifyingly the decimals formatting is decimal and not string. Furthermore the poreference is still a string. How did the template do that? The template accesses the value of XML nodes with Content.<node>. Quoting the value ensures it becomes a string and not a number in the JSON.
Even so the JSON output is not correct because the trailing zeroes are now missing. Thus it seems that you cannot fix the number of decimal points. As you can see I tried to use the round filter. This only works if the number of decimals overflows; it does add trailing zeroes. Given these points I do not think Liquid is rich enough to format decimal numbers.
Second solution – Azure function
Secondly we called from the Logic App to a Azure function to convert the XML to JSON. The azure function serializes the XML to JSON using custom C# code. The code also uses a LINQ query to convert the string values to decimal. My inspiration for this code comes from here.
Unfortunately while it seems promising at first the message is application/text and not application JSON. Subsequent conversion to JSON yields the same output as the Liquid template.
Conclusion
As can be seen I am now stuck because I do not have a good way replicating the behaviour of the BizTalk JSON encoder pipeline component in Azure. In particular I find that Azure solution drops trailing zeroes form decimal values. The json workflow function convert all XML values to strings. Liquid templates and custom C# running in an Azure function can convert XML to JSON and retain decimal values but at the end of the day they still drop trailing zeroes.
In spite of this some people might say this is by design. In addition they might say that JSON was never meant to support XML semantics. On the other hand I have issues with this because I think XML to JSON transformation should have parity with what the JSON encoder pipeline can do now. Furthermore if I think this has to be addressed before BizTalk solutions using the JSON encoder can be migrated to Azure.
Finally it is also possible that I have missed something. If there is a clever person out there that can format decimals and fix the decimal points in Logic Apps then please let me know. In the meantime I am in awe of the developers that wrote the BizTalk JSON encoder pipeline component. I think this component does some seriously good stuff. I wish we had the same features in the Azure json work flow function, JSON parser action or Liquid templates.
Postscript 11/05
How does the JSON Encoder pipeline component convert XML to JSON and format decimals with trailing zeroes? Dissembling the Microsoft.BizTalk.Component.JsonEncoder from the Microsoft.Pipeline.Components.dll we see the following code snippet;
Surprisingly the JSONConvert.SerializeXMLNodeToStream method signature does not exist in the open source Newtonsoft library. Thus the Newtonsoft.dll (version 3.0.1.0) shipped with BizTalk 2016 is a Microsoft fork. Finally, dissembling this assembly you find code that interrogates the XML and then sets the correct type in the JSON from the XML schema. Thus I now know how the BizTalk JSON Encoder format decimals with trailing zeroes.
Finally after briefly examining the idea of using the same logic in an azure function I gave up because the Microsoft Newtonsoft implementation is way to clever for me to mimic. Thus I will have to wait for some else to help with a solution.
