To create an XML map in Excel2003

excel|xml| Create

Microsoft Office Excel 2003 improves support for using native XML by allowing you to add your own XML Schema definition (XSD) to the workbook. This allows you to create an XML map that makes the data more meaningful when you import or export data. Therefore, you can import data that conforms to a custom XML vocabulary or schema, and then write the data back using the same XML schema.

This feature is intended for Office solution developers, but expert users can also use it well. This feature enables you to: ·

Create a map between the EXCEL spreadsheet and the XML structure ·

Use this mapping to import subsequent XML data into the same structure ·

Use this mapping to export XML data from the spreadsheet (conforming to the XML Schema) ·

Maintain and reuse the mapping for subsequent import and export operations, starting the process by adding an XSD file to the Excel workbook. Once you add an XSD file, Excel creates an XML map in the XML Source task pane (see Figure 1), which you can use for a specific range or single cell. Excel can also use this mapping to manage relationships between those mapping scopes and elements in the XML schema. A workbook can contain many XML mappings, each of which is a separate entity. Similarly, you can have multiple mappings for the same schema.

When you import or export XML data, Excel uses the mapping to associate the contents of a mapped range with elements in that schema.

Note A map must contain a single root element. If you add a schema that has more than one root element defined, you must select a single root element to use the mapping.

The XML Source task pane is a visual tool that enables Office solution developers to quickly set up how XML is used and generated in user-specific schemas

Spreadsheet of data.

Figure 1. XML Source task Pane >

The XML Source task pane consists of a tree view that lets you pick the XML elements you need and drag them into a cell in a logical grouping or a separate table.

Let's look at how to use XML data, schemas, and mappings from Excel in the XML Source task pane in more detail. You can use these procedures to examine the graphics in subsequent scenarios. Add an XML map to a workbook the first step in using XML data is to add a schema or mapping to the workbook.

1. On the View menu, click Task Pane to display the XML Source task pane.

-r-on the Data menu, point to XML, and then click XML Source.

2. Click XML Maps, and then click Add.

3. Enter the schema file, click Open, and then click OK.

This attaches the schema and adds an XML map to the workbook. You can now drag schema elements onto a spreadsheet to map them. Note that no data is added to the workbook. You need to import the data as a separate step after you mark the worksheet by mapping the required elements (see the following section).

Note You can add multiple schemas to the workbook. However, a single spreadsheet range can hold only one XML element. There can be no overlap between two or more XML elements.

Figure 1 shows an XML Source task pane that shows an XML map with the root node of root. EXCEL displays child elements in a hierarchical view.

1. Click Options to customize the mapping and toggle the mapping boundary. You can choose to preview the sample data in the task pane, display or hide the Help text in the task pane, and automatically merge the elements when you map. You can also specify that data be used as column headings when you map repeating elements to your worksheet.

2. Click XML Maps to display the XML Maps dialog box, which can be used to add, delete, or rename an XML map.

3. Click Verify Map for Export to verify that Excel can export data using the currently selected mappings. Using the XML Source task pane to map the XML Source task pane displays the XML schema in the workbook in a tree view. You can map elements by dragging them onto a worksheet, or you can right-click each element and select Map element. You can select nonadjacent elements by clicking an element, holding down the CTRL key, and clicking other elements. Once you have selected the elements, you can drag them to the location on the worksheet where you want them to appear. If the element is a repeating element, Excel creates an XML list in the cell where you added the element. To import data into a mapped range

Now let's import the XML data into the mapped grid.

Note If you use an XSD file during import or export, validation is not turned on by default. To turn on validation or select additional options related to the XML mapper, click the Data menu, click XML, and then click XML Map Properties.

1. Select a cell in the range of mappings where you want to import XML data.

2. On the Data menu, point to XML, and then click Import.

3. In the Look in list, click the drive, folder, or Internet location that contains the XML data file that you want to import.

4. In the Folder list, locate and open the folder that contains the file.

5. Click the file, and then click Import. XML mapping Scheme

Now that you see how to create a map from a schema and use that mapping to import data into a worksheet, the following sections explain how to map multiple schema structures to a grid.

The structure of the schema determines the mapping and appearance of the data after you import the data. The following sections provide several diagrams that illustrate the results of importing XML data and the XSD schema list.

For each scenario, it is recommended that you perform the following actions:

1. Use a text editor (for example, Notepad) to copy and paste XML and XSD lists into a separate file.

2. Import the XSD data into the XML Source task pane to create a map.

3. Drag and drop the element specified by the mapping (one or more) onto the worksheet cell.

4. Import data from an XML file.

By comparing these schemas to your own schemas, you can see the impact of some of the schemas on your data, or adjust them where you need them to fine-tune the graphics of your data to suit your needs. Create a tabular Report

User interface

Placing a Row or root element in cell B2 (using the XML Source task pane) produces the following table:

Figure 2. Table mappings

[1] [2] [3] Next page