Tuesday, 2 July 2019

Data Management Export - XML to JSON Transformation

In my last post, I wrote about event based integration using Business Events. I used JSON as my export file type. JSON is a lot easier to work with in Microsoft Flow or Azure LogicApp.
Below is how I achieved it.

Data Management framework doesn’t do JSON by default. However, it does do XML file format.
A bit of googling and trial and error. I found this XSLT code that transformed XML to JSON.
https://gist.github.com/bojanbjelic/1632534
Here is the authors blog post to give credit.
https://www.bjelic.net/2012/08/01/coding/convert-xml-to-json-using-xslt/#code

Setup

Under the Data management workspace, open the Source data format form. Create new record called JSON and set the default extension to json.
  • File format = XML
  • XML Style = Attribute
  • Root element = Document (I left this as default)


Create a new Export and select your entity. In the Source data format, select JSON record that was created in the previous step.

Now click on the View map icon.


In the mapping form, click on the Transformations tab. Then upload the xslt file you downloaded from the github.


That's it for the set up. Now to run it.

Process

Normally when you export and XML file. It looks like this:

Once the transformation is applied, you get a JSON file like this.


This is great because I like to use Microsoft Flow or LogicApps to read the files to process. With XML you have to use XPATH or figure out otherwise to read it.

This works very nicely and no development or extra transformation at the target.

4 comments:

Mötz Jensen said...

Actually you could also do the convertion directly in the LogicApp / Flow.

Reading this blog post shows how easier that actually is:
https://blogs.msdn.microsoft.com/mioteg/2017/07/06/converting-xml-to-json-with-azure-logic-apps/

Try this in a LogicApp:

json(xml('test'))

Mötz Jensen said...

I can see my code example was parsed wrongly.

Here is the original code on pastebin:

https://pastebin.com/F8J12ssv

Munib Ahmed said...

Thanks. I did not know that.

MS Dynamics said...

Informative blog. Thanks for sharing.
MS Dynamics Operations Trade & Logistics Online Training