In this Excel tutorial, you will learn how to
– Perform mapping in Excel
– Import XML data after mapping
– Remove the XML map
We have used Microsoft 365 to prepare this article. But you can use these methods in Excel versions from Excel 2003 onwards.
We use XML mapping to set up a connection between XML data and the cells within an Excel worksheet. It helps us to handle structured data, automated data as well as bulk data.
We need to have an XML schema file (.xsd) or an XML data file (.xml) to create an XML map in Excel. You can use XML schema or XML data files created from other applications to map in Excel. Otherwise, you can create one using Notepad or save the data as a text file.
Download Practice Workbook
What Is XML Mapping in Excel?
XML mapping in Excel is the process that can transform an XML document into an Excel workbook. You may use the process if you are working with large or complex XML documents, or when you need to import data from an XML document into an Excel workbook.
An XML map represents the structure of an XML document. The XML map helps to link XML elements to cells in an Excel workbook. While importing an XML document into a workbook, the XML data is mapped to the cells that are associated with the XML elements.
How to Perform XML Mapping in Excel?
To perform XML mapping in Excel, you need to have XML data (.xml) or XML schema file (.xsd) and use the Developer tab. The XML data (.xml) file must contain the fields you want to map. You can follow the steps below to map XML in Excel easily.
Step-01: Prepare XML File
- Initially, you need to open a Notepad and write the code of the following image in the notepad.
- After that, go to the File tab of the Notepad and click on Save.
- We give the file’s name as “Create.xml” and save it.
- If you already have an XML file, then you can use that.
Step-02: Map XML Data into Excel
- Now, go to the Developer tab, and click on the Source ribbon. The XML Source task pane will open.
- If you don’t find the Developer tab in the ribbon, you must enable it. You can follow this link to enable the Developer tab.
- In the task pane, you will find the option “XML Maps”. Click on it.
- Right after that, the XML Maps dialog box will appear.
- Click on the Add button. It will open the Select XML Source window.
- Select the correct XML source file and click on Open.
- After opening, you will get a warning message. Click OK to proceed.
- Now, select OK in the XML Maps window.
- As a result, the XML Source task pane will appear.
- We will use the fields under the record section. We can call them map elements.
- Now, select a field and drag them one by one in the worksheet, according to your needs. Click on the video below to see the process.
- After dragging the fields or map elements, add new data to your worksheet.
Step-03: Export Excel File as XML
- When you are done, select Export from the Developer tab.
- Lastly, in the File name box, name your XML file and export it.
How to Import XML Data into Excel After Mapping?
To import XML data into Excel, you need to use the Developer tab in the ribbon. You may follow the steps below to import XML data to Excel.
- First, go to the Developer tab and select Import.
- In the dialog box, select the XML source file.
- Then, choose the position where you want to store your data. Here, we selected Cell B4.
- Just after you click “OK”, you will see the data in the worksheet.
How to Remove XML Mapping?
You can remove an XML map using the XML Maps window. When you are working with someone else’s workbook, you may need to remove the previous mapping. Follow the steps below to delete a map from your workbook.
- First, click on the XML Maps command at the bottom of the XML Source dialog box.
- XML Maps dialog box will appear.
- Now, click on Delete to remove XML mapping.
- A warning dialog box will appear that says “If you delete this XML map, you will no longer be able to import or export XML data using this XML map.”
- Click OK to proceed.
- Finally, check the XML Source pane. You will find that no XML mapping is available now.
What Are Other Features of the XML Section in Excel?
There are some interesting features in the XML section that you can use in Excel. The first one is Map Properties.
1. XML Map Properties
The following image will give you an overall idea of the properties of an XML map.
While working with this box, you will have to be careful about the options under When refreshing or importing data. These options will determine how you are dealing with new data in your worksheet.
If your target is to alter the existing data with new data, you will choose the option “Overwrite existing data with new data”. However, if your goal is to add data to an existing XML table, you may use the option “Append new data to existing XML tables”.
2. Refresh XML Data
It is possible that you have brought some changes to the source data. But Excel does not update it automatically. Don’t be worried about this situation. You can solve this by using the Refresh Data option. Whenever you click on this option, Excel will automatically update the linked data.
Which Things Should You Keep in Mind?
- Before working with XML mapping, ensure that you have a clear understanding of the XML structure.
- Secondly, check the XML file you are working with is valid and well-formed.
- Lastly, give special attention to the data types of the mapped XML elements. Excel interprets certain data types differently, such as dates, numbers, or text.
Frequently Asked Questions
1. What are the benefits of using XML mapping in Excel?
Answer: There are a lot of benefits if you use XML mapping. Some of them are easy importing and exporting of data; data synchronization; data analysis; and automation of the full process.
2. Can I edit the XML data directly in Excel after importing it using XML mapping?
Answer: Yes, you can edit the XML data directly in Excel after importing it using XML mapping. Excel treats the XML data as a structured table, and you can modify the content in the mapped cells. However, note that any changes made to the XML data in Excel will not be reflected in the original XML file unless you export the data back to XML.
3. Can I use XML mapping in Excel without the Developer tab?
Answer: No, XML mapping in Excel requires the Developer tab to be enabled. The Developer tab provides access to the XML mapping tools and features. If the Developer tab is not visible in Excel, you need to enable it by going to the File tab, selecting Options, and customizing the ribbon to show the Developer tab.
Conclusion
Hey! You have reached the end of the article on XML mapping in Excel. You need to use the Developer tab to perform XML mapping in Excel. There are some properties of an XML map that can be handy when you work with large data in Excel. After mapping, you can export the XML file for other uses. If you find this article helpful, please share it with your friends. Moreover, do let us know if you have any further queries.
XML Mapping in Excel: Knowledge Hub
<< Go Back to Export Excel to XML | Export Data from Excel | Learn Excel
