Using XML in Excel 2003

See Also  

For more about the basic workings and terminology of XML, see Introduction to XML in Chapter 6.

In Chapter 6, Special Tasks in Microsoft Office Word 2003, you learned about the new XML support in Word 2003. Excel 2003 also includes full support for XML in the Professional editions, allowing you to open , work with, and save data in a format that can be easily read and applied in any number of forms. The process of using XML in your Excel worksheets follows this basic path :

1. Open the document you want to prepare or work with as an XML document.

2. Attach a workbook map.

3. Use the visual mapping tool to tag the various data items in your worksheet in XML.

4. Save the file as an XML spreadsheet or XML data only.

Note  

Full XML support, which enables you to map XML data, attach your own schemas, and apply transforms, is available only in the Office System Professional editions. Standard and Basic editions of the Office System enable you to save files as XML data but do not allow for higher level XML tasks.

Attaching a Workbook Map

Your first step in preparing your file for XML is to attach the XML schema, also called the workbook map , in Excel 2003. Display the XML Source task pane and click the Workbook Maps button in the lower right corner. In the XML Maps dialog box, click the Add button and navigate to the folder in which the XML schema you want to use is stored. Select it, click Open, and Excel uses that file as your workbook map.

Aha! ”Creating a Schema from a Data File

If you don t have an XML schema, but you have a similar data file that includes all the data elements you want to use, Excel can create a workbook map from that file. Click the XML file name , click Open, and Excel displays a message that it will create the schema based on the source data. Click OK to continue and attach the new document as the workbook map.

 

Using the Visual Mapping Tool

Once the workbook map is selected, the treelike structure of the data is displayed in the XML Source task pane to the right of your worksheet. You can map the XML data with the data tags you want to apply by dragging the item from the map in the XML Source task pane to the data in the worksheet. Figure 7-6 shows a data list that has been tagged as XML data. The List And XML toolbar provides additional tools for importing, exporting, updating, and working with XML data.

Figure 7-6: Preparing an XML file in Excel 2003 is literally as simple as drag-and-drop.

Aha! ”Changing XML Properties

You can change XML properties by clicking the XML Map Properties tool in the List And XML toolbar. In the dialog box that appears, you can control issues around data validation and format, and import and export procedures.