XML is the short form of "Extensible Markup Language." It can hold data in a format that can be easily be read by apps and systems.
If you already have the XMK file, either downloaded or a link, you can easily convert it into an Excel file.
Power Query
Import the "XML file" saved on your system
Download the file using the link, then right-click and save the file.
Once you have the file in your system, follow the steps below to get the XML data into Excel.
1. Open the Excel file on your system where you want to get the data from the XML file.
Using the mouse, right-click on the Excel of the system. It will display several tabs, including the Data tab.
2. Click the Data tab.
Below this, there are properties, queries, and connections, and get a transform.
3. In the "Get & Transform" data group, click on the "Get Data" option.
It Contains the other tabs, including "from a file, from the web, from the table, and other options.
4. Go to the 'From file' option
It will display from the workbook, XML, PDF, and the other sources your system might contain.
5. Click on "from XML."
In the data input dialog box that opens up, locate the XML file you want to import and select it.
6. Click import.
It will import the XML file into the power query and open the Navigator dialog box.
7. Select the data from the XML file you want to import.
The display would be, select multiple items, sitemap index, site map, and others. Click on the Site map.
8. Click on Load
The indicated steps would insert a new worksheet in the Excel file and load all the data from the XML file into that new worksheet.
The power query's great importance in fetching the data from an XML file into Excel is that if the XML file updates and there are new records, there is no need to repeat.
You can right-click on any cell in the table and refresh the query.
Import XML File into Excel using the web URL
You need to download the XML file on the system and import the data into Excel.
If you have a web URL that contains the XML file ( like https://www.Forbes.com/sitemap_index.XML), you don't need to download the file. You can connect the power query to the URL, extract the XML data, and import that data into Excel.
Steps
1. Open the Excel file where you want to import the data.
2. Click the Data tab.
3. In the Get & Transform group, click on the 'Get data' option.
4. Go to the 'From other sources' option.
5. Click on 'From web.'
6. In the 'From web' dialog box, copy and paste the URL data.
7. Click OK.
It will open the Navigator dialog box where you want to choose which XML data to import.
8. Click on 'Sitemap,' which is the XML data you want in Excel.
9. Click on the Transform Data button if you want to transform the data before loading it into Excel (optional).
10. Click load.
The above steps would insert a new worksheet in the Excel file and load all the data from the XML file into a new worksheet. And again, in the data updates in this URL, refresh the query to get the new data in Excel.
Suppose you have the XML in your system, you can import the data easily using the power query, and if you have the web URL of the XML file, you can also fetch the data into Excel.