Creating XML Mappings in Excel 2003

 

Frank Rice
Microsoft Corporation

February 2005

Applies to:
    Microsoft Office Excel 2003

Summary: Walk through common XML tasks in Microsoft Office Excel 2003 to learn more about the new XML functionality in Office 2003 Editions. Learn how to add custom schemas, work with XML maps in Excel, and create a series of mappings based on various schemas. (19 printed pages)

Contents

Introduction
Adding an XML Map to a Workbook
Mapping with the XML Source Task Pane
Importing Data into a Mapped Range
XML Mapping Scenarios
Creating Tabular Tables
Using Nested Elements
Creating Expense Report–Style Tables
Creating Relational Tables
Conclusion
Additional Resources

Introduction

Microsoft Office Excel 2003 improves support for using native XML by enabling you to add your own XML Schema Definition (XSD) to a workbook. This creates an XML map that you can use to make data more meaningful upon importing or exporting data. Thus, you can import data conforming to a custom XML vocabulary or schema, and then write the data back out by using the same XML schema.

This feature is targeted at Office solution developers, but expert users can also put it to good use. This feature enables you to:

  • Create a map between an Excel spreadsheet and an XML structure
  • Use that map to import subsequent XML data to that same structure
  • Use that map to export XML data from the spreadsheet, that conforms to that XML schema
  • Persist and reuse that map for subsequent import and export operations

The process starts by adding an XSD file to an Excel workbook. Once you add the XSD file, Excel creates an XML map in the XML Source task pane (see Figure 1) that you use to map to specific ranges or to a single cell. Excel also uses this map to manage the relationship between those mapped ranges and the elements in the XML schema. A workbook can contain many XML maps where each map is an independent entity. Likewise, you can also have multiple maps refer to the same schema. When you import or export XML data, Excel uses the map to relate the contents of a mapped range to elements in the schema.

Note   A map must contain a single root element. If you add a schema that defines more than one root element, you must choose a single root element to use with the map.

The XML Source task pane is a visual tool that enables Office solution developers to set up spreadsheets quickly that know how to consume and produce XML data in user-specified schemas.

Figure 1. The XML Source task pane

The XML Source task pane consists of a tree view that enables you to pick the XML elements you want and drag them into the cell grid in logical groupings and into separate tables.

Let's look more closely at working with XML data, schemas, and maps in Excel from the XML Source task pane. You can use these procedures to examine the shapes in the scenarios that follow.

Adding an XML Map to a Workbook

The first step in working with XML data is to add a schema or map to the workbook.

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

    –OR-

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

  2. Click XML Maps and then Add.

  3. Go to 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 the worksheet 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 up the worksheet by mapping the desired elements (see the following sections).

Note   You can add more than one schema to a workbook. However, a single spreadsheet range can only accommodate one XML element. There cannot be overlap between two or more XML elements.

Figure 1 displays the XML Source task pane showing an XML map with a root node of Root. Excel displays the child elements in a hierarchical view.

  1. Click Options to customize mapping and to toggle the mapping borders. You can choose to preview sample data in the task pane, show or hide help text in the task pane, and automatically merge elements when mapping. You can also specify to use data as column headings when you map repeating elements to your worksheet.
  2. Click XML Maps to display the XML Maps dialog box, which you can use to add, delete, or rename XML maps.
  3. Click Verify Map for Export to verify whether Excel can export data using the currently selected map.

Mapping with the XML Source Task Pane

The XML Source task pane displays the XML schemas in the workbook in a tree view. You can map elements by dragging them onto the worksheet, or you can right-click each element and choose Map element. You can select non-adjacent elements by clicking one element, holding down the CTRL key, and clicking additional elements. Once you select the elements, drag them to the worksheet location 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.

Importing Data into a Mapped Range

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

Note   If you are using an XSD file during importing or exporting, validation isn't turned on by default. To turn on validation or select other options pertaining to the XML mapper, click the Data menu, click XML, and then click XML Map Properties.

  1. Select a cell in the mapped range into which 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 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 Scenarios

Now that you see how to create a map from a schema and use that map to import data to a worksheet, the following sections illustrate how you can map various schema structures into a grid. The structure of the schema determines the mapping and appearance of the data once you import it. The following sections provide figures that illustrate the result of importing the XML data and the XSD schema listing. For each scenario, it is recommended that you:

  1. Copy and paste the XML and XSD listings into separate files using a text editor such as Notepad.
  2. Import the XSD data into the XML Source task pane to create a map.
  3. Drag and drop the designated element(s) of the map onto the worksheet cell.
  4. Import the data from the XML file.

