This article was adapted from Microsoft Office System Inside Out, 2003 Edition by Michael J. Young and Michael Halvorson. Visit Microsoft Learning to buy this book. Michael J. Young is an award-winning author who’s written more than 15 books on using and programming computers, including Microsoft Office XP Inside Out, XML Step by Step and several editions of Running Microsoft Office. Michael Halvorson has written more than 20 computer books, including the classic Microsoft Visual Basic 6 Professional Step by Step and the popular Microsoft Office XP Inside Out (with Michael Young). He is a former technical editor, acquisitions editor, and localization manager for Microsoft.

In this article

Creating an XML list

Working with import options

Customizing an XML list

Sample files  You can download the sample files that relate to excerpts from Microsoft Office System Inside Out from Microsoft Office Online. This article uses the file Inventory.xml.

XML is an increasingly important format for storing and exchanging structured data, primarily on the Internet. XML is ideally suited for representing lists and other types of data that are stored in an Excel worksheet. To gain a general understanding of the features and importance of XML, you should start by reading "Creating, Editing, and Viewing XML Documents," on page 594 of Microsoft Office System Inside Out, 2003 Edition. Pay particular attention to the definitions of the words element and schema. (You might want to stop reading that section when you reach the subsection “Creating XML Documents in Word,” which begins the coverage of the Word-specific XML features.)

 Note   For a comprehensive general introduction to XML, see XML Step by Step, Second Edition (Michael J. Young, XML Step by Step, Second Edition, 2002).

Creating an XML list

Beginning with version 2002, Excel has provided the ability to save an Excel workbook in XML format. The basic advantage of saving a workbook in XML format is that XML is a universal, open, nonproprietary, text-based format that is rapidly becoming the common medium for data exchange. Therefore, a workbook saved in XML isn’t limited to being processed by Excel. Rather, it can be displayed and updated, or have its data extracted, by software written by any third party.

 Note   To save the active workbook in XML format, click Save As on the File menu. Then, in the Save As dialog box, select XML Spreadsheet (*.xml) in the Save As Type drop-down list, and then specify a file name and location. (You can either include the .xml extension in the file name, or omit the extension and let Excel add it.)

When you save a workbook in the XML Spreadsheet format, the resulting document contains all of the workbook information (including the document properties, the size and dimensions of the document window, the workbook styles, the cell formats, as well as the actual cell data). The document uses a specific set of elements and is given a highly specialized structure. (The elements and structure conform to Excel’s own XML schema.) Saving a workbook in XML format allows you to reopen the workbook without loss of features, just as if you had saved it in the proprietary Excel .xls format.

Excel 2003 has added an important new XML capability: Within an Excel worksheet, you can now import, view, analyze, modify, and export XML data that uses any set of elements and has any structure — that is, XML data that conforms to any specified schema. In the worksheet, the XML data is contained in an XML list (which is explained later). You can import XML data from a variety of sources, including XML documents, Web services, or databases that can return data in XML format. You can now also work directly with the XML elements within the worksheet.

For general information on working with lists, see Chapter 28, "Power Database Techniques: Lists, Filters, and PivotTables" in Microsoft Office System 2003 Inside Out.

The following is a basic, general procedure you can use for importing, working with, and exporting XML data in a worksheet:

1. Open the workbook, and then activate the worksheet in which you want to work with XML data.
2. On the Data menu, point to XML, and then click Import to import the XML data into the active worksheet.
3. In the Import XML dialog box (which is similar to the standard Open dialog box), locate and select the XML document that contains the XML data you want to work with, and then click the Import button.

   If the XML document you selected doesn’t refer to an XML schema, Excel will display a message box indicating that it will create a schema based on the selected XML document. To continue, click the OK button in this message box.

   The Import Data dialog box opens.

   

