Microsoft Office Excel 2003 makes it easy to import Extensible Markup Language (XML) (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) data created from other databases and applications, map XML elements from 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.) to worksheet cells, and export revised XML data for interaction with other databases and applications. Think of these new XML features as turning Excel 2003 into an XML data file generator with a familiar user interface.

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.

This topic covers several aspects of using XML in Excel, including:

• Why use XML in Excel?
  • XML data and schema files
  • Key XML and Excel scenarios
• The basic process of using XML data in Excel
• Working with XML maps
  • Using the XML Source task pane
  • Element types and their icons
  • Working with single-mapped cells
  • Working with repeating cells in XML lists
  • XML map security considerations
• Importing XML data
• Working with an inferred schema
• Exporting XML data
• Using the XML Spreadsheet Schema format (XMLSS)

Why use XML in Excel?

XML is a technology designed for managing and sharing structured data in a human-readable text file. XML follows industry-standard guidelines and can be processed by a variety of databases and applications. Using XML, application designers can create their own customized tags, data structures, and schemas. In short, XML greatly eases the definition, transmission, validation, and interpretation of data between databases, applications, and organizations.

Top of Page

XML data and schema files

Excel works primarily with two types of XML files:

• XML data files (.xml), which contain the custom tags and structured data.
• Schema files (.xsd), which contain schema tags that enforce rules, such as data type and validation.

Note  The XML standard also defines 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.) (.xslt) files which are used to apply styles and transform XML data into different presentation formats. You can apply these transforms before you import XML files into Excel and after you export XML files from Excel. If XSLT files are linked to XML data files that you import into Excel, you do have the option to apply or not apply the formatting before the data is added to the worksheet, but only when you open an XML file by using the Open command under the File menu.

Top of Page

Key XML and Excel scenarios

Using XML and Excel 2003, you can manage workbooks and data in ways that were previously impossible or very difficult. By using XML maps, you can easily add, identify and extract specific pieces of business data from Excel documents. For example, an invoice that contains the name and address of a customer, or a report that contains last quarter's financial results are no longer just static reports. You can easily import this information from databases and applications, revise it, and export it to the same or other databases and applications.

The following are key scenarios that the Office Excel 2003 XML features are designed to address:

• Extend the functionality of existing Excel templates by mapping XML elements onto existing cells. This makes it easier to get XML data into and out of your templates without having to redesign them from the ground up.
• Use XML data as input to your existing calculation models by mapping XML elements onto existing spreadsheets.
• Import XML data files into a new workbook.
• Import XML data from a Web service into your Excel worksheet.
• Export data in mapped cells to XML data files independent from other data in the workbook.

Top of Page

The basic process of using XML data in Excel

The following diagram shows how the different files and operations work together when you use XML with Excel. Essentially, there are five phases to the process:

Adding an XML schema file (.xsd) to a workbook.

Mapping XML schema elements to individual cells or XML lists.

Importing an XML data file (.xml) and binding the XML elements to mapped cells.

Entering data, moving mapped cells, and and leveraging Excel functionality, while preserving XML structure and definitions.

Exporting revised data from mapped cells to an XML data file.

Top of Page

Working with XML maps

You can create or open a workbook in Excel, attach an XML schema file (.xsd) to the workbook, and then use the XML Source task pane to map XML elements of the schema to individual cells or lists. Once you have mapped the XML elements to your worksheet, you can import and export XML data into and out of the mapped cells.

When you add an XML schema file (.xsd) to your workbook, you create an XML map. In general, XML maps are used to create mapped cells, and manage the relationship between mapped cells and individual elements in the XML schema. In addition, these XML maps are used to bind the contents of mapped cells to elements in the schema when you import or export XML data files (.xml).

There are two kinds of mapped cells you can create: single-mapped cells and repeating cells (which appear as XML lists). To make designing your worksheet more flexible, you can drag the mapped cells anywhere on a worksheet and in any order — even one different from the XML schema. You can also choose which elements to map and not map.

The following rules about using XML maps are important to know:

