Import XML data

Before you import XML data into a worksheet, consider that there are several methods that you can use to import XML data and several commands that you can use to import XML data. Use the following table to help you decide which method of importing XML data is the best approach for you.

If you want to See the section To use this command
Import an XML data file into previously mapped cells Import an XML data file into mapped cells Import (XML group, Developer tab)
Import an XML data file only as table data into unmapped cells Import an XML data file as an XML table Import (XML group, Developer tab)
Import multiple XML data files into one or more sets of mapped cells Import multiple XML data files Import (XML group, Developer tab)
Import one or more XML data files with different schemas that use the same namespace Import multiple XML data files as external data From XML Data Import (From Other Sources command button, Get External Data group, Data tab)
Import XML data as flattened data (Microsoft Office Excel 2002 XML functionality) Open an XML data file Open (Microsoft Office ButtonButton image )
Import XML data and apply an XSLT (XSL Transformation (XSLT): A file that is used to transform XML documents into other types of documents, such as HTML or XML. It is designed for use as part of XSL.) stylesheet (Excel 2002 XML functionality) Open an XML data file Open (Microsoft Office ButtonButton image )
Learn about issues that you may encounter when you import XML data Issues with importing XML data

Import an XML data file into mapped cells

  1. If the Developer tab is not available, do the following to display it:
    1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
  1. Select one of the mapped cells that you want to import XML data into.
  2. On the Developer tab, in the XML group, click Import.

XML group

The Import XML dialog box is displayed.

  1. On a computer that is running Windows Vista    
  • In the Address bar, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.

On a computer that is running Microsoft Windows XP    

  • In the Look in list, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.
  1. Click the file, and then click Import.

Top of Page Top of Page

Import an XML data file as an XML table

  1. If the Developer tab is not available, do the following to display it:
    1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the XML group, click Import.

XML group

The Import XML dialog box is displayed.

  1. On a computer that is running Windows Vista    
  • In the Address bar, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.

On a computer that is running Microsoft Windows XP    

  • In the Look in list, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.
  1. Click the file, and then click Import.

If the XML data file does not refer to a schema, then Microsoft Office Excel infers the schema from the XML data file.

The Import Data dialog box is displayed.

  1. Select one of the following options:
  • XML table in existing worksheet

The contents of the XML data file are imported into an XML table in the existing worksheet at the specified cell location.

  • XML table in new worksheet

The contents of the file are imported into an XML table in a new worksheet starting at cell A1. The schema of the XML data file is displayed in the XML Source task pane.

  1. If the XML data file does not refer to a schema, then Excel infers the schema from the XML data file.
  2. To control the behavior of XML data, such as data binding, format, and layout, click Properties, which displays the XML Map properties dialog box. For example, existing data in a mapped range will be overwritten when you import data by default, but you can change this.

Top of Page Top of Page

Import multiple XML data files

  1. If the Developer tab is not available, do the following to display it:
    1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
  1. Do one of the following:
  1. To import multiple XML files into a single set of mapped cells, select one of the mapped cells that you want to import XML data to.
  2. To import multiple XML files into multiple sets of mapped cells, make sure that no mapped cell is selected by clicking a cell anywhere on the worksheet that is not mapped.
  1. On the Developer tab, in the XML group, click Import.

XML group

The Import XML dialog box is displayed.

  1. On a computer that is running Windows Vista    
  • In the Address bar, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.

On a computer that is running Microsoft Windows XP    

  • In the Look in list, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.
  1. Select the files as follows:
  • If the files are contiguous, press SHIFT, and then click the first and the last file in the list.
  • If the files are not contiguous, press CTRL, and then click each file that you want to import in the list.
  1. Click Import.

If you chose step:

  • 2a.      All of the data from the XML files is imported and appended to the mapped cells.
  • 2b.     In the Importing <filename>.xml dialog box, for each file, select the XML map that corresponds to the XML data file that you are importing.

To use a single map for all of the selected files that are not yet imported, select Use this XML map for all selected files of this schema.

Top of Page Top of Page

Import multiple XML data files as external data