By comparing these schemas to your own, you can see the effect of certain schemas on data or make adjustments where necessary to fine-tune the shape of your data to suit your needs.

Creating Tabular Tables

User Interface

Dropping the Row or Root elements onto cell B2 (using the XML Source task pane) results in this table:

Figure 2. Tabular mapping

XML Instance

<?xml version="1.0"?>
<Root 
  xmlns:xsi=http://www.w3.org/2001/XMLSchema-instance> 
  <Row>
    <Column1>A</Column1>
    <Column2>B</Column2>
    <Column3>C</Column3>
  </Row>
  <Row>
    <Column1>X</Column1>
    <Column2>Y</Column2>
    <Column3>Z</Column3>
  </Row>
</Root>

XSD

<?xml version="1.0"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
  <element name="Root">
    <complexType>
      <sequence>
        <element name="Row" maxOccurs="unbounded">
          <complexType>
            <sequence>
              <element name="Column1" type="string" />
              <element name="Column2" type="string" />
              <element name="Column3" type="string" />
            </sequence>
          </complexType>
        </element>
      </sequence>
    </complexType>
  </element>
</schema>

Using Nested Elements

User Interface

Dropping the Person or Root elements onto cell B3 results in this table:

Figure 3. Nested element mapping

XML Instance

<?xml version="1.0"?>
<Root  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <Person>
    <FirstName>Janet</FirstName>
    <Middle>Q</Middle>
    <LastName>Leverling</LastName>
    <Address>
      <Address1>123 Elm</Address1>
      <Address2></Address2>
      <City>Pleasantville</City>
      <State>MA</State>
      <Zip>12345</Zip>
    </Address>
  </Person>
  <Person>
    <FirstName>Robert</FirstName>
    <Middle>Q</Middle>
    <LastName>Fuller</LastName>
    <Address>
      <Address1>123 Main</Address1>
      <Address2></Address2>
      <City>Mayberry</City>
      <State>MA</State>
      <Zip>12345</Zip>
    </Address>
  </Person>
</Root>

XSD

<?xml version="1.0"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
  <element name="Root">
    <complexType>
      <sequence>
        <element name="Person" maxOccurs="unbounded">
          <complexType>
            <sequence>
              <element name="FirstName" type="string"/>
              <element name="Middle" type="string"/>
              <element name="LastName" type="string"/>
              <element name="Address">
                <complexType>
                  <sequence>
                    <element name="Address1" type="string"/>
                    <element name="Address2" type="string"/>
                    <element name="City" type="string"/>
                    <element name="State" type="string"/>
                    <element name="Zip">
                      <simpleType>
                        <restriction base="positiveInteger">
                          <pattern value="[0-9]{5}(-[0-9]{4})?"/>
                        </restriction>
                      </simpleType>
                    </element>
                  </sequence>
                </complexType>
              </element>
            </sequence>
          </complexType>
        </element>
      </sequence>
    </complexType>
  </element>
</schema>

Creating Expense Report–Style Tables

User Interface

Dropping the Meta element into cell B2, and then dropping the ExpenseItem element into cell B6, and results in this table:

Figure 4. Expense Report mapping

Dropping the Root element onto cell B2, results in the following variation:

Figure 5. Variation on Expense Report mapping

XML Instance

<?xml version="1.0"?>
<Root  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <Meta>
    <Name>Nancy Davolio</Name>
    <Date>2005-01-01</Date>
    <SAPCode>0001</SAPCode>
  </Meta>
  <ExpenseItem>
    <Date>2005-01-01</Date>
    <Description>Airfare</Description>
    <Amount>500</Amount>
  </ExpenseItem>
  <ExpenseItem>
    <Date>2005-01-01</Date>
    <Description>Hotel</Description>
    <Amount>200</Amount>
  </ExpenseItem>
</Root>

XSD

<?xml version="1.0"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
  <element name="Root">
    <complexType>
      <sequence>
        <element name="Meta">
          <complexType>
            <sequence>
              <element name="Name" type="string"/>
              <element name="Date" type="date"/>
              <element name="SAPCode">
                <simpleType>
                  <restriction base="positiveInteger">
                    <pattern value="[0-9]{4}" />
                  </restriction>
                </simpleType>
              </element>
            </sequence>
          </complexType>
        </element>
        <element name="ExpenseItem" maxOccurs="unbounded">
          <complexType>
            <sequence>
              <element name="Date" type="date"/>
              <element name="Description" type="string"/>
              <element name="Amount">
                <simpleType>
                  <restriction base="decimal">
                    <pattern value="[0-9]+(.[0-9]{2})?"/>
                  </restriction>
                </simpleType>
              </element>
            </sequence>
          </complexType>
        </element>
      </sequence>
    </complexType>
  </element>
