I worked with a client who needed to process a large amount of data stored in XML format and convert it into a more user-friendly format like CSV for reporting and analysis. They wanted an automated solution that didn’t require manual intervention or third-party tools.
In this tutorial, I will tell you how to convert XML to CSV using Power Automate with different examples.
Convert XML to CSV Using Power Automate
Let’s follow the different examples.
Example 1: Converting Employee Data from XML to CSV
Suppose you recently worked with a client who needed to convert their employee data into a CSV file in XML format. The XML data contained information about employees’ names, heights, weights, and stature (e.g., “average,” “slender,” “stocky”).
<root>
<person>
<name>John Doe</name>
<height>5.9</height>
<weight>170</weight>
<stature>Average</stature>
</person>
<person>
<name>Jane Smith</name>
<height>5.5</height>
<weight>130</weight>
<stature>Slender</stature>
</person>
<person>
<name>Michael Johnson</name>
<height>6.1</height>
<weight>200</weight>
<stature>Stocky</stature>
</person>
</root>This client wanted a simple way to extract this information from the XML and convert it into a CSV file.
Now follow the below steps:
1. In the Power Automate, click the Instant Cloud flow, enter the Flow name, and choose the trigger flow (i.e., manually trigger a flow).
2. Add a compose action and store the above XML into the input parameter.
3. Add Compose action and provide the below expressions to extract each <person> node in the XML data:
xpath(xml(outputs('Compose')), '/root/person')
4. Next, add an Initialize variable action and provide the below parameters:
- Name: varPersonDetails
- Type: Select Array.
- Value: Leave it empty for now.
5. Add an Apply to each action and select the output of the parsed XML from the previous Compose action.
6. Inside the loop, add a Compose action to extract the content of each <person> element using the below expression:
@{items('Apply_to_each')?['$content']}
7. Add another Compose action to convert base64 to the string:
base64ToString(outputs('Compose_2'))
8. Add the Append to array variable action. For Name, select the varPersonDetails variable. For Value, use the below expression to store the details:
{
"Name": @{join(xpath(xml(outputs('Compose_3')), '/person/name/text()'),'')},
"Height": @{join(xpath(xml(outputs('Compose_3')), '/person/height/text()'),'')},
"Weight": @{join(xpath(xml(outputs('Compose_3')), '/person/weight/text()'),'')},
"Stature": @{join(xpath(xml(outputs('Compose_3')), '/person/stature/text()'),'')}
}
9. Outside of the Apply to each action, add Create the CSV Table action. In the From field, select the varPersonDetails array variable.
10. Add the Create File action from the SharePoint Connector and provide below parameters:
- Site Address: Your SharePoint site.
- Folder Path: The destination folder is in the SharePoint library.
- File Name: Name the file, for example, Persons_Stature.csv.
- File Content: Select the output from the Create CSV table action.
Now, click save and run the flow manually after it runs successfully. Go to the SharePoint library to see the CSV file that was created successfully.
Once we open the CSV file, we can see that all the XML data have been exported to the CSV file.
Example 2: Transforming Online Order Details from XML to CSV
Suppose you work for a company that stores its online order data in an XML file generated daily by its e-commerce platform. The XML file includes details such as Order ID, Customer Name, Product Name, Quantity, Price, and Order Date.
<Orders>
<Order>
<OrderID>1001</OrderID>
<CustomerName>John Doe</CustomerName>
<ProductName>Laptop</ProductName>
<Quantity>1</Quantity>
<Price>1200</Price>
<OrderDate>2024-12-01</OrderDate>
</Order>
<Order>
<OrderID>1002</OrderID>
<CustomerName>Jane Smith</CustomerName>
<ProductName>Wireless Mouse</ProductName>
<Quantity>2</Quantity>
<Price>50</Price>
<OrderDate>2024-12-02</OrderDate>
</Order>
<Order>
<OrderID>1003</OrderID>
<CustomerName>Mike Johnson</CustomerName>
<ProductName>Keyboard</ProductName>
<Quantity>1</Quantity>
<Price>75</Price>
<OrderDate>2024-12-03</OrderDate>
</Order>
</Orders>You want this data converted into CSV format to generate daily sales reports.
1. Create an instant cloud flow with a manual trigger. Add an Initialize variable action, set the name to varXMLData, the type to String, and in the value section, provide the XML data mentioned above.
2. Add a compose action and provide the below expression:
json(xml(variables('varXMLData')))
Save the flow and run it. Once it runs successfully, copy the output section and paste it into a Notepad file.
3. Add a Parse JSON action and provide the below parameter:
- Content: Select the output( Compose action) from the dynamic content.
- Schema: To add the schema, click on ‘Generate from sample’, paste the copyed code, and then click ‘Done’. The schema will now be generated and visible.
4. Next, add an Initialize variable action and provide the below parameters:
- Name: varCSVData
- Type: Select String.
- Value: Provide below information:
OrderID,CustomerName,ProductName,Quantity,Price,OrderDate
5. Add the Apply each action and select the output of the Body Order from the previous Parse JSON action.
@{body('Parse_JSON')?['Orders']?['Order']}
6. Add the Append to String variable action to Apply each action. For Name, select the varCSVData variable. For Value, use the below expression to store the details:
@{item()?['OrderID']},@{item()?['CustomerName']},@{item()?['ProductName']},@{item()?['Quantity']},@{item()?['Price']},@{item()?['OrderDate']}
7. Add the Create File action from the SharePoint Connector and provide below parameters:
- Site Address: Your SharePoint site.
- Folder Path: The destination folder is in the SharePoint library.
- File Name: Name the file, for example, Order.csv.
- File Content: Select the output from the varCSVData variable.
Now, click save and run the flow manually after it runs successfully. Go to the SharePoint library to see the CSV file that was created successfully.
Once we open the CSV file, we can see that all the XML data have been exported to the CSV file.
Conclusion
This tutorial taught us how to convert XML data into a user-friendly CSV format using Power Automate. We covered two practical examples: converting employee data (names, heights, weights, and stature) and online order details (Order ID, Customer Name, Product Name, Quantity, Price, and Order Date) to an XML file.
Related Power Automate tutorials:
- Convert CSV to Excel Using Power Automate
- Convert HTML to PDF Using Power Automate
- Convert UTC to Local Time Zone in Power Automate
- Convert SharePoint List Item to PDF Using Power Automate
After working for more than 18 years in Microsoft technologies like SharePoint, Microsoft 365, and Power Platform (Power Apps, Power Automate, and Power BI), I thought will share my SharePoint expertise knowledge with the world. Our audiences are from the United States, Canada, the United Kingdom, Australia, New Zealand, etc. For my expertise knowledge and SharePoint tutorials, Microsoft has been awarded a Microsoft SharePoint MVP (12 times). I have also worked in companies like HP, TCS, KPIT, etc.
