Amal Nagm

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

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 , and then click Excel Options.
   2. 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.
2. Select one of the mapped cells that you want to import XML data into.
3. On the Developer tab, in the XML group, click Import.

   

   The Import XML dialog box is displayed.

4. 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.

5. Click the file, and then click Import.

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 , and then click Excel Options.
   2. 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.

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

   

   The Import XML dialog box is displayed.

3. 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.

4. 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.

5. 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.

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

6. 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.

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 , and then click Excel Options.
   2. 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.
2. 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.
3. On the Developer tab, in the XML group, click Import.

   

   The Import XML dialog box is displayed.

4. 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.

5. 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.
6. 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.

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.

3. Click the file, and then click Open.

   The Import Data dialog box is displayed.

4. 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.

5. 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.

Open an XML data file

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

   The Open dialog box is displayed.

2. 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.

3. In the folder list, locate and open the folder that contains the file.
4. Click the file, and then click Open.
5. 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.

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.

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.

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 , and then click Excel Options.
   2. 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.
2. On the Developer tab, in the XML group, click Source.

   

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

   The XML Maps dialog box is displayed.

4. 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.