Adding XML to a Workbook Manually

---

Once you have created an XML schema and an associated XML data file, you need to add the structure of the schema to a worksheet. To do so, you establish a data map, or pattern of data represented in a file, in your worksheet.

Note 

The words map and schema are often used interchangeably.

To associate a data map with a worksheet, follow these steps:

1. Click Data, XML, XML Source to display the XML Source task pane.

2. Click the XML Maps button to display the XML Maps dialog box.

3. Click Add to display the Select XML Source dialog box.

4. Navigate to the folder that contains the schema (a file with an .xsd extension) you want to map to the worksheet.

5. Click the file, and then click Open to add an outline of the schema to the XML Source task pane.

You can now drag individual elements from the map to the worksheet cells where you want the data to appear. After you drag an element to the worksheet, an AutoFilter down arrow will appear at the right edge of the cell. Click the down arrow, and select where you want the element label (the element's name, such as CompanyName) to appear.

Note 

The names of elements you have mapped to a cell in a worksheet appear in bold type in the XML Source task pane.

If you want to add an entire element with all its subelements, you can do so by dragging the main element to a cell in the worksheet. Figure 26-5 shows the result of dragging the Supplier element from the MySuppliers.xsd schema to cell A1.

Figure 26-5: You can map an entire element (with all its subelements) to a worksheet at once.

Note 

When you add a complex element (an element with subelements) to a worksheet, Excel treats the element as a single list, not a series of lists, which occurs when you drag simple elements to the worksheet individually.

After you've added the desired elements to your workbook, you can delete them as you would delete any other workbook contents. You can also remove an element from a worksheet by right-clicking the element in the XML Source task pane and clicking Remove Element on the shortcut menu.

If you want to change how the XML data is inserted into your worksheet, click the Options button in the XML Source task pane. You can use the options to preview your data in the task pane, hide help messages in the task pane, and let Excel know you have already added data labels so that it won't ask every time you insert a field. You can also change how Excel imports XML data by opening the Data menu, pointing to XML, and clicking XML Map Properties. You can use the controls in the XML Map Properties dialog box, shown in Figure 26-6, to change whether Excel resizes the columns to reflect the size of the data, preserves number formatting, and validates XML data against the associated schema before importing or exporting that data.

Figure 26-6: The XML Map Properties dialog box gives you more tools to control your XML data.

Importing XML Data Manually

Once you have assigned the schema elements to worksheet cells, you can import data into the worksheet from the XML data file that matches the structure of the schema.

To import data into an existing XML schema, follow these steps:

1. Click Data, XML, Import.

2. In the Import XML dialog box, navigate to the folder that contains the XML data file, click it, and click Import.

   Note 

   You don't need to map every element of a schema to a worksheet for the data import to work correctly.

---