Import XML to PowerApps
When preparing for a new project, I started to check whether different customer expectations are feasible with PowerApps. The first of these is the ability to use an XML file as input, to build a table in the application and possibly to save them to SQL later.
Nothing simpler, I thought. Finally, there is an “Attachment” control in PowerApps. And if not, there’s definitely some other one that I can use.
I learned on this occasion that the “Attachment” control is dedicated to forms linked to SharePoint lists and it is not possible to put it in the application “just as-is”. But there is another one that is called “Add picture” – although the name suggests using it only for adding image, it has a much more versatile usage purposes.
How to upload file to application?
Couple of words about the control to add an image. It is in the “Media” group:
Although it actually suggests, also after placing it on canvas, that it is only for uploading photos, the key phrase from the description is “Let users upload image through your app”. And if it allows uploading photos, then probably other file formats, right?
When you select it in the application, a group consisting of two controls is added. One is “AddMediaButton“, the other is just a control displaying the image. I was solely interested in this first one, which is why I removed the second one:
The control works in the following way
- User clicks it
- A Window Explorer pops-up allowing you to select “Images” or “All files” (luckily!)
- User selects a file
- File is uploaded to the application and stored in local blob storage.
In my scenario, I’ve added validation so that only XML files can be added. For the “OnChange” event of the control to select files, I added a piece of code:
If( EndsWith(FileUpload.FileName, ".xml"), [true], [false] )
So, if the file extension does not end with “xml“, then an error is shown and the upload button is disabled.
How to handle uploaded file?
Here, Microsoft Flow naturally enters the game. However, in order to send a file to the flow, it is not enough to simply use “FileUpload.Media“, because the file’s data is in local blob and this expression in no way matches the contents of the file, but returns its address:, e.g.: appres://blobmanager/jakisGUID
To send such a file to Flow, you need to use the Azure Blob Storage support and the method described in my post for placing a manual signature on the document:
- upload file to Azure Blob Storage
- call Flow passing URL of the uploaded file
- read file from Blob by Flow.
AzureBlobStorage.CreateBlockBlob( "files", FileUpload.FileName, FileUpload.Media );
Then the application must run Flow, passing the file’s address linking to the blob. Then Flow downloads the file and in my case, converts its content to JSON using the “Parse JSON” action, but first deleting the line that defines it as XML:
Using the below expression:
json(xml(replace(body('GetFileFromBlob'), '<?xml version="1.0" encoding="UTF-8"?>', '')))
Of course before parsing the XML to JSON you need to know its schema, so the best approach would be to first discover how your converted XML to JSON is going to look like and then use it as an example to generate schema.
I chose this approach because the use of xpath to read a file has a disadvantage – it creates a list. Finally, to return such a list to PowerApps it should be converted to JSON format. And in addition, if it has more than 5k elements, the loop rev limit should be additionally handled.
Current Flow limitations allow the loop to make max. 5.000 rounds. 100,000 in premium plans.https://docs.microsoft.com/en-us/flow/limits-and-config#looping-and-debatching-limits
And that’s basically everything. Having such an object, in which records are repeating , I can return them to PowerApps. Importantly, to return these records the standard action “Respond to PowerApps” is not enough, it is necessary to use the action “HTTP response” to transfer the data preserving the JSON format.
However, I do not return the entire contents of the file, but only the content from one of the attributes, which in my case is a table:
How does it work?
Above all – quickly. The complete expression under the data import button looks like below:
AzureBlobStorage.CreateBlockBlob( "files", FileUpload.FileName, FileUpload.Media ); ClearCollect(xml, ParseXMLFile.Run("/files/" & FileUpload.FileName))
The XML file has below format:
Static vs. dynamic
Actually, using this XML file and xpath you could make this solution a bit more dynamic. In described scenario Flow needs to know the JSON schema in order to be able to process XML data properly.
However, you can prepare your own mechanism running as SaaS, which receives JSON code and returns a schema for it, which is then used in the flow.
You can also define a dictionary for XML schemas that you decide to import into the application and based on the user’s choice, simply send a parameter enabling Flow to download the appropriate schema.
I hope that the solution described will be helpful for you. If you liked the article, leave a comment!