I am in a position where I am required to convert CSV files following the same template into XML files. I have a restriction that I only have Data Factory or Logic App as the available routes to achieve this. After doing some research, it seems that Data Factory still does not support XML Sinks so it seems I am left with Logic App as my only course of action. I was looking at this post for a way to do it but it seems a bit dated and uses BizTalk which I do not have available. I have gotten the premise that I basically need two XSD's, one for the CSV and one for the XML, plus a map. At this point I have hit a roadblock. Below is what I have so far: CSV Example The XSD for the XML Output (note, a lot of the references do not need to be filled in by the CSV) Any help on this would be massively appreciated, especially on how to create the schema for the CSV file and the Map, those are what I believe to be the biggest blockers so far.

Hello @Marc Towler - Welcome to MS Q&A, and thanks for posting here! For CSV to XML conversion, I’ve come across the following blog post which shows each required steps: http://blogs.recneps.net/post/Processing-a-flat-file-with-Azure-Logic-Apps As you’ll find in the post, you’ll need Azure resources such as Function app, Integration Account, and Logic Apps to accomplish the final solution. If you encounter a problem with any of the steps, let us know here and we’d be happy to help! UPDATE: Some steps in the blog post referenced above are a bit out of date, and also, @Marc Towler needed the solution to work without additional resources such as Azure Functions and Integration account. As such, the following sample application was created (instruction on the readme file) for demonstration purposes: https://github.com/mike-urnun-msft/csvtoxml Screenshot of the workflow:

Convert CSV to XML with Logic App

Accepted answer

Mike Urnun 9,761 Reputation points Microsoft Employee

2022-09-29T07:28:25.56+00:00

Hello @Marc Towler - Welcome to MS Q&A, and thanks for posting here!

For CSV to XML conversion, I’ve come across the following blog post which shows each required steps: http://blogs.recneps.net/post/Processing-a-flat-file-with-Azure-Logic-Apps

As you’ll find in the post, you’ll need Azure resources such as Function app, Integration Account, and Logic Apps to accomplish the final solution. If you encounter a problem with any of the steps, let us know here and we’d be happy to help!

UPDATE:
Some steps in the blog post referenced above are a bit out of date, and also, @Marc Towler needed the solution to work without additional resources such as Azure Functions and Integration account. As such, the following sample application was created (instruction on the readme file) for demonstration purposes: https://github.com/mike-urnun-msft/csvtoxml

Screenshot of the workflow:
Please sign in to rate this answer.
Marc Towler 21 Reputation points

2022-09-29T10:15:37.123+00:00

Hi Mike,

thanks for responding, unfortunately I do not have the ability to bring function apps into the equation, I have Data Factory, Logic Apps, Data Bricks as my only options, do you know of a solution that would be able to assist me with these resources?

Marc Towler 21 Reputation points

2022-10-03T23:04:15.69+00:00

any further ideas anyone?

Michael Daus 1 Reputation point

2022-10-04T02:12:10.073+00:00

Can you utilize Powershell to convert the CSV to XML?

Mike Urnun 9,761 Reputation points Microsoft Employee

2022-10-04T19:35:06.833+00:00

Hey @Marc Towler - Sorry for the delay in my response. You probably won't actually need the Function App at all as you can potentially substitute it with Inline Code action instead but I believe the solution would require an Integration Account resource to complement Logic Apps workflow -- could you confirm if that'll be OK? If so, I'll look into it more thoroughly and supply you with a working sample, and update my original answer above with the details soon.

Marc Towler 21 Reputation points

2022-10-04T22:29:34.043+00:00

Hi Mike,

not a problem at all, I dont have integration account as a seperate feature like in the linked blog but I do have the ability to add schemas and maps to the Logic App as per the image

Marc Towler 21 Reputation points

2022-10-12T16:20:03.857+00:00

Hi @Mike Urnun ,

I hate to be a bother but did you manage to get anywhere with this?

