How to Import XML Files

1. Open XML document to import its data
2. Click Developer > Import to import XML document
3. Click Data > Get Data > From File > From XML to import XML file

Open XML file with Excel to import its data

To open an XML document from the Excel application, select File tab > Open > Browse and then choose the desired XML file (such as pets.xml file which you created on Introducing XML tutorial) from the Open dialog box. After selecting a file, the Open XML dialog box appears:

The Open XML dialog box will be asked you to how to open the file:

• As an XML table
• As a read-only workbook
• Use the XML Source task pane

Select the As an XML table option and click OK. After clicking OK button Excel tells you that it could not find the schema for the XML document:

Click OK, Excel will automatically create the schema based on the content of the XML document. If you are trying to open a very complex XML document, a schema file created by Excel may be incorrect. In this case, you will need to create your own XML Schema Description document (.xsd file extension).

When you open an XML file as a table, Excel adds the data to a worksheet and creates a formatted table. Excel styles the data which provides additional features and formatting that makes it easy to identify and modify the table. Following figure shows data from the pets.xml document:

The table is highlighted with a blue table style (which you can change form the Design tab), and a filter (normally selected from the Data > Filter) is automatically applied. The XML document, opened in the above figure, contains the following code:

<?xml version="1.0"?>
 <pets>
  <pet type="cat" age="2">Simba</pet>
  <pet type="dog" age="3">Max</pet>
  <pet type="dog" age="5">Lucy</pet>
  <pet type="dog" age="5">Teddy</pet>
 </pets>

You can manage the Table and the data it contains from the XML section on the Developer tab:

For example, you can export changes to the table to the XML file, refresh the data in the table, edit the properties of the XML map, open the XML Source task pane and more.

Import XML Data using Developer tab

You can also import data from an XML file into any existing worksheet by selecting Developer tab and then clicking the Import button:

Again, a confirmation box appears if the XML document does not reference to an existing schema document (.xsd file):

You will also prompted to select a range in the worksheet telling where you want the data inserted. Choose an option and click OK button to import XML data:

Import XML using Data tab

You can also import the XML data by clicking the Data > Get Data > From File > From XML. This method also helps you for creating a data model:

When you import an XML data file, the two XML events AfterXMLImport and BeforeXMLImport triggered. You can write Workbook_AfterXMLImport and Workbook_BeforeXMLImport event listener procedures for these events to control what happens before and after import.