Map and unmap XML elements

You've heard how Excel and XML can work together by importing and exporting XML files. To import and export XML, you need to create an XML map that points XML elements to cells to get the results you want. To learn more about how to use XML with Excel, see an overview of XML in Excel.



Locate XML schema and XML data files

To start, you need an XML schema and XML data. There are several ways to obtain XML schema and XML data files.

Get the files from another database or application

You might already have 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 in another database or program. For example, a line-of-business application might export data into XML file format, a commercial web site or web service might supply an XML file, or a customized application developed by your IT department could create an XML file.

Convert a text file

If you have a text file you want to use, you can convert the text file to an XML file and then map it in Excel. For example, you can use Access and Excel to convert a text file to an XML file:

  1. Start Access.
  2. Create a linked table to the text file you want to convert.
  3. Export the data from the linked table to an XML data file and an XML schema file.
  4. Exit Access.
  5. Start Excel.
  6. Create an XML map based on the XSD file you exported from Access. If the Multiple Roots dialog box appears, make sure you choose dataroot to create an XML table. See the section Create an XML map for more information.
  7. Create an XML table by mapping the dataroot element. See the section Map XML elements for more information.
  8. Import the XML file you exported from Access. See the article Import XML data for more information.

Use sample files

If you don't have convenient access to XML files, the following sample XML files contain basic XML elements and structures you can use to test 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, and then copy and paste the sample text to Notepad.
  3. Save the file with the file name and extension you want.
  4. Exit Notepad.

Top of Page Top of Page

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 try to infer one from an XML data file (.xsd).

  1. Get started by clicking Source in the XML group on the Developer tab.

XML Refresh Data

If you don't see the Developer tab, do the following to display it:

  • In Excel 2013 and Excel 2010:
  1. Click File > Options.
  2. In the pane on the left, click the Customize Ribbon category.
  3. Under Main Tabs, check the Developer box, then click OK.
  • In Excel 2007:
  1. Click the Microsoft Office Button, and then click Excel Options.
  2. In the pane on the left, click the Popular category.
  3. Under Top options for working with Excel, check the Show Developer tab in the Ribbon box, then click OK.

The XML Source task pane appears.

  1. Click XML Maps, and then lick Add.
  2. In the Look in list, click the drive, folder, or Internet location that contains the file you want to open.
  3. Click the file, and then click Open. If you open:
  • An XML schema file, XML creates an XML map based on the XML schema. If the Multiple Roots dialog box appears, choose one of the root nodes defined in the XML schema file.
  • An XML data file, Excel tries to infer the XML schema from the XML data, and then creates an XML map.
  1. Click OK.

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

ShowIssue: When I add an XML map to my workbook, a message tells me that the XML schema can't be added to my workbook.

There are several types of XML schema constructs that Excel doesn't support. The following list details the XML schema constructs that can't be imported into Excel:

<any>    This element allows you to include elements that aren't declared by the schema.

<anyAttribute>    This element allows you to include attributes that aren't declared by the schema.

Recursive structures    A common example of a recursive structure is a hierarchy of employees and managers in which the same XML elements are nested several levels. Excel does not support recursive structures more than one level deep.

Abstract elements    These elements are meant to be declared in the schema, but never used as elements. Abstract elements depend on other elements being substituted for the abstract element.

Substitution groups    These groups allow an element to be swapped wherever another element is referenced. An element indicates it's a member of another element's substitution group through the <substitutionGroup> attribute.

Mixed content    This content is declared by using mixed="true" on a complex type definition. Excel doesn't support the simple content of the complex type but does support the child tags and attributes defined in that complex type.


Top of Page Top of Page

Map XML elements

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

  1. Click Source in the XML group on the Developer tab.

XML Refresh Data

If you don't see the Developer tab, do the following to display it:

  • In Excel 2013 and Excel 2010:
  1. Click File > Options.
  2. In the pane on the left, click the Customize Ribbon category.
  3. Under Main Tabs, check the Developer box, then click OK.
  • In Excel 2007:
  1. Click the Microsoft Office Button, and then click Excel Options.
  2. In the pane on the left, click the Popular category.
  3. Under Top options for working with Excel, check the Show Developer tab in the Ribbon box, then click OK.

The XML Source task pane appears.

  1. 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 that you want to map.
  2. To map the elements, do the following:
  1. Right-click the selected elements, and then click Map element on the shortcut menu.
  2. In the Map XML elements dialog box, select a cell, and then click OK.

 Tip   You can also drag the selected elements to the worksheet location where you want them to appear.

Each element appears in bold type in the XML Source task pane to indicate the element is 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, a smart tag with three commands is displayed, which you can use to control the placement of the heading or label:

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 the data or above the data).

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).

  • When you drag a repeating XML element onto the worksheet to create repeating cells in an XML table, the XML element names are automatically used as column headings for the table. However, you can change the column headings to any headings that you want by editing the column header cells.

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

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

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

ShowIssue: All the XML commands are dimmed, and I can't map XML elements to any cells.

Check to see if the workbook is shared. (On the Review tab, in the Changes group, click Share Workbook.)

After you create a shared workbook, you can no longer map XML elements to any cells. If you want to map XML elements in a workbook you want to share, you need to map the XML elements to the cells you want, import the XML data, remove all of the XML maps, and then create a shared workbook.


ShowIssue: I can't copy an XML table to another workbook.

An XML table (which contains data) has an associated XML map that defines the data structure. This XML map is stored in the workbook. However, when you copy an XML table to a new workbook, the XML map isn't automatically included. So, instead of creating an XML table, an Excel table is created that contains the same data. If you want the new table to be created as an XML table, do the following:

  1. Add an XML map to the new workbook by using the .xml or .xsd file you used to create the original XML map.

 Note   You should save these files if you want to add XML maps to other workbooks.

  1. Map the XML elements to the table to make it an XML table.

ShowIssue: When I map a repeating element to a merged cell, Excel unmerges the cell.

Although you can map nonrepeating elements to a merged cell, you can't map a repeating element to a merged cell. If you try to map a repeating element — or an element that contains a repeating element — to a merged cell, the cell is unmerged and the element is mapped to the cell where the pointer is located. This is the expected behavior, because repeating elements are designed to work only with unmerged cells.


Top of Page Top of Page

Unmap XML elements

Why would you unmap XML elements from a mapped cell or XML table? Maybe you've decided not to use them, or you don't want the contents of cells to be overwritten when you import XML data. For example, you'll want to unmap an XML element if it's currently mapped to a single-mapped cell or repeating cells that contain formulas and you don't want to overwrite the formula when you import an XML file. After you import XML data, you can remap the XML element to the cells that contain the formulas so you can export the results of the formulas to the XML data file.

  1. Click Source in the XML group on the Developer tab.

XML Refresh Data

If you don't see the Developer tab, do the following to display it:

  • In Excel 2013 and Excel 2010:
  1. Click File > Options.
  2. In the pane on the left, click the Customize Ribbon category.
  3. Under Main Tabs, check the Developer box, then click OK.
  • In Excel 2007:
  1. Click the Microsoft Office Button, and then click Excel Options.
  2. In the pane on the left, click the Popular category.
  3. Under Top options for working with Excel, check the Show Developer tab in the Ribbon box, then click OK.

The XML Source task pane appears.

  1. Right-click the element name, and then click Remove element..

Top of Page Top of Page

 
 
Applies to:
Excel 2013, Excel 2010, Excel 2007