</schema>

Creating Relational Tables

User Interface

This is how the grid appears if you drop the Customer or Root elements onto cell A3:

Figure 6. Relational tables mapping

Alternately, you can create three separate tables by:

  • Dropping the Customer element onto cell A3 (with only Customer/Id, Customer/Name, Customer/Address selected).
  • Dropping the Order element onto cell A7 (with only Customer/Order/Id and Customer/Order/Date selected).
  • Dropping the Product element onto cell A13 (with only Customer/Order/Product/Name and Customer/Order/Product/Quantity selected).

Figure 7. Multi-table view of relational table mapping

XML Instance

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Root
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <Customer>
    <Id>1</Id>
    <Name>Janet Q. Leverling</Name>
    <Address>
      <AddressLine>123 Elm</AddressLine>
      <City>Pleasantville</City>
      <State>WA</State>
      <zip>12345</zip>
    </Address>
    <Order>
      <Id>1</Id>
      <Date>3/2/01</Date>
      <Product>
        <Name>Widget</Name>
        <Quantity>3</Quantity>
      </Product>
      <Product>
        <Name>Sprocket</Name>
        <Quantity>7</Quantity>
      </Product>
    </Order>
    <Order>
      <Id>2</Id>
      <Date>4/7/01</Date>
      <Product>
        <Name>Widget</Name>
        <Quantity>1</Quantity>
      </Product>
    </Order>
  </Customer>
  <Customer>
    <Id>2</Id>
    <Name>Nancy Davolio</Name>
    <Address>
      <AddressLine>123 Main</AddressLine>
      <City>Mayberry</City>
      <State>WA</State>
      <zip>12345</zip>
    </Address>
    <Order>
      <Id>3</Id>
      <Date>5/1/01</Date>
      <Product>
        <Name>Widget</Name>
        <Quantity>5</Quantity>
      </Product>
      <Product>
        <Name>Sprocket</Name>
        <Quantity>2</Quantity>
      </Product>
    </Order>
    <Order>
      <Id>4</Id>
      <Date>5/8/01</Date>
      <Product>
        <Name>Widget</Name>
        <Quantity>4</Quantity>
      </Product>
    </Order>
  </Customer>
</Root>

XSD

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <xsd:element name="Root">
         <xsd:complexType>
              <xsd:sequence>
                  <xsd:element minOccurs="0" maxOccurs="unbounded" name="Customer">
                  <xsd:complexType>
                      <xsd:sequence>
                          <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Id" />
                          <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Name" />
                          <xsd:element minOccurs="0" maxOccurs="1" name="Address">
                          <xsd:complexType>
                             <xsd:sequence>
                                <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="AddressLine" />
                                <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="City" />
                                <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="State" />
                                <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="zip" />
                             </xsd:sequence>
                          </xsd:complexType>
                          </xsd:element>
                          <xsd:element minOccurs="0" maxOccurs="unbounded" name="Order">
                          <xsd:complexType>
                             <xsd:sequence>
                                <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Id" />
                                <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Date" />
                                <xsd:element minOccurs="0" maxOccurs="unbounded" name="Product">
                                <xsd:complexType>
                                   <xsd:sequence>
                                      <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Name" />
                                      <xsd:element minOccurs="0" maxOccurs="1" type="xsd:string" name="Quantity" />
                                   </xsd:sequence>
                                </xsd:complexType>
                                </xsd:element>
                            </xsd:sequence>
                        </xsd:complexType>
                        </xsd:element>
                    </xsd:sequence>
                </xsd:complexType>
            </xsd:element>
        </xsd:sequence>
      </xsd:complexType>
  </xsd:element>
</xsd:schema>

Creating Multi-Dimensional Tables

User Interface

Here is how the grid appears when you drop the FlightReport or Root elements onto cell A3:

Figure 8. Multidimensional mapping

XML Instance

