Let’s see how Ellen created the map for the expenses.xls worksheet, using the expenses.xsd schema. She might have started from scratch, defining a map between the schema and a blank worksheet. However, since World-wide already had a worksheet design, she started with that.

To associate the worksheet with the schema:

The XML Source task pane is used during the mapping process to create the links between the XML element types and the worksheet cells. The top part of the XML Source task pane shows the general structure allowed by the schema, as shown in Figure 7-3.^[2]

Figure 7-3. The XML Source task pane

The task pane shows all the XML element types and attributes, organized into the hierarchical structure allowed by the schema. There are several things to notice about the way element types and attributes are displayed:

Once an element type or attribute is mapped, selecting it in the XML Structure task pane causes Excel to select its mapped cell(s) in the worksheet. Likewise, when you select a mapped cell in the worksheet, Excel selects the element type or attribute to which it is mapped in the task pane.

Element types and attributes can be mapped to cells by dragging them from the task pane to the worksheet. It is also possible to right-click the element type or attribute in the task pane. From the resulting context menu, click Map element.

During the mapping process, Excel makes a distinction between repeating and non-repeating element types. The latter only occur once within the XML structure, according to the schema. In our example, they include the element types that make up the header of the expense report, such as empName, empName, and billToCode.

Repeating element types may appear more than once. In our example, the expense items, such as explanation and mileage, are repeating. Even though they may only appear once in their parent element, expense, the parent may appear more than once, so the children are therefore considered repeating.

Excel looks at the maxOccurs attributes in the schema to determine whether an element type is repeating. The default for this attribute is 1. If an element type, or any of its ancestors, is declared with a maxOccurs value of more than 1 (including “unbounded”), it is considered to be repeating.

Attributes are considered repeating if they belong to repeating element types; otherwise they are non-repeating.

The mapping process differs depending on whether the element type or attribute is repeating or non-repeating, so we will discuss each separately.

Mapping non-repeating element types and attributes

You generally map a non-repeating element type or attribute to a single cell in the worksheet.^[3]

Let’s map the non-repeating element types of the schema to our expense report worksheet. To do this, drag each one to the cell that is to contain the data. For example, drag the empName element type onto cell A4.

This will cause a smart tag to appear next to the cell. Clicking it reveals three options, as shown in Figure 7-4:

• My Data Already Has a Heading

• Place XML Heading to the Left

• Place XML Heading Above

Figure 7-4. Heading-related options

These choices allow you to insert a heading for your cell. Since we already have column headers in our worksheet, we can choose the first option (the default). If you choose either of the other two options, the XML name will be placed in the cell to the left of or above the chosen cell.

Notice that the cell that we have mapped now has a blue border around it. Continue to map the rest of the non-repeating element types and attributes.

To change the map, you can right-click the name in the XML Structure task pane, and click Remove element. This will remove the link between that element type or attribute and the cell. You can then remap the element type or attribute to another cell.

You can export the data that is currently in the worksheet as an XML document by clicking Export on the XML submenu. This will save a document that conforms to the expenses.xsd schema.

Unlike Word, Excel is not constantly validating your data against the schema as you edit it. When you export it, though, it does validate it and it reports any errors it finds. These errors might include:

Excel will inform you about the first of the validation errors it encounters (not all of them), but it will save the exported XML anyway. An example of what is saved is shown in Example 7-3.

