Importing XML Data into a Worksheet
XML (Extensible MarkUp Language) is a markup language that is used just like its cousin, HTML (HyperText Markup Language) to render Web pages on the Internet. This means that it uses codes called tags to define a document's structure and appearance. Unlike HTML, whose tags are all predefined and set in stone (at least until a new version comes out), XML is extensible in the sense that you (well, actually not you, but a trained programmer) can define and create new tags as needed for any particular project.
Another difference between HTML and XML is that XML actually describes the structure and meaning of its data whereas HTML defines only how its data looks (and beauty, as they say, is only skin-deep). It is this quality that makes XML so valuable in terms of sharing data among different incompatible systems because it makes it easy to reuse the data wherever it's needed.
XML has three distinct types of files to accomplish its magic:
- XML Data files (using the .xml filename extension) containing your data plus XML tags that describe its meaning and structure
- XML Schema files (using the .xsd filename extension) defining the rules for what you can and can't put in your XML data files
- XML Transform files (using the .xsl filename extension) that enable the use of the XML data in a variety of programs or files and can automate data exchange between different applications and control its visual display
The XML features I describe in this section - except for the option of saving files in the XML spreadsheet format - are available only if you're using Excel 2003 either as part of the Microsoft Office Professional Edition or purchased as a stand-alone unit. If you aren't using this version, you can't import XML data files.
In Excel 2003, you need to create an XML map that links certain cells in your worksheet to the schema used by your XML file. If an outside source such as your IT department hasn't provided you with an XML schema file, Excel infers one from the structure of the XML file itself. You can then use the schema that Excel creates to make your map and into which you then load the XML data as an Excel 2003 list.
You can't save a workbook as an XML file until you create an XML map.
When you first open an XML data file in Excel, the Open XML dialog box appears. This dialog box gives you the following choices to open the file:
- As an XML List: Select this option to view the XML data as an Excel 2003 list.
- As a Read-Only Workbook: Select this option to open the XML data file in read-only mode - a mode that doesn't enable you to save any changes you make to the original file.
- Use the XML Source Task Pane: Select this option to map the data to a schema into which you can then load its data.
To see how easy you can generate an XML schema and then use it to create an XML map into which to load its data, follow along with these steps:
- Open a new workbook in Excel and then choose File → Open.
- Select XML Files (*.xml) in the Files of Type drop-down list, select the XML file you want to open in the Look In list box, and then click the Open button.
This action opens the Open XML dialog box, where you indicate how to open the XML file. - Select the Use the XML Source Task Pane option and then click OK.
An alert dialog box appears if the XML data file does not refer to an XML schema file or the file does not exist. This dialog box indicates that Excel will create the necessary schema from the XML source data. - Click OK.
The XML Source task pane appears on the right side of the Excel window. Note that the XML Source task pane shows the schema created from the XML data file and that a floating List toolbar appears in the body of the worksheet. You then create the XML map by dragging the pertinent fields from the XML Source task pane to the desired cells in the worksheet. - Enter labels in the worksheet to identify the fields in the schema that you want to map. Enter these labels in the cells above or to the left of those cells where you intend to map their fields.
- Drag the name of the field from the schema to the cell in the worksheet (next to its identifying label) where you want the data to appear.
- Make sure that one of the mapped cells is selected and then choose Data → XML → Import to open the Import XML dialog box.
- Select the XML file whose data you want to import into the worksheet with the XML map in the Look In drop-down list and then click
the Import button.
Excel imports the data from the XML file into the appropriate mapped cells in the worksheet. - Choose File → Save As to open the Save As dialog box, select the appropriate folder, edit the filename, and click the Save button.
After saving the XML data as an Excel workbook, you can reopen it and refresh the data from the XML source at anytime. Note, however, that by default when updating data from an XML source, the program overwrites the original data in the worksheet. If you want Excel to add to the existing information, you must change this setting.
To do this, choose Data → XML → XML Map Properties to open the XML Map Properties dialog box. Select the Append New Data to Existing XML Lists option and click OK.
To refresh the data from an updated XML source file (assuming that it has the same name as the original file), open the worksheet containing the mapped XML data and then choose Data → XML → Refresh XML Data.