<?xml version="1.0"?>
<Root  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <FlightReport altitude="1000" weight="10000">
    <Measurement>
      <Airspeed>350</Airspeed>
      <Vibration>125.6</Vibration>
      <Noise>62</Noise>
      <Drag>0.12</Drag>
    </Measurement>
    <Measurement>
      <Airspeed>400</Airspeed>
      <Vibration>99</Vibration>
      <Noise>75</Noise>
      <Drag>0.10</Drag>
    </Measurement>
  </FlightReport>
  <FlightReport altitude="2000" weight="10000">
    <Measurement>
      <Airspeed>350</Airspeed>
      <Vibration>125.6</Vibration>
      <Noise>62</Noise>
      <Drag>0.12</Drag>
    </Measurement>
    <Measurement>
      <Airspeed>400</Airspeed>
      <Vibration>99</Vibration>
      <Noise>75</Noise>
      <Drag>0.10</Drag>
    </Measurement>
  </FlightReport>
  <FlightReport altitude="1000" weight="15000">
    <Measurement>
      <Airspeed>350</Airspeed>
      <Vibration>125.6</Vibration>
      <Noise>62</Noise>
      <Drag>0.12</Drag>
    </Measurement>
    <Measurement>
      <Airspeed>400</Airspeed>
      <Vibration>99</Vibration>
      <Noise>75</Noise>
      <Drag>0.10</Drag>
    </Measurement>
  </FlightReport>
  <FlightReport altitude="2000" weight="15000">
    <Measurement>
      <Airspeed>350</Airspeed>
      <Vibration>125.6</Vibration>
      <Noise>62</Noise>
      <Drag>0.12</Drag>
    </Measurement>
    <Measurement>
      <Airspeed>400</Airspeed>
      <Vibration>99</Vibration>
      <Noise>75</Noise>
      <Drag>0.10</Drag>
    </Measurement>
  </FlightReport>
</Root>

XSD

<?xml version="1.0"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
  <element name="Root">
    <complexType>
      <sequence>
        <element name="FlightReport" maxOccurs="unbounded">
          <complexType>
            <sequence>
              <element name="Measurement" maxOccurs="unbounded">
                <complexType>
                  <sequence>
                    <element name="Airspeed" type="positiveInteger"/>
                    <element name="Vibration" type="decimal"/>
                    <element name="Noise" type="positiveInteger"/>
                    <element name="Drag">
                      <simpleType>
                        <restriction base="decimal">
                          <pattern value="[0-9]{1,2}.[0-9]{2}"/>
                        </restriction>
                      </simpleType>
                    </element>
                  </sequence>
                </complexType>
              </element>
            </sequence>
            <attribute name="altitude" type="positiveInteger"/>
            <attribute name="weight" type="positiveInteger"/>
          </complexType>
        </element>
      </sequence>
    </complexType>
  </element>
</schema>

Creating an Attribute–Centric Map

User Interface

Here is how the grid appears when you drop the FlightReport or Root elements onto cell A3:

Figure 9. Attribute-centric mapping

XML Instance

<?xml version="1.0"?>
<Root  
  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"> 
  <Employees EmployeeId="1" FirstName="Janet" LastName="Leverling" Title="Sales Representative"/>
  <Employees EmployeeId="2" FirstName="Nancy" LastName="Davolio" Title="Technical Advisor"/>
  <Employees EmployeeId="3" FirstName="Robert" LastName="Fuller" Title="Accountant"/>
</Root>

XSD

<?xml version="1.0"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
  <element name="Root">
    <complexType>
      <sequence>
        <element name="Employees" maxOccurs="unbounded">
          <complexType>
            <attribute name="EmployeeId" type="positiveInteger"/>
            <attribute name="FirstName" type="string"/>
            <attribute name="LastName" type="string"/>
            <attribute name="Title">
              <simpleType>
                <restriction base="string">
                  <enumeration value="Sales Representative"/>
                  <enumeration value="Technical Advisor"/>
                  <enumeration value="Accountant"/>
                </restriction>
              </simpleType>
            </attribute>
          </complexType>
        </element>
      </sequence>
    </complexType>
  </element>
</schema>

Obviously, there are as many possible formats as there are schemas to create them. The purpose of presenting this set of formatting possibilities is to give you a starting place for developing and fine-tuning your own schemas.

Conclusion

A schema enables you to specify which elements, attributes, data types, and hierarchies to allow in an XML data file. You can use schema files to validate XML data and to ensure predictability when exchanging data. Because of this predictability, you can adjust your schemas to ensure that your data displays correctly. In Excel, you use the XML Source task pane to map elements to cells. Then, it's relatively easy to import the XML data in a form best suited to your needs.

Additional Resources

See the following resources for additional information about using XML and XML maps to integrate data using Excel 2003: