Page 1 of 21

Excel > XML: What's it all about ?

Overview Chances are, you've heard of XML, and you've probably heard some
reasons why you should use it to store your data. But what is XML,
exactly, and how does it work? This course explains the basics: what
XML is, what it does, and some of the benefits of using it.

To learn more about this course, read the overview in the center of this
page or the table of contents in the left column. Then click Next to
start the first lesson.

Learn the basics of XML, what it is and -

how it works, and then see how some of the programs in the Microsoft Office System help you use XML.

Goals
After completing this course you will be able to:

• Help plan an XML system.

• Use and follow basic XML terminology and concepts such as tags, schemas, and transforms.

• See how XML works in some Microsoft Office System programs.

About this course

This course includes:

• Five self-paced lessons and two practice sessions for hands-on experience.

• A short test at the end of each lesson; tests are not scored.

• A Quick Reference Card you can take away from the course.

Before you begin

The practices for this course require that you have Microsoft Office Excel 2003 and Microsoft Office Word
2003, or that you have Microsoft Office Professional Edition 2003. You cannot complete the practices by
using the versions of Excel and Word in Microsoft Office Standard Edition 2003.

Lesson 1: Why use XML?

So, why should you use XML? Because today's businesses

thrive on data, and that data can come from any number of
sources, and in any number of formats: databases, Web pages,
spreadsheet files, and e-mail, to name just a few. XML enables
you to work with more data from more sources, and to get
more from that data. You keep hearing about XML because it's
so useful.

Excel-XML_what's all about

 Page 2 of 21
XML enables you to use data from more sources and in more ways.

Use more data in more ways

Multiple data sources, multiple uses for the data from each source.

Suppose you run the human resources department of a medium-sized company. Your staff sees lots of resumés,
usually in the form of documents or e-mail messages.

Wouldn't it be great if you could automatically copy the names, addresses, and lists of job skills from those resumés?
And even better, if you could use your computers to match those skills with open positions? And best of all, if the
same process prepared a report on your department's performance for your manager?

You can do those things (and more) with XML. You can extract data from various original sources, store that data in
one place so you know just where to find it, and use the data again whenever and wherever you need it.

For example, do you need to report budget data for the past three years? However you store that data, if it's in XML,
you can select what you need and import it into a document, worksheet, or database — whatever the situation
requires.

Lesson 2: A quick look at XML data files

The key component in an XML system is the data. XML data

files contain your data and a set of codes called tags that
describe what the data means. You can create any tags you
need for your data. That's one reason that XML is so adaptable
and so useful that they named it extensible.

Data: the key component in any XML system.

Excel-XML_what's all about

 Page 3 of 21

XML tags describe what XML data means

Sample XML data.

Suppose you run a veterinary clinic and you want to use XML to store data for your various animal clients. Your XML
data files will contain the data for each animal. Each piece of that data is surrounded by a tag, and each tag describes
what the piece of data means. The combination of tag and data is called a node.

The illustration shows a sample XML data file for a cat named Izzy. The tags are the combinations of angle brackets
and text:

<CAT>, <NAME>, <AGE>

and so on.
Tags actually consist of two parts, an opening tag and a closing tag like so:

<BREED> … </BREED>

The forward slash (/) is what makes a tag a closing tag. The opening and closing tags surround any data, like so:

<BREED>Siamese</BREED>

In XML, the tags are designed to clearly describe every piece of data. If someone asks what all those tags mean, you
can say that they mean whatever you need them to mean. That's a part of what makes XML "extensible." In this
case, you know what "yes," "no," and "Izzl138bod" all mean.

Because the tags describe the structure and meaning of the data, then any computer program or system that
supports XML can understand that data and put it to use. For instance, you could load the cat's name and the owner's
name from the data into a vaccination report and a payment request simultaneously.

Those are just a couple of examples of how you can put XML to work. You can use your data in reports, Web pages,
and databases, and that's just for starters. When you need to exchange data, XML can meet almost any business,
scientific, or academic need.

Excel-XML_what's all about

 Page 4 of 21

Anatomy of an XML data file

More parts of an XML data file.

Declarations
In addition to data and tags, XML data files contain declarations, root elements, and attributes. The names may
sound mysterious, but after some brief definitions, you'll see that these are all simple and logical.
Root element
XML declarations are statements in the data file that identify it and other files it works with. In this illustration, the
first declaration states that the file is an XML file. (If that's not stated, programs don't know how to work with it.) The
Attribute
second declaration names a file that governs data structure for this file and other files of its sort. The third declaration
names a file that is used to control the appearance of the data, and that allows an XML system to reorganize the
Tags and data
data.

