Introduction to XML capabilities in Excel (Part 2)

Previously we exported data in Excel to XML after creating XML Maps from a schema. Here, we will do the opposite, which is to import data from XML into Excel. The same schema will be used. The data in XML however is slightly different, the data has been sorted alphabetically according to the name. This is just to show that the XML data may undergo manipulations and subsequently reloaded into Excel.

<?xml version="1.0" encoding="utf-8"?>
<xs:schema elementFormDefault="qualified" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="Employees">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="Employee" maxOccurs="unbounded">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Name" type="xs:string" />
              <xs:element name="Age" type="xs:integer" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Employees>
	<Employee>
		<Name>Chong</Name>
		<Age>44</Age>
	</Employee>
	<Employee>
		<Name>Crystal</Name>
		<Age>25</Age>
	</Employee>
	<Employee>
		<Name>Elize</Name>
		<Age>29</Age>
	</Employee>
	<Employee>
		<Name>Hafiz</Name>
		<Age>23</Age>
	</Employee>
	<Employee>
		<Name>Rashidan</Name>
		<Age>23</Age>
	</Employee>
</Employees>

Here we have a workbook named SortedEmployees. In Sheet1, we create an empty table with two column headers, Name and Age.

From Developer tab, we launch the XML Source pane, and add the schema Employees.xsd below. This will create the XML Map required for importing XML data and mapping to the worksheet.

The mapping can be done by dragging the XML element from the source pane onto the target cell. In this case, we drag the Employee element onto Name column header.

Now that mapping is done, we may begin importing data from the XML file into Excel. Click on the Import button under Developer tab, and open the XML data source, in this case Employees.xml.

Voila! It’s done. It’s that easy to import and export data between Excel and XML. The tricky part is to write the XML schema definition to create the appropriate XML map. But it’s most likely a one time job, and the schema can be reused many times as long as there are no changes to the data format. Hopefully this article helps a little to demonstrate the XML capabilities of Excel, and inspire you to learn more about Excel, XML and XML Schema.