If you want to import multiple XML files that use the same namespace but different XML schemas, you can use the From XML Data Import command, which was designed so that multiple XML files with the same namespace can use multiple XML schemas. Excel creates a unique XML map and XML mapping for each XML data file that you import.

 Note   If you are importing multiple XML files that do not define a namespace, these XML files are treated as if they use the same namespace.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From XML Data Import.
  2. On a computer that is running Windows Vista    
    • In the Address bar, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.

On a computer that is running Microsoft Windows XP    

  • In the Look in list, click the drive, folder, or Internet location that contains the XML data file (.xml) that you want to import.
  1. Click the file, and then click Open.

The Import Data dialog box is displayed.

  1. Click one of the following options:
    • XML table in existing worksheet

An XML table is created in a new worksheet.

The contents of the file are imported into a new XML table.

If the XML data file does not refer to a schema, then Excel infers the schema from the XML data file.

  • Existing worksheet

The XML data is flattened or transformed into a two-dimensional table comprising rows and columns. The XML tags appear as column headings, and the data appears in rows below the appropriate column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row.

In this case, Excel does not infer a schema, and you cannot use an XML map.

  • New worksheet

Excel adds a new worksheet to your workbook and automatically puts the XML data in the upper-left corner of the new worksheet.

If the XML data file does not refer to a schema, then Excel infers the schema from the XML data file.

  1. To control the behavior of XML data, such as data binding, format, and layout, click Properties, which displays the XML Map properties dialog box. For example, existing data in a mapped range is overwritten when you import data by default, but you can change this.

Top of Page Top of Page

Open an XML data file

  1. Click the Microsoft Office Button Button image, and then click Open.

The Open dialog box is displayed.

  1. On a computer that is running Windows Vista    
    • In the Address bar, click the drive, folder, or Internet location that contains the file that you want to open.

On a computer that is running Microsoft Windows XP    

  • In the Look in list, click the drive, folder, or Internet location that contains the file that you want to open.
  1. In the folder list, locate and open the folder that contains the file.
  2. Click the file, and then click Open.
  3. Choose one of the following, depending on which dialog box is displayed:

The Import XML dialog box    

 Note   This dialog box appears if the XML file that you open refers to one or more Extensible Stylesheet Language Transformation (XSLT) (XSL Transformation (XSLT): A file that is used to transform XML documents into other types of documents, such as HTML or XML. It is designed for use as part of XSL.) style sheets.

Click one of the following options:

  • Open the file without applying a style sheet

The XML data is flattened or transformed into a two-dimensional table comprising rows and columns. The XML tags appear as column headings, and the data appears in rows below the appropriate column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row.

  • Open the file with the following style sheet applied (select one)

Select the style sheet that you want to apply, and then click OK. The XML data is formatted according to the style sheet that you selected.

 Note   The XML data is opened as read-only in Excel so that you don't accidentally save your original source file in the Excel Macro-Enabled Workbook file format (.xlsm). In this case, Excel does not infer a schema, and you cannot use an XML map.

The Open XML dialog box    

 Note   This dialog box appears if the XML file has no XSLT style sheet references.

Click one of the following options:

  • As an XML table

An XML table is created in a new workbook.

The contents of the file are imported into the XML table. If the XML data file does not refer to a schema, then Excel infers the schema from the XML data file.

  • As a read-only workbook

The XML data is flattened or transformed into a two-dimensional table comprising rows and columns. The XML tags appear as column headings, and the data appears in rows below the appropriate column headings. The first element (the root node) is used like a title and is displayed in the specified cell location. The rest of the tags are sorted alphabetically across the second row.

The XML data is opened as read-only in Excel so that you don't accidentally save your original source file in the Excel Macro-Enabled Workbook file format (.xlsm). In this case, Excel does not infer a schema, and you cannot use an XML map.

  • Use the XML Source task pane

The schema of the XML data file is displayed in the XML Source task pane. You can then drag elements of the schema to the worksheet to map those elements to the worksheet.

If the XML data file does not refer to a schema, then Excel infers the schema from the XML data file.

Top of Page Top of Page

Issues with importing XML data

