How to Map an XML File in Excel (Complete Guide)

To export your Excel file as an XML document, you must map your XML document with your Excel worksheet. Without mapping, Excel can’t figure out how data from your Excel worksheet will be stored in the XML file. Thus, mapping is a mandatory step to export an Excel file as an XML file. Thus, I’ve come up with this article to guide you to map and XML documents in Excel. I hope, you will find this article beneficial to you.

What is XML Mapping in Excel?

XML mapping in Excel is the process of creating a mapping between an XML document and an Excel workbook. This mapping allows data from the XML document to be imported into the Excel workbook, and data from the Excel workbook to be exported to the XML document.

XML mapping in Excel can be used to import and export data between an XML document and an Excel workbook. To map an XML document to an Excel workbook, you need to add an XML schema to the workbook.

Related: 2 VBA Codes to Open an XML File in Excel

Related: How to Remove XML Mapping in Excel (Quickest Way)

About the Setup

I’ve created the following dataset in Microsoft Excel. Now I want to export the dataset as an XML file.

To export an Excel file as an XML document, I need to create an XML schema first.

I’ve created the following XML schema to export my Excel file.

If you don’t know how to create an XML schema, please check out this link to learn to create an XML schema for Excel.

<?xml version='1.0' encoding='UTF-8'?>
<dataset>

  <record>
    <FirstName>Ulrick</FirstName>
    <LastName>Copner</LastName>
    <Email>ucopner0@naver.com</Email>
    <Gender>Male</Gender>
  </record>

  <record>
    <FirstName>Tabbie</FirstName>
    <LastName>Aysik</LastName>
    <Email>taysik1@topsy.com</Email>
    <Gender>Female</Gender>
  </record>

</dataset>

Related: How to Create an XML Schema Using Data in Excel

Related: How to Import XML Data from Web to Excel

Guide to Map XML in Excel

To work with XML files, you have select the Developer tab. Under the Developer tab, you will get all the necessary commands in the XML group.

By default, the Developer tab is not activated in the main ribbon. However, if you don’t see the Developer tab in the main ribbon, read 2 ways to add the Developer tab in Excel.

Related: 4 Ways to Open an XML File in Excel

Add an XML Schema Source

Before mapping an XML schema, you need to add an XML schema source first.

To do that,

Step_1: Select the Developer tab first.

Step_2: Now click on the Source command in the XML group.

The XML Source task pane will appear.

Step_3: Click on the XML Maps command at the bottom of the XML Source task pane.

Add an XML Schema Source to Map in Excel

XML Maps dialog box will pop up.

Step_4: Click on the Add button to add an XML schema source.

Add an XML Schema Source to Map in Excel

After selecting an XML schema, the following dialog box will show up.

Step_5: Just hit the OK button to proceed.

Now you will see that your selected XML schema has been added to the XML Maps dialog box.

Step_6: Just hit the OK button to proceed.

Add an XML Schema Source to Map in Excel

Now the column names in the XML schema will show up in the XML Source task pane.

Related: A Complete Guide to Convert a CSV File to an XML File in Excel

Map XML Document to Excel File

Once you have all the column names visible in the XML Source task pane, you can start mapping them.

To map,

Step_1: Select the first column name in the XML Source task pane.

Step_2: Then drag it all the way and drop it on the corresponding column header in the Excel worksheet.

For example, Drag FirstName from the XML Source task pane and drop it on the FirstName column header in the Excel sheet.

Step_3: Repeat Step_1&2 for the rest of the column names.

Map an XML Document to a Worksheet in Excel

This is how you can do that mapping with your XML schema to your Excel worksheet.

To check whether the mapping has been successful or not,

  • Click on any of the column names in the XML Source task pane.
  • The corresponding column will be automatically selected in the Excel worksheet.

For example, if I click on the LastName column header in the XML Source task pane, the LastName column in the Excel worksheet is automatically highlighted.

When the mapping is done successfully, the column names in the XML Source task pane will look bold also. This is another way to check that the mapping has been successfully completed.

Map an XML Document to a Worksheet in Excel

Conclusion

I tried to demonstrate how to map an XML document with an Excel worksheet step by step. I hope this article evaporates all of your confusion. If you still have any issues regarding mapping an XML schema to your Excel worksheet, feel free the describe them in the comment section. Thanks!

Related Articles

(Visited 328 times, 1 visits today)

Similar Posts

Leave a Reply

Your email address will not be published. Required fields are marked *