Import XML data

 Note    XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

Before you import XML data into a worksheet, consider that there are several ways to import and several commands you can use. 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 Command
Import an XML data file into mapped cells Import an XML data file Data —> XML —> Import
Import an XML data file only as list data Import an XML data file as an XML list Data —> XML —> Import
Import multiple XML data files into one or more sets of mapped cells Import multiple XML data files Data —> XML —> Import
Import XML data from Windows SharePoint Services Import XML data from a Web service Data —> Import External Data —> Import Data
Import one or more XML data files with different schemas using the same namespace Import multiple XML data files as external data Data —> Import External Data —> Import Data
Import XML data as flattened data (Excel 2002 XML functionality) Open an XML data file File —> Open
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 File —> Open

Import an XML data file

  1. Select one of the mapped cells into which you want to import XML data.
  2. On the Data menu, point to XML, and then click Import.
  3. In the Look in list, browse to the drive, folder, or Internet location that contains the XML data file (.xml) you want to import.
  4. Click the file, and then click Import.

Top of Page Top of Page

Import an XML data file as an XML list

  1. On the Data menu, point to XML, and then click Import.
  2. In the Look in list, browse to the drive, folder, or Internet location that contains the XML data file (.xml) you want to import.
  3. Click the file, and then click Import.

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

Excel displays the Import Data dialog box.

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

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

  • XML list in new worksheet

The contents of the file are imported into an XML list 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 will infer 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. Do one of the following:
    • To import multiple XML files into a single set of mapped cells, select one of the mapped cells to which you want to import XML data.
    • To import multiple XML files into multiple sets of mapped cells, make sure no mapped cell is selected by clicking a cell anywhere on the worksheet that is not mapped.
  2. On the Data menu, point to XML, and then click Import .
  3. In the Look in list, browse to the drive, folder, or Internet location that contains the XML data files (.xml) you want to import.
  4. Select the files as follows:
    • If the files are contiguous, press SHIFT and then click the first and last file in the list.
    • If the files are not contiguous, press CTRL, and then click each file.
  5. Click Import.
  6. In the Importing <filename>.xml dialog box, for each file, select the XML map that corresponds to the XML data file you are importing.

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

Top of Page Top of Page

Import XML data from a Web service

To do the following procedure, you must have access to a server that is running pnSTS11. A default installation of pnSTS11 provides a data retrieval service for connecting to data in SharePoint lists. A SharePoint site administrator can install the Microsoft Office Web Parts and Components to add additional data retrieval services for Microsoft SQL Server and Microsoft Business Solutions. The installation program for Microsoft Office Web Parts and Components is available from the Downloads on Microsoft Office Online.

  1. On the Data menu, point to Import External Data, and then click Import Data.
  2. Do one of the following:

ShowOpen an existing data source

  • In the Select Data Source dialog box, select a Data Retrieval Service Connection (.uxdc) file from the My Data Sources folder, and then click Open.

ShowCreate and open a new data source connection

  1. In the Select Data Source dialog box, click New Source.
  2. In the Data Connection Wizard, choose a data source to connect to, and then click Next.
  3. Under What kind of data source do you want to connect to?, click Microsoft Business Solutions or Data retrieval services.

An XML list that you can refresh from the data source will be created in your worksheet.

 Note   If you cannot find the data source you want, contact your system administrator.

  1. Follow the remaining instructions in the Data Connection Wizard, and then click Finish when you are done.
  1. Select one of the following options:
  • XML list in existing worksheet

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

  • XML list in new worksheet

The contents of the file are imported into an XML list in a new worksheet starting at cell A1.

  1. If the XML data file does not refer to a schema, then Excel will infer 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 as external data

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

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

  1. On the Data menu, point to Import External Data, and then click Import Data.
  2. In the Look in list, browse to the drive, folder, or Internet location that contains the XML data file (.xml) you want to import.
  3. Click the file, and then click Open.

Excel displays the Import Data dialog box.

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

An XML list is created in a new worksheet.

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

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

  • Existing worksheet

The XML data is "flattened" or transformed into a two-dimensional worksheet made of 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 Microsoft Excel will infer 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 will be 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 File, and then click Open.
  2. In the Look in list, click the drive, folder, or Internet location that contains the file 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 Excel displays:

ShowIf you see the Import XML dialog box...

The Import XML dialog box is displayed if the XML file that you're opening refers to one or more Extensible Stylesheet Language Transformation (XSLT) stylesheets.

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 worksheet made of 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 you want to apply, and then click OK. The XML data is formatted according to the style sheet 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 XML Spreadsheet (XMLSS) format. In this case, Excel does not infer a schema, and you cannot use an XML map.

ShowIf you see the Open XML dialog box...

The Open XML dialog box is displayed if the XML file has no XSLT style sheet references.

Click one of the following options:

  • As an XML list

An XML list is created in a new workbook.

The contents of the file are imported into the XML list. If the XML data file does not refer to a schema, then Microsoft Excel will infer the schema from the XML data file.

  • As a read-only workbook

The XML data is "flattened" or transformed into a two-dimensional worksheet made of 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 XML Spreadsheet (XMLSS) format. 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 Microsoft Excel will infer the schema from the XML data file.

Top of Page Top of Page

 
 
Applies to:
Excel 2003