In this article, we’ll learn simple steps to create XML mapping in Excel. We often need to change the type of a file so that it can be used with different programs and operating systems. Based on what they need, XML and XLSX are two of the most popular file types for Android and Windows. So converting XML to Excel or Excel to XML is a very important scheme these days.
You can download the practice workbook and XML structure file from here.
Step-by-Step Procedures to Create XML Mapping in Excel
There are 3 main steps to create XML Mapping in Excel. The main portion is choosing an XML file which we will follow for the structure. Then we will put that structure or format into the Excel workbook. And finally, we will export our Excel file as XML or keep it as it is if we need to. So those procedures are described below.
Step 1: Choosing XML File
For creating XML mapping, we will require an XML file that we will follow to create a new XML file with our desired data. Here in our case, we will follow the XML file code attached in the below image. We need the structure only to create XML mapping. If the XML file only contains structure, that is also okay to work with. We only need the structure for mapping, not any data. Here is a preview of the structure we will use as an example.
Step 2: Mapping XML into Excel File
This step is the main phase where we will start creating XML mapping. For reference, we want to create XML mapping on the following data table.
To create XML mapping we will follow these steps:
- First, we will go to the Developer tab in the Ribbon, and in the XML area select Source.
- Second, selecting Source will trigger a panel on the right named XML In that panel select XML Maps. A dialog box named XML Maps will appear.
- Third, we will select Add in the dialog box.
- Next from that window, select the file that’s structure will be followed. In our case, we need to write the code mentioned earlier and save it as an XML file. Here we saved it as XML Example.xml.
- Then we will see a message from Excel like the one below. We will click on OK.
- Furthermore, press OK in the XML Maps box as well.
- After that, we will see the structure of the XML Example file on the XML source panel.
- Consequently, we will drag and drop the field names from the XML Source panel to our table’s respective fields/columns.
- Finally after doing that we will have the XML mapped in our Excel file.
Step 3: Exporting Excel File as XML File
We need to export our data as an XML file after mapping. So we should follow these steps:
- Firstly we will select Export in the XML section under the Developer tab in the Ribbon.
- Secondly, we will give our new file a name and save it.
Things to Remember
- The data type must be the same while mapping a column.
- Not necessary to have the same number of fields as the XML file has. We may not need to use a column in the XML But all the fields/columns in the workbook must be mapped to export as XML.
- For this demonstration, we used Excel 365. So the interface may vary with other versions.
Conclusion
Creating XML mapping in Excel is important as technology is now centralizing file sharing between different platforms. This article will help you to create XML mapping in excel. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions. For any excel related problems, you can visit our website Exceldemy for all types of excel related problem solutions.