<?xml version="1.0" encoding="UTF-8"?>
<ns1:expenseReport xmlns:ns1="http://xmlinoffice.com/expenses">
  <ns1:empName>Ellen Sandler</ns1:empName>
  <ns1:empNum>305</ns1:empNum>
  <ns1:purpose>Sales meeting with ABC Corp.</ns1:purpose>
  <ns1:deptCode>305</ns1:deptCode>
  <ns1:billToCode>1104</ns1:billToCode>
  <ns1:periodFrom>2004-04-16</ns1:periodFrom>
  <ns1:periodTo>2004-04-18</ns1:periodTo>
  <ns1:expense>
    <ns1:date>2004-04-16</ns1:date>
    <ns1:explanation>Phone call from hotel</ns1:explanation>
    <ns1:mileage/>
    <ns1:airFare/>
    <ns1:perDiem/>
    <ns1:lodging/>
    <ns1:auto/>
    <ns1:taxi/>
    <ns1:phone>0.75</ns1:phone>
    <ns1:businessMeals/>
    <ns1:other>
      <ns1:code/>
      <ns1:amount/>
    </ns1:other>
  </ns1:expense>
       ...
  <ns1:expense>
    <ns1:date>2004-04-16</ns1:date>
    <ns1:explanation>Dry Cleaning</ns1:explanation>
    <ns1:mileage/>
    <ns1:airFare/>
    <ns1:perDiem/>
    <ns1:lodging/>
    <ns1:auto/>
    <ns1:taxi/>
    <ns1:phone/>
    <ns1:businessMeals/>
    <ns1:other>
      <ns1:code>887</ns1:code>
      <ns1:amount>16.75</ns1:amount>
    </ns1:other>
  </ns1:expense>
</ns1:expenseReport>

Notice that all possible elements are included in the exported XML. If a cell is empty, an empty element appears, rather than the element simply being omitted. This can sometimes cause a problem for elements with numeric or date types, for which an empty element is not valid.

New XML data can also be imported into the worksheet. This is accomplished by clicking Import on the XML submenu, and selecting the name of the XML file to import.

Importing XML data into an existing worksheet is quite different from simply opening an XML document in Excel, as we will see in 7.5.1, “Using a schema”, on page 148. When XML data is imported, Excel will place all the data from the XML document into the correct cells based on the maps.

For example, we can import a new expense file called new expenses.xml to see how the import works. To do this:

You will now see the new data in the worksheet. Note that the number of lines in the list range has been reduced, as shown in Figure 7-5.

Figure 7-5. Imported data

Preserving data and formatting

By default, importing XML data will overwrite the data that is currently in the cells, and it will alter the size of the list range (by adding or deleting rows) to accommodate the new data. It is also possible to append repeating data to what already exists, adding new rows to the list range while preserving the existing ones. This process can be controlled by a number of options that are available by clicking XML Map Properties from the XML submenu. The resulting dialog is shown in Figure 7-6.

Figure 7-6. The XML Map Properties dialog

The following options are available from this dialog:

• Validate data against schema for import and export. If this box is checked, Excel will validate imported and exported XML documents against the schema.

• Adjust column width. The column widths will be automatically updated to accommodate the width of the data that is imported. If you have carefully formatted your worksheet, you may wish to uncheck this box.

• Preserve column filter. The current options specified for the list range will not be overwritten on import.

• Preserve number formatting. The formatting of the numbers in cells will not be overwritten on import.

• Overwrite existing data with new data. All existing data will be deleted and new data inserted.

• Append new data to existing XML lists. New data will be appended to the existing data in the list range. This does not affect non-repeating elements, which will be overwritten.

Some XML documents, such as the expenses.xml document, shown in Example 7-1, use the xsi:schemaLocation attribute to specify the location of the schema. When you open such a document, Excel knows about the schema and it automatically brings it up in the XML Source task pane.

To open an XML document with a schema in Excel:

The schema structure automatically appears. The worksheet is a completely blank slate, and you can begin mapping to it.

For non-repeating element types, you can use the smart tag that appears to the right of a recently mapped cell to add a label for the data, namely the element-type name. For repeating element types, the list column headers are automatically populated with the element-type names.

You can map a parent element type and all its children in one step by selecting the parent and dragging it to the worksheet. For example, select expense and drag it to cell B2. This will create a list range starting at B2 which has a column for each child of expense. You can remove any unwanted columns from the list range by selecting a column (for example phone), right-clicking it, and clicking Remove element.

Once you have mapped some element types, you can populate the data to ensure that the map is correct. To do this, position your cursor in a mapped cell and click Refresh on the XML submenu. You can refresh as often as you like during the mapping process.