Root element means one tag that contains all the other tags, and all the data in each particular record. XML files
require root tags. For this vet's data files, the root tag is the individual animal client.

Attributes store information about the tags and the data in them. The types of information in attributes are defined
by the XML for each kind of tag. In this example, every animal client must have a patient number as an attribute of
the root element.

Tags and data are, of course, the principal components of the file. You can also think of tags and data as child
elements contained by the <CAT> … </CAT> root element.

All XML must stick to the rules

Rule Example
One tag must contain all other tags. In other words, your data file must
have a root element. The previous section discussed root elements. <cat>

<name>Izzy</name>

</cat>

If you provide an opening tag, you must provide a closing tag (with the
exception of empty tags, which we explain below). <cat> ... </cat>

Excel-XML_what's all about

 Page 5 of 21

All opening and closing tags must match. Correct

<cat> ... </cat>

Incorrect

<cat> ... </Cat>

Some tags, called empty tags, do not require closing tags because they Horizontal rule
include the closing forward slash as part of the tag. The example shows
tags for inserting a horizontal rule and a line break. <hr/>

Line break

<br/>

You must enclose all attribute values in single or double quotation marks. Correct

patient_id="389467"

Incorrect

patient_id=389467

All tags must be nested correctly. The sequence of closing tags must Correct
mirror the sequence of opening tags.
<cat>

<name>Izzy</name>

</cat>

Incorrect

<cat>

<name>Izzy</cat>

</name>

All entities must be declared. Now don't panic. An entity is a virtual

storage area. It can contain images, text, or characters such as the &copy; ©
copyright symbol. Here are the entities for the copyright symbol and each
angle bracket.
&lt; <

&gt; >

The basic rules for creating well-formed XML.

Excel-XML_what's all about

 Page 6 of 21

You may hear the phrase "well-formed XML" and wonder what that means. It's simple: XML is well-formed when it
follows a small set of strict rules.

For example, XML is case sensitive. It cares about capital letters. For XML data to be well-formed, the tags must all
use an identical mix of capital letters and small letters. So <CAT> ... </CAT> is well-formed, but <CAT> ...
</Cat> is not.

You don't have to know all the rules, but if you'd like to know them, they're listed in this table. Here's what you do
have to know: You can share XML data among users and systems only when that data is well-formed. If it is not well-
formed, your XML system stops working (which means that your business stops working).

But that's a good thing. Really. Because, if a block of XML data is not well-formed, chances are that it's also corrupt.
XML's innate ability to alert you early can save you from a variety of later problems, such as having to correct
inaccurate numbers in your reports or clean corrupted data out of your files.

In the practice session, you'll violate one of the rules for well-formed XML and see how Word 2003 handles that error.

Practice

Practice instructions appear in a separate window alongside Word.

Ready to get acquainted with some XML data? In this practice session, you'll see how easy it is to create an XML file in Word
2003. You'll also see how Word behaves when it encounters XML data that isn't well-formed.

Important To complete this practice session, you must use Microsoft Office Word 2003 or Microsoft Office Professional
Edition 2003. You cannot complete this session by using the version of Word in Microsoft Office Standard Edition 2003.

About the practice session

When you click Practice in Word at the bottom of this page, a blank document will download to your computer and open in
Word, and a separate window with practice instructions will appear alongside (see picture).

Tips

• If the practice instructions aren't visible, or disappear when you click in Word, click the Word Help taskbar button
and then click the Auto Tile button on the upper-left corner of the instructions.

• If the practice instructions cover up Word, click the Auto Tile button on the upper-left corner of the instructions.
Before you begin
Make sure to close Word if it is already running.

Start the practice

Click the Practice in Word button now

Excel-XML_what's all about

 Page 7 of 21

Test Yourself

Which of these helps make XML extensible?. The ability to create tags as needed to describe a piece
of data. You got it. If you need more tags to define your data, you're free to add them.

To create an XML file in Word 2003, you: Save a document as an XML file, Right! Start with Save As
(on the File menu), and in the Save As dialog box, select XML document from the Save as type list..

Lesson 3: A quick look at XML schemas