• A workbook can contain one or more XML maps.
• You can only map one element to one location in a workbook at a time.
• Each XML map is an independent entity, even if multiple XML maps in the same workbook refer to the same schema.
• An XML map can only contain one root element. If you add a schema that defines more than one root element, then you will be prompted to choose the root element to use for the new XML map.

Top of Page

Using the XML Source task pane

You use the XML Source task pane to manage XML maps. To open it, click XML on the Data menu, and then click XML Source. The following diagram shows the main features of this task pane.

 Lists XML maps that have been added to the workbook.

 Displays a hierarchical list of XML elements in the currently listed XML map.

 Sets options when working with the XML Source task pane and the XML data, such as how to preview the data and control headings.

 Opens the XML Maps dialog box, which you can use to add, delete, or rename XML maps.

 Verifies whether you can export XML data through the current XML map.

Top of Page

Element types and their icons

The following table summarizes each type of XML element that Excel can work with and the icon used to represent it.

Element type	Icon
Parent element
Required parent element
Repeating parent element
Required repeating parent element
Child element
Required child element
Repeating child element
Required repeating child element
Attribute
Required attribute
Simple content in a complex structure
Required simple content in a complex structure

Top of Page

Working with single-mapped cells

A single-mapped cell is a cell that has been mapped to a nonrepeating XML element. You create a single-mapped cell by dragging a nonrepeating XML element from the XML Source task pane onto a single cell in your worksheet.

When you drag a nonrepeating XML element onto the worksheet, you can use a Smart Tag to choose to include the XML element name as a heading above or just to the left of the single-mapped cell, or use an existing cell value as a heading.

You can also use a formula in a single-mapped cell if the cell is mapped to an XML element with an XML Schema Definition (XSD) data type that Excel interprets as a number, date, or time.

Top of Page

Working with repeating cells in XML lists

XML lists are similar in appearance and functionality to Excel lists (list: A series of rows that contains related data or a series of rows that you designate to function as a datasheet by using the Create List command.). An XML list is an Excel list that has been mapped to one or more XML repeating elements. Each column in the XML list represents an XML element.

An XML list is created when you:

• Use the Import command (Data menu, XML submenu) to import an XML data file.
• Use the Open command (File menu) to open an XML data file — and then select As an XML list in the Open XML dialog box.
• Use the Import Data command (Data menu, Import External Data submenu) to import an XML data file — and then select XML list in existing worksheet or New worksheet in the Import Data dialog box.
• Drag one or more repeating elements from the XML Source task pane to a worksheet.

When you create an XML list, the, XML element names are automatically used as column headings. You can change these to any column headings you want. However, the original XML element names are always used when you export data from the mapped cells.

Two options under the Options drop-down button in the XML Source task pane are useful when working with XML lists:

• Automatically Merge Elements When Mapping  When checked, Excels create one XML list from multiple fields as they are dropped onto the spreadsheet. This works as long as they are dropped on the same row, one adjacent to the other. When this option is cleared, each element will appear as its own XML list.
• My Data Has Headings  When checked, existing heading data will be used as column headings for repeating elements that you map to your worksheet. When this option is cleared, the XML element names are used as column headings.

Using XML lists, you can easily import, export, sort, filter, and print data based on an XML data source. However, XML lists do have some limitations regarding how they can be arranged on the worksheet.

• XML lists are row-based, meaning that they grow from the header row downwards. You cannot add new entries above existing rows.
• You cannot transpose an XML list so that new entries would be added to the right.

You can use formulas in columns mapped to XML elements with an XML Schema Definition (XSD) data type that Excel interprets as a number, date, or time. Just as in an Excel list, formulas in an XML list are filled down the column when new rows are added to the list.

Top of Page

XML map security considerations

An XML map and its data source information is saved with the Excel workbook, not a specific worksheet. A malicious user could view this map information by using a Microsoft Visual Basic for Applications (VBA) macro. Furthermore, if you save your workbook as an interactive Web page or as an XML workbook, this map information can be viewed through Notepad or through another text-editing program.

If you want to keep using the map information but remove the potentially sensitive data source information, you can delete the data source definition of the XML Schema from the workbook, but still export the XML data, by clearing the Save data source definition in workbook check box in the XML Map Properties dialog box available from the XML submenu under the Data menu.

