Using XML file on Excel, and working further on it is quite simple. We are going to use Microsoft Excel to use XML, but first you need an XML file.

Xml file

This would look like this, in assumption that you use Notepad to create it:

Save this file in an xml format.

Hom to open XML file?

Open Microsoft Excel, and press Open on top.

Browse to the XML file, and click on the arrow under or side of “file name”.

Choose the XML file.

Click on the open as XML table.

Follow the steps in the wizard to import the data into an Excel worksheet.

Click on the last cell in the table, and press TAB on your keyboard.

Keep filling the XML table with details.

If the XML file contains properly formatted data, Excel will open the file and display the data in the worksheet without using the XML Import Wizard.

If the XML data is not properly formatted, you may need to use an XML editor to modify the data before importing it into Excel.

XPath Queries

XPath is a powerful query language for selecting specific data elements within an XML document. Excel supports XPath queries, which allow you to extract precisely the data you need from an XML file. To use XPath in Excel:

1. After importing the XML file, click on the cell containing the XML data.
2. In the Developer tab, click “Source” in the XML group.
3. In the XML Source task pane, you can define and test XPath queries.

XML Mapping

Excel allows you to create XML maps to establish a connection between XML elements and specific cells or ranges in your worksheet. This mapping simplifies the process of importing and exporting data between Excel and XML. To create an XML map:

1. Go to the Developer tab and click “Source” in the XML group.
2. In the XML Source task pane, select “XML Maps.”
3. Add a new XML map and define the correspondence between XML elements and Excel cells or ranges.

Saving the XML File

If you wish to keep the XML format, it’s essential to save the Excel file as an XML file:

1. Go to the “File” menu.
2. Choose “Save As.”
3. Select a location and enter a file name.
4. In the “Save as type” dropdown, choose “XML Data (*.xml).”
5. Click “Save” to save the file in XML format.

Related posts:

How to Share Excel Workbook How to Use DAX (Data Analysis Expressions) Functions How To Use Slicers In Excel