A second basic component in an XML system is a schema. A

schema may sound mysterious, but it's just a set of rules that
say what can, and what can't, be in the various parts of an XML
data file.

You use a schema to validate your data. For example, a

schema can help make sure that people don't enter text in a
box where they should put phone numbers

A schema: the second basic component in any XML system.

A rule by any other name?

Excel-XML_what's all about

 Page 8 of 21
Schemas contain rules that help ensure valid data.

Don't let the term intimidate you. Schema is just another word for structure or organization. In XML, a schema
contains the rules for what can and can't be in the various parts of an XML data file.

It's important to keep separate the rules in a schema and the rules for well-formed XML. The rules for well-formed
XML dictate how your resident expert or IT department has to build a data structure, including any schemas. A
schema is XML, and as such it must conform to the rules for well-formed XML.

In contrast, the rules in a schema dictate what can and can't reside in a given data structure. For example, a schema
may say that a certain tag can contain only dates and another can contain only prices. Without a schema, the date
field in your well-formed XML could contain text, and your price field could contain dates. Your XML would be like a
grammatical, but meaningless, sentence.

A schema can be contained in a data file, or it can be a separate file linked to the data file. Either way, whenever
you use a data file in concert with a schema, that data file is said to conform to that schema. If your IT department
creates a separate schema file, that schema file uses the file name extension .xsd. Data files use the file name
extension .xml.

Anatomy of a schema

Parts of an XML schema

Declarations

Optional sequence declaration (controls the order of the tags in the data file)

Data type

Excel-XML_what's all about

 Page 9 of 21

Schemas can become extremely complex, and your IT department will probably create them for you, after talking to
you about what types of data you need and how the XML system should work. Knowing what schemas look like will
help you talk to IT about them. The illustration shows a schema setting rules for the <CAT> ... </CAT> tag set
discussed in the previous lesson.

This schema contains a set of declarations. The declarations control the type of data that each tag can contain. In
this case, the sequence declaration also controls the order of the tags that reside inside the <CAT> root element.
Any data file that uses this schema must have its tags in the order shown. Elsewhere in the illustration, the data
type declarations control the type of data that each tag in a data file can contain: a string of letters, a number, or a
Yes/No choice. As you'd expect, tags in a data file cannot contain a different type of data than what the schema
declares. This is how a schema can help to validate your XML data.

(You may recall that an earlier section, “Anatomy of an XML data file,” also mentioned declarations. One of those
declarations stated which schema would govern the data file's content. So the two groups of declarations are
related, logically and functionally.)

So how does all this affect you? If you are a veterinarian, and you need to have your files contain additional
information about your animal clients, such as their color or markings, you would have the person in charge of your
XML add tags for that data. Adding those tags will also require changes to the schema for the file. If you do not
change the schema, the new tags will be considered illegal, and the system will stop working. Simple schema
changes may be very quick, but any schema change is important because it creates a new rule for your data.

Because you can change your schemas as your computing and data needs evolve, XML files can be adapted to new
situations and requirements without rebuilding the files from scratch. Like the tags, the schema is extensible.

Test Yourself
A schema enforces rules for well-formed XML. False. Good! The rules in a schema define what can
and cannot reside in a given data file. The rules for well-formed XML ensure that you can exchange your
data with other users and XML systems.
A schema contains _____: Declarations. Yes! The declarations in a schema control which tags and data
can and cannot reside in a data file.
All XML systems must use .xsd files. False. That's it. Schemas can be separate files that use the .xsd file
name extension, or they can reside in data files that use the .xml file name extension.

Lesson 4: A quick look at XML transforms

The third main component of a typical XML system is a

transform. A transform allows you to use the same data in
many different ways. The XML data that you have filed and
structured so carefully can now be put to use in reports,
databases, Web pages, and a growing number of other
applications. This is where XML can become very exciting.

A transform: the third basic component of any XML system.

Excel-XML_what's all about

 Page 10 of 21

Transforms convert your XML data

We've told you that XML, in addition to structuring and validating

data, also provides powerful ways to repurpose or reorganize that
data. The mechanism for doing that is called an Extensible Style Sheet
Language Transformation (XSLT), or more simply, a transform. (This
is why .xsl is the file name extension for a transform file.)

A transform provides a set of rules (yes, more rules) for converting

data described by one set of tags into data described by another set
of tags.