4. In the Import Data dialog box, click XML List In Existing Worksheet.
5. In the text box, indicate where you want to place the block of XML data in your worksheet (or just accept the default location), and then click the OK button.

   Excel inserts the XML data at the specified location. The data will be placed within an XML list (also known as an XML map), which is a special type of Excel 2003 list designed for working with XML data.

    Note   For an explanation of Excel 2003 Lists, see "Using the New Excel List Commands," on page 800 in Microsoft Office System 2003 Inside Out.

   The following is an example of an XML document that you might import into a worksheet. This document describes an inventory of books and is named Inventory.xml. It’s a good example of the type of XML document that you can effectively work with in an Excel list because, like a typical list, it consists of a series of records (the BOOK elements), each of which contains the same set of fields (TITLE, AUTHOR, BINDING, PAGES, and PRICE).

   <?xml version="1.0"?>
   <!-- File Name: Inventory.xml -->
   <INVENTORY>
    <BOOK>
    <TITLE>The Adventures of Huckleberry Finn</TITLE>
    <AUTHOR>Mark Twain</AUTHOR>
    <BINDING>mass market paperback</BINDING>
    <PAGES>298</PAGES>
    <PRICE>5.49</PRICE>
    </BOOK>
    <BOOK>
    <TITLE>Leaves of Grass</TITLE>
    <AUTHOR>Walt Whitman</AUTHOR>
    <BINDING>hardcover</BINDING>
    <PAGES>462</PAGES>
    <PRICE>7.75</PRICE>
    </BOOK>
    <BOOK>
    <TITLE>The Legend of Sleepy Hollow</TITLE>
    <AUTHOR>Washington Irving</AUTHOR>
    <BINDING>mass market paperback</BINDING>
    <PAGES>98</PAGES>
    <PRICE>2.95</PRICE>
    </BOOK>
    <BOOK>
    <TITLE>The Marble Faun</TITLE>
    <AUTHOR>Nathaniel Hawthorne</AUTHOR>
    <BINDING>trade paperback</BINDING>
    <PAGES>473</PAGES>
    <PRICE>10.95</PRICE>
    </BOOK>
    <BOOK>
    <TITLE>Moby-Dick</TITLE>
    <AUTHOR>Herman Melville</AUTHOR>
    <BINDING>hardcover</BINDING>
    <PAGES>724</PAGES>
    <PRICE>9.95</PRICE>
    </BOOK>
    <BOOK>
    <TITLE>The Portrait of a Lady</TITLE>
    <AUTHOR>Henry James</AUTHOR>
    <BINDING>mass market paperback</BINDING>
    <PAGES>256</PAGES>
    <PRICE>4.95</PRICE> 
    </BOOK>
    <BOOK>
    <TITLE>The Scarlet Letter</TITLE>
    <AUTHOR>Nathaniel Hawthorne</AUTHOR>
    <BINDING>trade paperback</BINDING>
    <PAGES>253</PAGES>
    <PRICE>4.25</PRICE>
    </BOOK>
    <BOOK>
    <TITLE>The Turn of the Screw</TITLE>
    <AUTHOR>Henry James</AUTHOR>
    <BINDING>trade paperback</BINDING>
    <PAGES>384</PAGES>
    <PRICE>3.35</PRICE>
    </BOOK>
   </INVENTORY>
   

   The Inventory.xml example XML document is available in the download.

   Here’s how the data looks when it has been imported into a worksheet.

   

   When Excel imports a block of XML data that’s structured like that in Inventory.xml into an XML list, using the procedure described here, it does the following:

   • It maps each of the XML document’s repeating "field" elements (for the example document, TITLE, AUTHOR, BINDING, PAGES, and PRICE) to a separate column. Each mapped column is used for displaying and editing the contents of a specific repeated XML field element.
   • It copies the data from the XML document and displays it in the appropriate cells, according to the mapping. Each of the "record" elements in the XML document (for the example document, each BOOK element) is displayed in a separate row.
   • It normally stores — within the workbook — the location of the XML data source. (This allows you to refresh the XML data from the original source by choosing a single command, as explained later.)
6. In the XML list you can add or delete rows, sort or filter rows, edit or format cells, and perform other manipulations on the data, using the techniques discussed in the previous chapters in this part of the book. You’ll find the methods given in Chapter 28 in Microsoft Office System 2003 Inside Out particularly useful.

   For instance, in the example worksheet (shown under the previous step) you might want to assign currency formatting to the cells in the PRICE column. You might want to sort the rows by author. Or you might want to add a total row at the bottom (you can do this by clicking the Toggle Total Row button on the List toolbar).

7. If you have modified the data in your XML list — if, for example, you have added or removed records or edited the contents of fields — you can save the modified data back to the original XML source document (overwriting the document’s current contents), to another existing XML document (overwriting its contents), or to a new XML document. To do this, select a cell in the XML list, point to XML on the Data menu, and then click Export. Then, in the Export XML Data dialog box (which is similar to the Save As dialog box), choose a file location, select or enter a file name, and then click the Export button. Excel will write the current contents of the XML list to the destination file as a well-formed XML document. This document will store the raw XML data, but not the formatting or other workbook features.

   Using the Export command is generally useful if you are using your XML list as a tool for editing XML (as opposed to just viewing or analyzing XML data).

    Caution   Keep in mind that when you export an XML list to an existing XML document, the document’s contents will be overwritten with the data that you’re saving.

8. If the original XML source document has been modified since you imported it and you want to view the modified document, or if you have changed the XML list in Excel and you want to discard your changes, you can copy the current contents of the source document to the XML list in your worksheet, replacing the current contents of the XML list. To do this, select a cell in the XML list, point to XML on the Data menu, and then click Refresh XML Data.

   

   Using the Refresh XML Data command is generally useful if you’re using your XML list as a tool for viewing or analyzing (rather than editing) the XML data contained in a source document that other software is updating. Consider, for example, that an order entry system stores orders in an XML document and that you’re using an Excel XML list to view and analyze these orders. If this document is the XML source document for your XML list, you would use the Refresh XML Data command to view an up-to-date list of orders.

    Caution   When you use the Refresh XML Data command, the contents of your XML list are replaced with the current contents of the original XML source document. If you use the Refresh XML Data command and then realize you’ve made a big mistake, click Undo Import on the Edit menu immediately after refreshing. Your previous XML list contents will be restored.