Mike Urnun 9,761 Reputation points Microsoft Employee

2022-10-14T19:02:46.717+00:00

Hi @Marc Towler - My apologies for the delay, I got sidetracked but jumping back in now. Looks like you're on Standard SKU which means you won't need an Integration Account as a separate resource so we're good there.

Function App can be substituted with Inline Code action wherein small code iterates over the CSV that's delimited by commas , and \r\ns to construct an array of JSON objects as its Output whose properties & corresponding values can then be basically turned into Dynamic Content in your Logic Apps flow, which then makes it easier to work with your Map in Transform XML action.

I'll try to whip up the code & the flow by today/tmrw and post the Github link here.

Marc Towler 21 Reputation points

2022-10-14T21:54:51.077+00:00

that will be a massive help thanks @Mike Urnun and no worries on the delay, you are busy on top of helping so it is appreciated!

Marc Towler 21 Reputation points

2022-10-27T23:27:24.1+00:00

sorry to be a pain @Mike Urnun did you manage to put together a solution?

Mike Urnun 9,761 Reputation points Microsoft Employee

2022-10-27T23:53:06.153+00:00

Sorry for the delay, @Marc Towler . I haven't got the entire solution with the XSDs and Maps but I do have the Javascript code below for the Inline Code action which I think was the tricky part in all of this:

var data = workflowContext.actions.Blob_content.outputs.data; var row = data.split("\r\n"); var output = []; for(i=0; i<row.length; i++){ var col_names = row[0].toLowerCase().split(','); var json = {}; if(i!=0){ for(j=0; j<col_names.length; j++){ col = row[i].split(','); json[col_names[j]] = col[j]; } output.push(json); } } return output;

Mike Urnun 9,761 Reputation points Microsoft Employee

2022-10-27T23:57:17.637+00:00

The code above will use the CSV header columns as keys & subsequent rows as values to construct an array of JSON objects. You can then use the Foreach loop alongside Parse JSON action to access each data point and pass them to your Map. I'll try to complete it tonight and post it on GitHub with more details for ya. (can't post here due to character limits)

Marc Towler 21 Reputation points

2022-10-29T16:15:35.46+00:00

Awesome, looking forward to having a look @Mike Urnun . I presume we are just using the inject code to replace the functions app and then doing a little bit extra to convert from JSON to what we need?

Mike Urnun 9,761 Reputation points Microsoft Employee

2022-10-31T05:31:48.243+00:00

Hi @Marc Towler - I've put together the solution and uploaded it on my Github with details: https://github.com/mike-urnun-msft/csvtoxml Sorry it has taken a long time but I hope this helps! Feel free to let me know if any questions ;)

Marc Towler 21 Reputation points

2022-10-31T18:15:59.557+00:00

Hi @Mike Urnun thank you so much, going to start putting this in tomorrow. Just to confirm, all the CSV lines get outputted to a single XML doesn't it?

Mike Urnun 9,761 Reputation points Microsoft Employee

2022-10-31T21:33:23.67+00:00

@Marc Towler Each CSV row results in a separate XML file based on the XSD schema above. If you want to aggregate them into a single XML file, you can do so by leveraging the Variables in Logic Apps. You can initialize a variable of type String and append to it in the Foreach loop, and then as a final step outside the loop, create the single XML file filled with contents from the variable.

Harrison Bulman 0 Reputation points

2023-09-13T16:07:52.4333333+00:00

HI @Marc Towler I'm trying to achieve the same outcome as Mike in his question, converting .csv to .xml using Logic App. I've been trying to recreate the steps laid out here and in the GitHub link but I keep receive an error message saying that "the workflow must be associated with an integration account to use the workflow run action 'Execute_Javascript_Code'. I'm a little confused as your comments here and the readme in the GitHub link says that Integration Account isn't required.

Could you shed any more light on this for me?
Sign in to comment

Convert CSV to XML with Logic App

0 additional answers