For example, say your sales department stores its data in Excel
workbooks, and your accounting department needs that information
imported into a database. A transform can write data from preselected
Transforms can automate data exchange. cells in the worksheet into the correct fields in the database.

A few ways to use transforms

Regardless of how you need to use your data, a transform can help.

Depending on the steps that are built into them, transforms act as data processors. For instance, you can
sort or filter data as you write it from your XML data store to a document or spreadsheet program.

So, you can publish a newsletter listing new business partners by state, but omitting their street addresses.
You can also use your XML products inventory to create a Web catalog without showing your supplier's unit
prices, or sort salespeople by region or manager when you generate the latest diagram of your organization.

What's more, transforms can also control your visual displays — the appearance of your data. For instance,
you can use a transform to print sales data so that it looks like it came from a spreadsheet program, even if
it didn't, or vice-versa.

In addition to creating Web pages, you can also use transforms to deploy multiple versions of a Web site.
You can store all your data in one place and create a set of transforms that make your data look good in

Excel-XML_what's all about

 Page 11 of 21
any number of Web browsers. When visitors come to your site, you detect their browser type and route
them to the appropriate version of your content. You can stop building and maintaining separate Web
content for each browser type, which of course saves time and money.

And because more companies, schools, and government agencies are adopting XML, software companies
are providing transforms that work with their programs. In the future, you'll be able to quickly find and
download transforms that either meet your needs or that your IT folks can easily adjust to suit your
purpose. This means that your first investment in XML will enable you to do more and more as time goes
by.

Anatomy of a transform

Like schemas, transforms are XML files, and they must

conform to the rules for well-formed XML. Transforms
can also be extremely complex, and writing them is a
job for professionals. Again, however, it will help you
get the results you want if you know enough about
transforms to help in planning them. The illustration
shows part of a transform that writes data from the
<CAT> file, discussed in previous lessons, into a table
on a Web page.

This example shows just one way to write a transform.

A transform used to convert data into a document or a
diagram would contain tags specific to the program and
the display context.

In case you were wondering, Extensible Style Sheet

Language isn't a language separate from XML. It's a
vocabulary of XML. A transform is just another XML
document.
One set of tags builds a table on a Web page.

One declaration brings XML data into the table.

Each table cell shows a specific piece of data.

Test Yourself

You use transforms to: Sort, filter, and perform other types of processing on data. Good! Keep in mind
that you can also use transforms to control the display of data.

You use transforms to convert data into XML. False. Indeed! Converting data into XML is a different
process, and that's the next lesson.

Like it or not, your business will have to write its own transforms. False. That's right. A growing
number of software providers are creating transforms that you can either use right away or adapt to do a
particular job.

Excel-XML_what's all about

 Page 12 of 21

Lesson 5: A quick look at XML in Microsoft Office

Professional Edition 2003

When you see how much XML can do, and will be
doing, you understand why several Office 2003
programs have XML built right in. Built-in XML makes
this powerful language much easier to use. See how
smoothly you can move from standard Office files to
their XML versions, and you'll know why XML is
gaining so much acceptance worldwide.

Put XML to work in Microsoft Office Professional Edition 2003.

More support than ever

Office 2003 programs support XML in ways that make sense for them.

Several of the programs in the Microsoft Office Professional Edition 2003 support XML. Each program
furnishes XML tools that coincide with the nature of the program itself. The earlier practice showed how
Word 2003 allows you to save files as XML. You can also attach schemas and transforms to your Word 2003
documents.