If you delete a worksheet before deleting a map, the map information about the data sources and possibly other sensitive information is still saved in the workbook. If you are cleaning the workbook to remove sensitive information, make sure you delete the XML map before you delete the worksheet so that the map information is permanently removed from the workbook.

Top of Page

Importing XML data

You can import XML data in several ways:

• Import the contents of an XML data file into an existing XML map in your workbook.
• Import XML data from a Web service by using a Data Retrieval Service Connection (.uxdc) file to connect to a data source.

When you import data, you bind the data from the file to an XML map stored in your workbook. This means that each data element in the XML data file has a corresponding element in the XML schema that you mapped from an XML Schema file or inferred schema. Each XML map can only have one XML data binding, and an XML data binding is bound to all of the mappings that have been created from a single XML map.

You can display the XML Map Properties dialog box (Click XML under the Data menu.) where there are three options, all enabled by default, that you can set or clear to control the behavior of an XML data binding:

• Validate data against schema for import and export  Specifies whether or not Excel validates data against the XML map when importing data. Set this option when you want to ensure that the XML data you import conforms to the XML schema.
• Overwrite existing data with new data  Specifies whether or not data is overwritten when you import data. Set this option when you want to replace the current data with new data, for example, when up-to-date data is contained in the new XML data file.
• Append new data to existing XML lists  Specifies whether or not the contents of the data source are appended to the existing data on the worksheet. Set this option, for example, when you are consolidating data from several similar XML data files into an XML list, or you do not want to overwrite the contents of a cell that contains a function.

When you import XML data, you may want to overwrite some mapped cells but not others. For example, some mapped cells may contain formulas, and you don't want to overwrite the formula when you import an XML file. There are two approaches you can take:

• Unmap the elements that you don't want over-written before you import the XML data. After you import the XML data, you can then re-map the XML element to the cells containing the formulas, so you can export the results of the formulas to the XML data file.
• Create two XML maps from the same XML schema. Use one XML map for importing the XML data. In this "Import" XML map, don't map elements to the cells that contain formulas or other data that you don't want overwritten. Use another XML map for exporting the data. In this "Export" XML map, map the elements that you want to export to an XML file.

Top of Page

Working with an inferred schema

If you import XML data without first adding a corresponding XML schema to create an XML map, Excel will try to infer a schema for you based on the tags defined in the XML data file. The inferred schema is stored with the workbook, and let's you work with XML data if you don't have an XML schema file associated with it.

When working with imported XML that has an inferred schema, you can also customize the XML Source task pane. Select the Preview Data in Task Pane option from the Options drop-down button to display the first row of data as sample data in the element list if you have imported XML data associated with the XML map in the current session of Excel.

You cannot export Excel's 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 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.

Top of Page

Exporting XML data

You export XML data by exporting the contents of mapped cells on the worksheet. When you export data, Excel applies the following rules to determine what data to save and how to save it:

• Empty items are not created when blank cells exist for an optional element, but empty items are created when blank cells exist for a required element.
• Unicode Transformation Format-8 (UTF-8) encoding is used to write the data.
• All namespaces are defined in the Root XML element.
• Excel overwrites existing namespace prefixes. The default namespace is assigned a prefix of ns0. Successive namespaces are designated ns1, ns2 to ns<count> where <count> is the number of namespaces written to the XML file.
• Comment nodes are not preserved.

You can display the XML Map Properties dialog box (Click XML under the Data menu.) where you can use the Validate data against schema for import and export option, enabled by default, to specify whether or not Excel validates data against the XML map when export data. Set this option when you want to ensure that the XML data you export conforms to the XML schema.

Top of Page

Using the XML Spreadsheet Schema format (XMLSS)

You can save an Excel workbook in a variety of file formats, including XML Spreadsheet format. Excel has defined its own XML schema, called XML Spreadsheet Schema (XMLSS), that define the contents of an Excel workbook, including XML tags that store all workbook information, such as data and properties, and define the overall structure of the workbook. Custom applications can use XMLSS. For example, developers may want to create a custom application to search for data in multiple workbooks saved in the XMLSS format and create a reporting system based on the data found.

Top of Page