If you don’t have a schema, Excel will infer a schema from an XML document. If you attempt to open an XML document that does not make use of the xsi:schemaLocation attribute to indicate a schema, Excel will prompt you with a dialog saying “The specified XML source does not refer to a schema. Excel will create a schema based on the XML Source data.” It will then infer a schema from the XML document.

We do not recommend this approach. The problem is that Excel may not get the schema 100% right, because it has limited information. That is understandable, but there is no way to change the schema once it is inferred. It is embedded in the Excel workbook and you cannot edit it. One of the most common problems is that the schema inference tool assumes that some non-repeating element types are actually repeating. This wreaks havoc on the ability to map them to the worksheet.

To avoid writing a schema from scratch, you can use a separate tool to infer a schema from an XML document. Microsoft provides a free schema inference tool at:

http//apps.gotdotnet.com/xmltools/xsdinference

Once you have generated a schema, you will have the ability to fine-tune it before you begin mapping.

Dawn wants to import expense reports from several different employees into the same worksheet. The worksheet we created earlier in this chapter would not work, because it would overwrite all the non-repeating elements every time a new expense report was imported. Instead, Dawn needs a worksheet that will repeat all the normally non-repeating “header” information in each row, alongside the repeating expense item elements.

You can think of this as flattening the hierarchy. We are extracting the elements with data content, regardless of the level they are at, and presenting them in adjacent cells in the same rows of the spreadsheet.^[4]

To do this:

You now have a large list range with a column for each element type that can contain only data (i.e. that cannot contain elements). The column headings (element-type names) are shown in Table 7-1.

During import, the data in the non-repeating element types is repeated in each expense item row.

To import several expense reports at once:

This will import all the data from all of the expense reports, as shown in Figure 7-8.

Figure 7-8. Flattened data from multiple expense reports (expense data analysis.xls)

Caution

The flattened data cannot be exported using the map with which you imported it. Excel will warn that the map is not exportable because it contains denormalized data. However, you can export the data – or calculations based on the data – by creating a separate range of cells that reference the data using cell equations. This second range of cells can be mapped to a separate schema and exported.

Now that the data is combined, Dawn can use summary reports and charts to analyze it, as shown in Figure 7-9 and Figure 7-10. When she wants to update the analysis, she can simply import new XML data (or refresh the current data) and the summary report and charts will automatically be updated. A working version of this worksheet is in expense data analysis.xls.

Figure 7-9. Summary report

Figure 7-10. Data analysis using charts

Worldwide accumulates all of its expense reports in a database for long-term analysis of trends. The database format has a slightly different structure. Instead of individual mileage, airFare, etc. elements, it has a single repeating amount element with an expenseTypeCode attribute indicating the type of expense.

The corresponding XML document is shown in Example 7-4.

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<expenseReport xmlns="http://xmlinoffice.com/expenses">
  <empName>Ellen Sandler</empName>
  <empNum>305</empNum>
  <purpose>Sales meeting with ABC Corp.</purpose>
  <deptCode>305</deptCode>
  <billToCode>1104</billToCode>
  <periodFrom>2004-04-16</periodFrom>
  <periodTo>2004-04-18</periodTo>
  <expense>
    <date>2004-04-16</date>
    <explanation>Phone call from the hotel</explanation>
    <amount expenseTypeCode="801">.75</amount>
  </expense>
  <!-- ... -->
  <expense>
    <date>2004-04-16</date>
    <explanation>Dry Cleaning</explanation>
    <amount expenseTypeCode="883">16.75</amount>
  </expense>
</expenseReport>

The schema for this document does not map directly to the expense report worksheet because the data that belongs in the various expense type columns comes from the same amount element. Excel does not allow you to map, say, the “Phone” column to “all amount elements with an expenseTypeCode attribute whose value is 801”.

XSLT stylesheets can be used to create compatibility between the worksheet-friendly XML and the database-compatible alternative.

from worksheet to database

from database to worksheet

Paired transforms can also be used with other document types that don’t naturally fit into a tabular structure. For example, those with multiple levels of hierarchy, or that contain element types whose content must be combined or split apart for use in the worksheet.