Excel 2003, on the other hand, uses the concept of a map for working with XML. You create a map by
linking the cells in a worksheet to elements in a schema. You can't save a workbook as XML unless you first
create a map (which you'll see how to do in the practice session for this lesson).

The best way to learn how an Office 2003 program supports XML is to open Help for that program and
search on "XML" for more information.

As for XML schemas, transforms, or data from other suppliers, Microsoft Office Professional Edition 2003
programs are ready to work with those.

Excel-XML_what's all about

 Page 13 of 21

Practice

Practice instructions appear in a separate window alongside Excel.

The first practice session showed you how Word 2003 works with XML. Now you'll see how Excel 2003
works with XML. In this practice session, you'll create an XML map for Excel, and you'll open an XML data
file in Excel as an XML list.

Important To complete this practice session, you must use Microsoft Office Excel 2003 or Microsoft
Office Professional Edition 2003. You cannot complete this session by using the version of Excel in Microsoft
Office Standard Edition 2003. In addition, you need to download a sample XML data file. Follow the
instructions below to download the sample XML data file:

Download an XML file for the practice

To complete this practice session, first you must download a sample XML data file called Expense
Report.xml. Follow these steps to download that file. It's best if you complete these steps without any
interruption.

1. To keep these instructions in front of you, print this page. Right-click the page, then click Print. In
the Print dialog box, click Preferences and set the orientation to Landscape. Click OK, then click
Print.
2. Next, click this link: Practice XML file. The link takes you away from this page of instructions to a
download page titled "Excel 2003 Sample: XML File."
3. On the right of that download page, click the Download button.

Note If you use Windows® XP Service Pack 2, you need to follow some additional steps. After
you click the Download button, the File Download - Security Warning dialog box appears. Click
Run. The Internet Explorer - Security Warning dialog box then appears. Click Run again. Skip
step 4 below and follow step 5 and step 6 to finish downloading the sample file.

4. In the File Download dialog box, click Open.

5. When asked whether you want to install the file, click Yes, and then click Yes to acknowledge the
license agreement. Type the name of a destination folder or browse to a destination folder, and
then click OK until you complete the installation process.

Excel-XML_what's all about

 Page 14 of 21
6. If you have completed these steps without interruption, you should see the "Excel 2003 Sample:
XML File" download page again. Click the Back button in your Web browser to return to this training
course page. If you can't return directly, you can use your browser history to get back to the course,
or you can go to Training on Office Online and open the course again, then return to this practice
page.
About the practice session
When you click Practice in Excel at the bottom of this page, a practice worksheet will download to your
computer and open in Excel, and a separate window with practice instructions will appear alongside (see
picture).

Tips

• If the practice instructions aren't visible, or if they disappear when you click in Excel, click the
Excel Help taskbar button and then click the Auto Tile button in the upper-left corner of the
instructions.

• If the practice instructions cover up Excel, click the Auto Tile button in the upper-left corner of
the instructions.
Before you begin
Make sure to close Excel if it is already running.

Start the practice

Click the Practice in Excel button now.

Excel 2003 Sample: XML File - How to use XML in Excel 2003

Excel 2003 > Working with Data > Working with XML.

Applies to
Microsoft Office Excel 2003
Note The information in this article assumes that you have a basic knowledge of
Extensible Markup Language (XML). If you'd like more basic information, see
Manage Information with XML in Microsoft Office Professional Edition 2003.
One of the most powerful features of Microsoft Office Excel 2003 is one you can't
really see: support for user-defined XML schemas. In plain English, that means you
can add XML schemas (a schema is just a structure) and data to your workbooks,
regardless of where the XML originated. Excel automates most of the processes for
you. All you need to do is open the file and decide how you want to use the data.
You can:

• View the data in an XML list Use this option when you want to see all
your data in one list. Lists act as cohesive units. If it helps, think of them as
worksheets within larger worksheets. You can manipulate the data in a list
separately from the surrounding worksheet. For example, you can sort data,

Excel-XML_what's all about

 Page 15 of 21

rearrange rows, and, to a limited extent, rearrange the columns in a list without
affecting the surrounding cells.
• View the data in a read-only workbook Use this option when you or
others need to view the data in a file but don't need—or want—to manipulate it.
Read-only workbooks can provide a fast way to create business reports and other
types of files that you don't want users to change. When you select this option,
Excel displays the data in a static grid.
• Create a map from the structure and import data into the map When
you use the XML Source task pane to create a map, you link or bind a cell in a
worksheet to an element in the XML file. Importing data into the map populates
the cells with data. This is the most flexible and powerful option for working with
XML data. For example, say your sales department stores its data as XML, and
you need to create a report that summarizes sales activity for each month. When
the sales department posts new data, you can import that data into a map or set up a
system that updates your worksheets automatically. This figure shows a worksheet
with a populated map. Excel surrounds the mapped cells with a blue border.

Open and work with an XML file

Follow these basic steps to view an XML file in Excel by using one of the methods
described earlier.
Note If you'd like to follow this procedure but don't have an XML file handy, you
can download this sample XML file. Follow the on-screen instructions, and place it
in a convenient place on your network or your computer's hard disk.

1. On the File menu, click Open.

2. In the Files of type list, select XML files (*.xml).
3. In the Look in box, navigate to the XML file you want to open, select the
file, and then click Open.

The Open XML dialog box appears.

Excel-XML_what's all about
 Page 16 of 21

4. Click one of the options in the Open XML dialog box, and then click OK.

Information in the following sections explains how to use each option:

As an XML list

If a message box appears, click OK to close it. Excel creates the list. Notice that
Excel adds all the data in the file to the list.

Depending on the structure of your source file, you may see a lot of repeated
data, such as names and dates. You can eliminate redundancies by using the Use
the XML Source task pane option when you open the file.

More information

The following links take you to more information about Excel and XML lists:

• Layout limitations of XML lists

• Troubleshoot lists
• About XML lists
• About lists

As a read-only workbook

Excel adds all the data in the file to the list and flattens the file, meaning it
displays the data in a standard grid instead of a list.

Depending on the structure of your source file, you may see a lot of repeated
data, such as names and dates. You can eliminate redundancies by using the Use
the XML Source task pane option when you open the file.

Use the XML Source task pane

The XML Source task pane appears and displays the schema (the structure) of
your XML file.
Excel-XML_what's all about
 Page 17 of 21

5. Drag the items (they're called elements) that you want to map from
the task pane to blank cells on your worksheet.
6. Select any one of the mapped cells. On the Data menu, point to XML, and
then click Import.

The Import XML dialog box appears.

7. Use the Look in list to locate the source file you want to import.
Select the file, and then click Import.
8. Click OK to display the map in the task pane.

More information
The following links take you to more information about XML maps:

• About XML maps

• About XML data bindings
• Unsupported XML schema constructs

Tip
The following steps demonstrate another way you can open the XML Source task
pane and use it to create an XML map:

1. On the Data menu, point to XML and then click XML Source.
2. In the XML Source task pane, click XML Maps, and then click Add.
3. Use the Look in list to locate the XML file that you want to open, and then
click Open.

If a message box appears, click OK to close it.

4. In the XML Maps dialog box, click OK to display the map in the XML
Source task pane.
5. Drag the items (they're called elements) that you want to map from the task
pane to blank cells on your worksheet.
6. Select any one of the mapped cells. On the Data menu, point to XML, and
then click Import.

The Import XML dialog box appears.

7. Use the Look in list to locate the source file. Select the file, and then click
Import.

About nonrepeating and repeating XML elements

When you create an XML map, you use two types of XML elements: single,
nonrepeating elements and repeating list elements. Single, nonrepeating elements
Excel-XML_what's all about
 Page 18 of 21

occur once in a given XML file, while repeating list elements can occur any number
of times. For example, if you create the expense report in the Practice exercise at the
end of this article, you'll use a combination of nonrepeating elements (such as an
employee name) and repeating list elements (such as a description of each expense
item and the cost of each item).
Keep these facts in mind:

• The XML Source task pane uses several icons to denote repeating list
elements. For the Practice exercise, pay attention to these:

Denotes a repeating parent element.

Denotes a repeating child element.

• Excel automatically formats repeating list elements as XML lists when you
drag the elements onto the worksheet. That makes it easy to distinguish between
repeating and nonrepeating elements, because lists automatically provide the
AutoFilter button.
• If you place repeating list elements next to each other on the same row, Excel
includes each adjacent element in a single large XML list.
• If you place repeating elements by themselves, or next to nonrepeating
elements, Excel formats them as individual XML lists.

Import, export, and save files as XML

If you've added an XML map to a worksheet, you can import data into that map at
any time. You can also export the data for others to use. Those users can apply their
own schemas to your data to suit their needs. For example, a user can create a chart
that uses and links to your data, so that the chart updates automatically whenever
you update the data.
Note To follow the steps in this section, your worksheet must contain an XML map.
For information about maps, see the steps in Use the XML Source task pane.

Import data

1. Select one of the mapped cells in a worksheet.

2. On the Data menu, point to XML, and then click Import. The Import XML
dialog box appears.
3. Use the Look in list to locate the file that you want to import, and then click
Import.

Export data

Excel-XML_what's all about

 Page 19 of 21

1. On the Data menu, point to XML, and then click Export. The Export XML
dialog box appears.
2. Use the Save in list to navigate to the folder to which you want to export the
data, and then click Export.

Save data

1. On the File menu, click Save As.

2. From the Save as type list, select XML data (*.xml).
3. Click Save.

Note When you use the Save As command, Excel also makes available the
older XML Spreadsheet file format. If you save your data using that format,
other users must have Microsoft Excel 2002 or later to work with your data.

More information
The following links take you to more information about importing and exporting
data:

• About exporting XML data

• Features and limitations of XML Spreadsheet format

Practice exercise: Create and populate a map

Need something a bit more concrete? No problem. If you haven't already, download
the sample XML file. You'll use the file to create a fictitious expense report. You'll
add a map to a worksheet and then populate the map with imported data.

Start by preparing the worksheet

1. Start Excel and open the sample file.

When the Open XML dialog box appears, click Use the XML Source task
pane, and then click OK.

2. In cell A1, type Employee, and in cell A3, type Manager.

3. In cell C1, type Department, and in cell C3, type Date Submitted.
4. Format the text as you like to give it emphasis.

Add the map

1. Drag the ns1:EmployeeName element from the XML Source task pane to
cell B1, and then drag the ns1:Department element to cell D1.
2. Drag the ns1:ManagerName element to cell B3, and then drag the ns1:Date
element to cell D3.

Excel-XML_what's all about

 Page 20 of 21

3. Under ns1:ExpenseItem, select ns1:Description and drag it to cell B5.

4. Still under ns1:ExpenseItem: Drag the ns1:Account element to cell C5,
drag ns1:Date to cell D5, and then drag ns1:Total to cell E5.
5. Save the file. This adds the map to the file and binds the data in the source
file to the map.

You're now ready to...

Import data into the map

1. Select any one of the mapped cells. On the Data menu, point to XML, and
then click Import.

The Import XML dialog box appears.

2. Use the Look in list to locate the sample file, and then click Import.

Excel populates the mapped fields. Notice that Excel automatically applies XML
list formatting to the repeating elements (in this case, the expense items) in the
map.

Practice

Practice instructions appear in a separate window alongside Excel.

The first practice session showed you how Word 2003 works with XML. Now you'll see how Excel 2003
works with XML. In this practice session, you'll create an XML map for Excel, and you'll open an XML data
file in Excel as an XML list.

Excel-XML_what's all about

 Page 21 of 21
Important To complete this practice session, you must use Microsoft Office Excel 2003 or Microsoft
Office Professional Edition 2003. You cannot complete this session by using the version of Excel in Microsoft
Office Standard Edition 2003. In addition, you need to download a sample XML data file. Follow the
instructions below to download the sample XML data file:

Download an XML file for the practice

To complete this practice session, first you must download a sample XML data file called Expense
Report.xml. Follow these steps to download that file. It's best if you complete these steps without any
interruption.

1. To keep these instructions in front of you, print this page. Right-click the page, then click Print. In
the Print dialog box, click Preferences and set the orientation to Landscape. Click OK, then click
Print.
2. Next, click this link: Practice XML file. The link takes you away from this page of instructions to a
download page titled "Excel 2003 Sample: XML File."
3. On the right of that download page, click the Download button.

Note If you use Windows® XP Service Pack 2, you need to follow some additional steps. After
you click the Download button, the File Download - Security Warning dialog box appears. Click
Run. The Internet Explorer - Security Warning dialog box then appears. Click Run again. Skip
step 4 below and follow step 5 and step 6 to finish downloading the sample file.

4. In the File Download dialog box, click Open.

5. When asked whether you want to install the file, click Yes, and then click Yes to acknowledge the
license agreement. Type the name of a destination folder or browse to a destination folder, and
then click OK until you complete the installation process.
6. If you have completed these steps without interruption, you should see the "Excel 2003 Sample:
XML File" download page again. Click the Back button in your Web browser to return to this training
course page. If you can't return directly, you can use your browser history to get back to the course,
or you can go to Training on Office Online and open the course again, then return to this practice
page.
About the practice session
When you click Practice in Excel at the bottom of this page, a practice worksheet will download to your
computer and open in Excel, and a separate window with practice instructions will appear alongside (see
picture).

Tips

• If the practice instructions aren't visible, or if they disappear when you click in Excel, click the
Excel Help taskbar button and then click the Auto Tile button in the upper-left corner of the
instructions.

• If the practice instructions cover up Excel, click the Auto Tile button in the upper-left corner of
the instructions.
Before you begin
Make sure to close Excel if it is already running.

Start the practice

Click the Practice in Excel button now.

Excel-XML_what's all about