Add, map, and unmap XML elements

 Note   XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.

When you work with XML maps, you do the following closely related tasks:

Locate XML schema and XML data files

Usually, you have access to XML schema (.xsd) (XML Schema: A formal specification, written in XML, that defines the structure of an XML document, including element names and rich data types, which elements can appear in combination, and which attributes are available for each element.) and XML data (.xml) files that were created from another database or application.

However, if you don't have convenient access to these files, the following sample XML files contain basic XML elements and structures that you can use to try out XML maps.

ShowSample XML data (Expenses.xml)

<?xml version="1.0" encoding="UTF-8" standalone="no" ?>
<Root>

  <EmployeeInfo>
    <Name>Jane Winston</Name>
    <Date>2001-01-01</Date>
    <Code>0001</Code>
  </EmployeeInfo>

  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Airfare</Description>
    <Amount>500.34</Amount>
  </ExpenseItem>

  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Hotel</Description>
    <Amount>200</Amount>
  </ExpenseItem>

  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Taxi Fare</Description>
    <Amount>100.00</Amount>
  </ExpenseItem>

  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Long Distance Phone Charges</Description>
    <Amount>57.89</Amount>
  </ExpenseItem>

  <ExpenseItem>
    <Date>2001-01-01</Date>
    <Description>Food</Description>
    <Amount>82.19</Amount>
  </ExpenseItem>

  <ExpenseItem>
    <Date>2001-01-02</Date>
    <Description>Food</Description>
    <Amount>17.89</Amount>
  </ExpenseItem>

  <ExpenseItem>
    <Date>2001-01-02</Date>
    <Description>Personal Items</Description>
    <Amount>32.54</Amount>
  </ExpenseItem>

  <ExpenseItem>
    <Date>2001-01-03</Date>
    <Description>Taxi Fare</Description>
    <Amount>75.00</Amount>
  </ExpenseItem>

  <ExpenseItem>
    <Date>2001-01-03</Date>
    <Description>Food</Description>
    <Amount>36.45</Amount>
  </ExpenseItem>

  <ExpenseItem>
    <Date>2001-01-03</Date>
    <Description>New Suit</Description>
    <Amount>750.00</Amount>
  </ExpenseItem>

</Root>
 

ShowSample XML schema (Expenses.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="1" name="EmployeeInfo">
          <xsd:complexType>
            <xsd:all>
              <xsd:element minOccurs="0" maxOccurs="1" name="Name" />
              <xsd:element minOccurs="0" maxOccurs="1" name="Date" />
              <xsd:element minOccurs="0" maxOccurs="1" name="Code" />
            </xsd:all>
          </xsd:complexType>
        </xsd:element>
        <xsd:element minOccurs="0" maxOccurs="unbounded" name="ExpenseItem">
          <xsd:complexType>
            <xsd:sequence>
              <xsd:element name="Date" type="xsd:date"/>
              <xsd:element name="Description" type="xsd:string"/>
              <xsd:element name="Amount" type="xsd:decimal" />
            </xsd:sequence>
          </xsd:complexType>
        </xsd:element>
      </xsd:sequence>
    </xsd:complexType>
  </xsd:element>
</xsd:schema>

ShowHow to save the samples as files

  1. Start Microsoft Notepad.
  2. Select the sample text, then copy and paste it into Notepad.
  3. Save the file with the file name and extension you want.
  4. Close Notepad.

Create an XML map

You create an XML map by adding an XML schema to a workbook. The schema can be copied from an XML schema file (.xsd), or Excel can attempt to infer one from an XML data file (.xsd).

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

The XML Source task pane is opened.

  1. Click XML Maps.
  2. Click Add.
  3. In the Look in list, click the drive, folder, or Internet location that contains the file you want to open.
  4. Click the file, and then click Open. If you open:
    • An XML schema file, XML creates an XML map based on the XML schema.
    • An XML data file, Excel tries to infer the XML schema from the XML data, and then creates an XML map.
  5. Click OK.

The XML map is displayed in the XML Source task pane.

Map XML elements

You map XML elements to single-mapped cells and repeating cells in XML Lists so you can create a relationship between the cell and the XML data element in the XML schema.

  1. On the Data menu, point to XML, and then click XML Source to open the XML Source task pane.
  2. To map one or more elements to your worksheet, select the elements in the XML Source task pane. To select nonadjacent elements, click one element, and then hold down CTRL and click each element.
  3. Drag the selected elements to the worksheet location where you want them to appear.

Excel bolds each element in the XML Source task pane to indicate that they are currently mapped.

  1. Decide how you want handle labels and column headings:
    • When you drag a nonrepeating XML element onto the worksheet to create a single-mapped cell, Excel displays a smart tag with three commands you can use to control the placement of the heading or label:

      Place XML Heading to the Left    Click this option to use the XML element heading as the cell label (to the left of the data).

      Place XML Heading Above    Click this option to use the XML element heading as the cell heading (above the data).

      My Data Already Has a Heading    Click this option to ignore the XML element heading, because the cell already has a heading (to the left of or above the data).
    • When you drag a repeating XML element onto the worksheet to create a repeating cells in an XML list, the XML element names are automatically used as column headings for the list. However, you can change the column headings to any heading that you want.

      In the XML Source task pane, you can click Options to further control XML list behavior:

      My Data Has Headings
          When this check box is selected, existing data can be used as a column headings when you map repeating elements to your worksheet.

      Automatically Merge Elements When Mapping    When this check box is selected, XML lists are automatically expanded when you drag an element to a cell adjacent to the XML list.

Unmap XML elements

You unmap XML elements from a mapped cell or XML list because you decide not to use them, or because you don't want the contents of cells to be overwritten when you import XML data. For example, you may want to unmap an XML element if it is currently mapped to a single mapped cell or repeating cells containing formulas, and you don't want to overwrite the formula when you import an XML file. After you import XML data, you can then re-map the XML element to the cells containing the formulas, so you can export the results of the formulas to the XML data file.

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

The XML Source task pane is displayed.

  1. Right-click on the element name.
  2. Click Remove an element.
 
 
Applies to:
Excel 2003