However, there are couple of limitations with Microsoft Excel

1. You cannot actually create XML files in Excel.
2. Excel has a limit of 255 characters per cell.

If your elements contain large amounts of data then Excel would not be a good choice for editing them. You can create XML files using a number of editors including Notepad and Visual Studio .NET. Or, if you want create XML files easily and without using HTML tags you can use a free utility I have developed called XmlCreator.

Excel requires a schema to edit XML data. If your XML is in the right format Excel can create a temporary schema based on the data it contains. This is the easiest way to edit XML using Excel. However, I did find it a little strange that Excel can create a schema based on the data in an XML file and yet cannot create a schema based on data in a worksheet. What I mean is that you cannot create an Excel spreadsheet and save the data as an XML data file.

This sample XML file is in the correct format. Notice that two rows are required for Excel to be able to create the temporary schema.

<?xml version="1.0" encoding="UTF-8"?>
<Dataroot>
  <Row>
    <Col1></Col1>
    <Col2></Col2>
    <Col3></Col3>
  </Row>
  <Row>
    <Col1></Col1>
    <Col2></Col2>
    <Col3></Col3>
  </Row>
</Dataroot>

Walk through of editing an XML file with Excel 2003:

1. Open the XML file using Excel. This can be accomplished a number of ways.
   • Right click on the XML file and select Open with Microsoft Office Excel.
   • Open Excel and drag and drop the XML file onto an empty workbook.
   • Open Excel and use the File Open dialog box to select the XML File. You will need to change the file type to XML Files (*.xml).
2. An open XML dialog box will appear: Please select how you would like to open this file.
   • As an XML list
   • As a read-only workbook
   • Use the XML Source task pane
     Select the first option As an XML list and click OK.
3. A second dialog box will appear: The specified XML source does not refer to a schema. Excel will create a schema based on the XML source data. Just click OK.
4. You should now see your XML data with the row elements in bold at the top. There should also be a blue border around your data, a blue asterik in the last row. This asterik is where you would add a new row. Excel's AutoFilter feature should be turned on. This is very handing for finding the data you want edit but it can be truned off by selecting Data, Filter, AutoFilter from the drop down menu.
5. Edit your data inside the blue border. This is the only thing that will be saved if you save the file as XML data.
6. To save your changes click the save button. The save dialog will come up with the default file name Book1.xls. Change the file type to XML Data (*.xml). Make sure not to select XML Spreadsheet (*.xml). You can browse to your original XML file and then click save.
7. Read the cautions section below before you begin and always make a backup copy first.

If all cells contain data, after you save the XML file from Excel it will look like this:

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<Dataroot xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <Row>
    <Col1>data</Col1>
    <Col2>data</Col2>
    <Col3>data</Col3>
  </Row>
  <Row>
    <Col1>data</Col1>
    <Col2>data</Col2>
    <Col3>data</Col3>
  </Row>
</Dataroot>

You will notice that Excel changes the first two lines. The standalone attribute in the XML declaration is set to yes and a namespace is added to the root element.

To see the temporary schema that Excel generates open the Visual Basic Editor and type Print ActiveWorkbook.XmlMaps(1).Schemas(1).Xml in the immediate window and press enter. The schema will be displayed.

Cautions

If you save a file as XML Data everything outside of the blue border and any formatting will not be saved.

You cannot add columns to the data from Excel. If you need to add a column close Excel, add the column using a text editor, and then reopen the XML file with Excel. However, you can delete a column from Excel.

Excel does not create element tags for cells that do not contain data. Therefore, make sure at least one cell in each column contains data or the column will be deleted if you save the file as XML Data.

You cannot use Excel formulas inside the blue border. You can create summaries outside the blue border but, they will be lost if you save the file as XML data.