9. To save the entire workbook itself, including any XML data that it currently contains, use either the Save command or the Save As command on the File menu.

 Top of Page

Working with import options

If you disable the Save Data Source Definition In Workbook option (described later), Excel will discard the location of the XML source document, and the Refresh XML Data command will no longer be available. You can still refresh the document, however: select a cell in the XML list, point to XML on the Data menu, click Import, and then select the XML source document in the Import XML dialog box.

You can set the Save Data Source Definition In Workbook option, as well as several other options that affect refreshing and importing data, by selecting a cell in your XML list, pointing to XML on the Data menu, and then clicking XML Map Properties to open the XML Map Properties dialog box.

 Note   You can save an entire workbook that contains an XML list and other data in native Excel format or in XML format. You can also save it as an Excel template if you want to use it as the basis for creating other workbooks. Keep in mind that this is a distinct process from exporting the XML data contained in a specific XML list to an XML document. As explained, saving a workbook in XML format always saves all workbook features, including formatting, and uses Excel’s own schema to structure the output document. In contrast, exporting the XML data from a particular XML list saves only the raw data contained in that list and structures the data according to the schema that is attached to that list, creating a "pure" XML document (pure in the sense that Excel workbook information isn’t mixed in).

 Top of Page

Customizing an XML list

When you create an XML list by importing an XML document as described in the previous section, Excel maps all of the repeating field elements, in the order they occur in the document, to successive columns in the XML list, and it reads all the document’s character data into the list. If, however, you wish to work with only a portion of the data from an XML document, or if you want to arrange the fields in a different order from that in which they occur in the document, you can customize the XML list by using the XML Source task pane to remap the field elements to columns in your XML list.

 Note   You can also create a mapping for an XML list from scratch (rather than modifying an existing mapping as described in this section). One way to do this is to use the Open dialog box to directly open an XML document. Then, when Excel displays the Open XML dialog box, select the third option: Use The XML Source Task Pane.

As an example, if you had imported the demonstration XML document given in the previous section (Inventory.xml) into an XML list, you might want to customize the list. You might, for instance, want to display only the AUTHOR element followed by the TITLE element (rather than displaying all five repeating field elements in their original order). The following are the steps you would need to perform:

1. Select a cell in the XML list, point to XML on the Data menu, and then click XML Source to display the XML Source task pane with the design tools shown here:

   

   The XML Source task pane displays a hierarchical list of all the XML source document’s elements, indicating that document’s structure. (Excel stores this structure internally in the schema that it generated when you imported the document.) When you click an element (say, AUTHOR), Excel will highlight the column in the XML list that’s mapped to this element.

2. Remove the mappings for the AUTHOR, BINDING, PAGES, and PRICE elements. To remove these mappings, you can simply delete the corresponding columns (B, C, D, and E) in the worksheet. One way to remove these four columns is to click the heading for column B, hold down the mouse button, drag to the right to highlight all four columns, and then click Delete on the Edit menu. In the XML Source task pane, you will now notice that the AUTHOR, BINDING, PAGES, and PRICE elements are no longer displayed in bold type, indicating that these elements are not currently mapped.
3. Insert a new column to the left of column A (the only remaining column in the XML map). You can do this by selecting column A, and then clicking Columns on the Insert menu.
4. Map the AUTHOR element to the new column you inserted (now column A) by dragging the AUTHOR element from the hierarchical list in the XML Source task pane, dropping it on the top cell of column A (cell A1).

   You now have the XML elements displayed in the desired order in the XML list, but you’ll notice that the AUTHOR column no longer contains data. That’s because you deleted the AUTHOR mapping from its original position and remapped it to a new column.

    Note   XML elements have a one-to-one relationship with worksheet ranges. That is, you can map a given element to only one single range, and a given range can be mapped to only one single element.

5. To restore the data, select any cell in the XML list, point to XML on the Data menu, and then click Refresh XML Data. With your customized XML list, the Refresh XML Data command will read in only the data for the mapped elements (AUTHOR and TITLE), rather than reading in all the document’s character data. The final result is shown in the following figure.

   

    Note   When you export a customized XML list, the resulting XML document will contain just the elements that are actually mapped to the list (including the parents of those elements). In the example, the XML document would contain only the INVENTORY, BOOK, TITLE, and AUTHOR elements. Exporting XML lists was discussed in the previous section.

 Top of Page