Skip to main content

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.

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.
Blog27999 said…
This comment has been removed by a blog administrator.
Mia Grey said…
Cheers! Everybody. I am a developer; I am always anticipating making people aware of web development tools. I have come across a number of web development sites. However, your website is exceptional than the others. The content, tools and easiness of using your website is just amazing. I can be helpful by introducing you to range of Json tools. Here is the link to my site jsononline

Popular posts from this blog

AX - How to use Map and MapEnumerator

Similar to Set class, Map class allows you to associate one value (the key) with another value. Both the key and value can be any valid X++ type, including objects. The types of the key and the value are specified in the declaration of the map. The way in which maps are implemented means that access to the values is very fast.
Below is a sample code that sets and retrieves values from a map.
static void checkItemNameAliasDuplicate(Args _args) { inventTable inventTable; Map map; MapEnumerator mapEnumerator; NameAlias nameAlias; int counter = 0; ; map = new Map(Types::String, Types::Integer); //store into map while select inventTable { nameAlias = inventTable.NameAlias; if (!map.exists(nameAlias)) { map.insert(nameAlias, 1); } else { map.insert(nameAlias, map.lookup(nameAlias) + 1); } } //retrieve from…

Azure DevOps Release Pipeline–Walkthrough

It is a great start to 2019. Joris from Microsoft has welcomed the year with the release of the Azure DevOps Release Pipeline task on the marketplace. Official blog:https://community.dynamics.com/365/financeandoperations/b/newdynamicsax/archive/2019/01/18/first-azure-devops-task-released Addition blog:https://daxmusings.codecrib.com/2019/01/azure-devops-release-pipeline.html I thought I would do a walkthrough for those that haven’t had a chance to play with it yet.
New release pipeline In Azure DevOps, click on the New release pipeline.
You will get an option to select from a template. Just select “Empty Job”. In the first stage, make sure the Agent job is using “Hosted VS 2017”.

In the Agent job click on the + icon to add a task. Select the LCS Asset Upload task. If you don’t see, then you have not installed it. Just select the “Dynamics 365 Unified Operations Tools” link at the bottom. Otherwise, install from here https://marketplace.visualstudio.com/items?itemName=Dyn365FinOps.dynamics36…

Detailed guide on creating Business Events with Azure Service Bus

I have been working with the new Business Events feature released in FinOps and you should read the docs site first.
This blog post focuses primarily on setting up Azure Service Bus endpoint. Setting up the Azure services can be tricky if you are not familiar with Azure Key Vault and application registrations.
I have sequenced the post so that you don't have to jump around. There are four key elements to this: Create the app registrationCreate the service busCreate the key vault secretConfigure FinOps Create an App Registration In the Azure Portal, navigate to the Azure Active Directory menu. Click on App registrations (there is the old one and the preview menu - they are the same but the UI is a bit different). I will show the original App registrations way.


Create a new Web app/API registration and give it a name. It doesn’t really matter in our case what the sign-on url is.




Take note of the Application ID as you will need it later for setting up the Business Event.

Under the Ke…