Microsoft Excel makes it easy to import Extensible Markup Language (XML) data that is created from other databases and applications, to map XML elements from an XML schema to worksheet cells, and to export revised XML data for interaction with other databases and applications. Think of these XML features as turning Office Excel into an XML data file generator with a familiar user interface.
XML is a technology that is 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.
Excel works primarily with two types of XML files:
The XML standard also defines Extensible Stylesheet Language Transformation (XSLT) (.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 from within Excel. Choose the XML Files (*.xml) file type before you click the Open button to see the XML files in the folder.
By using XML and Excel, 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 XML features are designed to address:
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 tables
Importing an XML data file (.xml) and binding the XML elements to mapped cells
Entering data, moving mapped cells, and leveraging Excel functionality, while preserving XML structure and definitions
Exporting revised data from mapped cells to an XML data file
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 tables. After you map 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 to 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 that you can create: single-mapped cells and repeating cells (which appear as XML tables). To make designing your worksheet more flexible, you can drag the mapped cells anywhere on a worksheet and into 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:
You use the XML Source task pane to manage XML maps. To open it, on the Developer tab, in the XML group, click Source. The following diagram shows the main features of this task pane.
1. Lists XML maps that were added to the workbook
2. Displays a hierarchical list of XML elements in the currently listed XML map
3. Sets options when working with the XML Source task pane and the XML data, such as how to preview the data and control headings
4. Opens the XML Maps dialog box, which you can use to add, delete, or rename XML maps
5. Verifies whether you can export XML data through the current XML map
The following table summarizes each type of XML element that Excel can work with and the icon that is used to represent each type of element.
Element type
Required parent element
Repeating parent element
Required repeating parent element
Required child element
Repeating child element
Required repeating child element
Simple content in a complex structure
Required simple content in a complex structure
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 you can 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.
XML tables are similar in appearance and functionality to Excel tables. An XML table is an Excel table that has been mapped to one or more XML repeating elements. Each column in the XML table represents an XML element.
An XML table is created when you:
When you create an XML table, the XML element names are automatically used as column headings. You can change these to any column headings that you want. However, the original XML element names are always used when you export data from the mapped cells.
Two options under the Options button in the XML Source task pane are useful when you work with XML tables:
Using XML tables, you can easily import, export, sort, filter, and print data based on an XML data source. However, XML tables do have some limitations regarding how they can be arranged on the worksheet.
You can use formulas in columns that are 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 table, formulas in an XML table are filled down the column when new rows are added to the table.
An XML map and its data source information are saved with the Excel workbook, not a specific worksheet. A malicious user can view this map information by using a Microsoft Visual Basic for Applications (VBA) macro. Furthermore, if you save your workbook as a macro-enabled Excel Office Open XML Format File, this map information can be viewed through Microsoft 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, which is available from the Map Properties command in the XML group on the Developer tab.
If you delete a worksheet before you delete a map, the map information about the data sources, and possibly other sensitive information, is still saved in the workbook. If you are updating the workbook to remove sensitive information, make sure that you delete the XML map before you delete the worksheet, so that the map information is permanently removed from the workbook.
If you import XML data without first adding a corresponding XML schema to create an XML map, Excel tries to infer a schema for you based on the tags that are defined in the XML data file. The inferred schema is stored with the workbook, and the inferred schema allows you to work with XML data if an XML schema file isn't associated with the workbook.
When you work with imported XML data 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 button to display the first row of data as sample data in the element list, if you imported XML data associated with the XML map in the current session of Excel.
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.
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:
You can display the XML Map Properties dialog box (Click Map Properties in the XML group on the Developer tab.) and then use the Validate data against schema for import and export option (active by default) to specify whether Excel validates data against the XML map when exporting data. Click this option when you want to ensure that the XML data you export conforms to the XML schema.
You can save an Excel workbook in a variety of file formats, including the Excel macro-enabled Office Open XML Format File (.xlsm). Excel has a defined XML schema that defines 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 this Excel macro-enabled Office XML Format File. For example, developers may want to create a custom application to search for data in multiple workbooks that are saved in the this format and create a reporting system based on the data found.
You can always ask an expert in the Excel Tech Community or get support in Communities.
