How to open XML files in Excel 2003 (827294)

MORE INFORMATION

Structured Data

XML is a meta-markup language that provides a format for describing structured data. XML is a subset of the Standard Generalized Markup Language (SGML) that is optimized for delivery over the Web.

XML is a flexible text format that is derived from SGML (ISO 8879) as defined by the World Wide Web Consortium (W3C). The W3C oversees the implementation of XML so that it remains independent of applications and vendors. For more information about the W3C and XML, visit the following W3C Web site: In XML, you can define an unlimited set of tags. Tags are used to describe the type of data that is enclosed. The following XML example is a weather report:

<?xml version="1.0" ?>
<weather-report>
   <date>March 25, 1998</date>
   <time>08:00</time>
   <area>
      <city>Seattle</city>
      <state>WA</state>
      <region>West Coast</region>
      <country>USA</country>
   </area>
   <measurements>
      <skies>partly cloudy</skies>
      <temperature>46</temperature>
      <wind>
         <direction>SW</direction>
         <windspeed>6</windspeed>
      </wind>
      <h-index>51</h-index>
      <humidity>87</humidity>
      <visibility>10</visibility>
      <uv-index>1</uv-index>
   </measurements>
</weather-report>

How to Open Files in Excel

When you open an XML file in Excel, you are prompted to select one of the following methods to import the XML data:

• As an XML list
• As a read-only workbook
• Use the XML Source task pane

Open the File As an XML List

When you open an XML source, Excel looks for a tag for an XML style sheet (XSL). The XSL describes how the data is to be presented. If the tag is present, Excel prompts you with a choice to apply the style sheet or not. If you select to apply an XSL, the XSL dictates how to present the data; in this case, the remainder of the information in this section of this article does not apply.

If there is no XSL tag, you receive the following message:
The specified XML source does not refer to a schema. Excel will create a schema based on the XML source data. Excel then imports the data in the XML source. If the XML data contains duplicate fields inside an element, Excel spreads the data over multiple rows. Each row contains one unique set of data. This can cause a small set of data in XML to be displayed as a larger number of rows in Excel.

Open the File As a Read-Only Workbook

When you select to open an XML source document as a read-only workbook, the first tag of the element is used like a title and is placed in cell A1. The rest of the tags are sorted alphabetically and placed across the second row. The weather report example earlier in this article will be sorted into the following list of column headings when you open it as a read-only workbook:

• /area/city
• /area/country
• /area/region
• /area/state
• /date
• /measurements/h-index
• /measurements/humidity
• /measurements/skies
• /measurements/temperature
• /measurements/uv-index
• /measurements/visibility
• /measurements/wind/direction
• /measurements/wind/windspeed
• /time

If there is one set of data, the data for each of these columns is put on the third line of the spreadsheet. If the XML data contains duplicate fields inside an element, Excel spreads the data over multiple rows. Each row contains one unique set of data. This can cause a small set of data in XML to be displayed as a larger number of rows in Excel.

Use the XML Source Task Pane

The schema of the XML data file is displayed in the XML Source task pane. You can then drag elements of the schema to the worksheet to map those elements to the worksheet.

If you are opening an XML data file that does not refer to a schema, Excel infers the schema of the XML data file.