In this blog post we follow the instruction at the MSDN website’s article Creating an XML Mapping Schema in Excel 2010 on how to import an XML file into Microsoft Excel.

There are three main steps you take to accomplish this.

1. Create the XML Data File
2. Create the XML Mapping Schema File
3. Import the Schema and XML Data into Excel

1. Create the XML Data Fie

1. Start a text editor such as Notepad, Notepad++, Sublime or Atom.
2. Paste or type the following XML into the editor. See below.
3. Save the document as C:\BookData.xml (or change the location if you wish)
4. Close the text editor.

Below is an example of an XML file from MSDN by Microsoft.

<?xml version='1.0'?>
<BookInfo>
   <Book>
      <ISBN>989-0-487-04641-2</ISBN>
      <Title>My World</Title>
      <Author>Nancy Davolio</Author>
      <Quantity>121</Quantity>
   </Book>
   <Book>
      <ISBN>981-0-776-05541-0</ISBN>
      <Title>Get Connected</Title>
      <Author>Janet Leverling</Author>
      <Quantity>435</Quantity>
   </Book>
   <Book>
      <ISBN>999-1-543-02345-2</ISBN>
      <Title>Honesty</Title>
      <Author>Robert Fuller</Author>
      <Quantity>315</Quantity>
   </Book>
</BookInfo>

2. Create the XML Mapping Schema File

In this task, you create a custom XML Mapping schema by using Microsoft Visual Basic for Applications (VBA). You can create a schema with either of two methods: by explicitly writing the template XML in the code or by reading the XML file into a string and then creating the schema from that string. In this blog we will use the second method: reading the XML file into a string.

To create the schema in Excel by reading the XML data into a string:

1. In a blank Excel 2010 workbook, open the Visual Basic Editor by pressing Alt+F11.
2. Insert a general module by clicking Insert and then clicking Module.
3. In the module, paste or type the following code. See below.
4. Change the location of the BookData.xml file in the above code to the location of your file. Change it in both places in the code.
5. Close the Visual Basic Editor.
6. Next, run the code. On the Developers tab, click Macros, highlight Create_XSD, and then click Run. Examine the schema file at C:\MySchema.xsd. Shortly, you will load this file into Excel 2010 to create an XML map.

Sub Create_XSD2()
   Dim StrMyXml As String, MyMap As XmlMap
   Dim StrMySchema As String
   ' Book.xml is the file created in section one of this topic.
   StrMyXml = "C:\BookData.xml"

   ' Turn off async loading.
   Application.DisplayAlerts = False
   ' Add the string to the XmlMaps collection.
   Set MyMap = ThisWorkbook.XmlMaps.Add(StrMyXml)
   Application.DisplayAlerts = True

   ' Create an empty file and output the schema.
   StrMySchema = ThisWorkbook.XmlMaps(1).Schemas(1).XML
   Open "C:\BookData2.xsd" For Output As #1
   Print #1, StrMySchema
   Close #1
End Sub

3. Import the Schema and XML Data into Excel

Import the schema created in the previous section into Excel to create an XML map and then import an XML data file.

1. On the Developer tab, in the XML group, click Source to open the XML Source task pane.
2. In the XML Source task pane, drag the BookInfo node to cell A1. This action creates a table in the worksheet. You can format the table as necessary.

1. Next, import the XML data file. On the Developer tab, click Import.
2. In the Import XML dialog box, navigate to the XML data file and then click Import. The data is mapped into the formatted table as shown in Figure 2.

Here is a screenshot of the result.

At this point in this exercise you can actually add data rows to your table and then go ahead and export those back out to a new XML file. Just add the data and then click the Export button in the Developer tab. You can even delete the existing data in the table and add new rows of data and export that to an XML file.