The following sections explain issues that you can encounter when you import XML data.

I get errors when I import XML data.

Microsoft Office Excel 2007 displays the XML Import Error dialog box when it cannot validate data according to the XML map. Click the Details button in this dialog box for additional information about each error. The following table describes important errors that you can encounter.

Error Meaning
Failed schema validation You selected Validate data against schema for import and export in the XML Map Properties dialog box. The data failed to be validated against the specified XML map. However, the data was imported.
Some data was imported as text

Some or all of the data that you imported was converted from its declared data type to text. If you want to use this data in a calculation, you must convert the data from text to numbers or dates. For example, a date value converted to text will not work as intended in the YEAR function until you convert the date value to the Date data type. Excel converts data to text when:

  • The data is expressed in a format that Excel is unable to support.
  • The data is incompatible with the Excel internal representation of the XSD data type. To fix this problem, make sure that your XML data is valid according to the XML schema by checking each data type declaration.
XML parse error The specified XML file cannot be loaded by the XML parser. Make sure that the XML file has no syntax errors and that the XML is well-formed.
Cannot find an XML map that corresponds to the data This problem can occur when you select more than one XML data file to import, and Excel cannot find a matching XML map for one of the files. For the file named in the title bar of the dialog box, import an appropriate schema first, and then import the file again.
An XML table cannot be resized to accommodate data

You are attempting to add rows by importing or appending data to the XML table, but there is no room for the table to expand. An XML table can only expand from the bottom down. For example, there may be an object, such as a picture or even another table, immediately below the XML table that prevents the XML table from growing. Or, for the XML table to expand, it will exceed the Excel row limit of 1,048,576.

To fix this problem, rearrange the tables and objects on the worksheet to allow the XML table to expand from the bottom down.

When I open an XML file, I get the message, "The specified XML file does not refer to a schema".

You are opening an XML file that doesn't refer to an XML schema (XML Schema: A formal specification, written in XML, that defines the structure of an XML document, including element names and rich data types, which elements can appear in combination, and which attributes are available for each element.). Excel must create a schema based on the contents of the XML file in order to work with the XML data that is contained in the file.

If the schema that is created by Excel is incorrect or insufficient for your needs, then you should create an XML schema file and then edit the XML data file so that the XML data file refers to the schema.

 Note   You cannot export the Excel inferred schema as a separate XML schema data file (.xsd). Although there are XML schema editors and other methods for creating an XML schema file, you may not have convenient access to them or know how to use them. As an alternative, you can use the Microsoft Office Excel 2003 XML Tools Add-in Version 1.1, which can create a schema file from an XML map. For more information, see Using the Excel 2003 XML Tools Add-in Version 1.1.

Follow these steps to remove the schema that Excel created from your workbook:

  1. If the Developer tab is not available, do the following to display it:
    1. Click the Microsoft Office Button Button image, and then click Excel Options.
  1. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.
  1. On the Developer tab, in the XML group, click Source.

XML group

  1. In the XML Source task pane, click XML Maps.

The XML Maps dialog box is displayed.

  1. Click the XML map that represents the schema that was created by Excel, and then click Delete.

I am having problems importing multiple XML files that use the same namespace but different schemas.

In general, when you work with multiple XML data files and XML schemas, it is common to create an XML map for each schema, map the elements that you want, and then import each XML data file to the appropriate XML map. The Import command (in the XML group on the Developer tab) was designed so that if you import multiple XML files with the same namespace, you can only use one XML schema. If you use this command to import multiple XML files that use the same namespace but different schemas, you can get unexpected results. For example, data may get overwritten, or the operation may fail.

If you want to import multiple XML files that use the same namespace but different XML schemas, you can use the From XML Data Import command (from the From Other Sources button, in the Get External Data group, on the Data tab), which was designed so that multiple XML files with the same namespace can use multiple XML schemas. Excel creates a unique XML map and XML mapping for each XML data file that you import.

 Note   If you are importing multiple XML files that do not define a namespace, these XML files are treated as if they use the same namespace.

Top of Page Top of Page

 
 
Applies to:
Excel 2007