Note XML features, except for saving files in the XML Spreadsheet format, are available only in Microsoft Office Professional Edition 2003 and Microsoft Office Excel 2003.
Working with XML Maps
When I add an XML map to my workbook, a message tells me the XML schema cannot be added to my workbook.
There are several types of XML schema constructs that Microsoft Office Excel 2003 does not support. The following list details the XML schema constructs that cannot be imported into Excel:<any> The <any> element allows you to include elements that are not declared by the schema.
<anyAttribute> The <anyAttribute> element allows you to include attributes that are not 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 that are more than one level deep.
Abstract elements Abstract 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 Substitution groups allow an element to be swapped wherever another element is referenced. An element indicates that it's a member of another element's substitution group through the <substitutionGroup> attribute.
Mixed content Mixed content is declared using mixed="true" on a complex type definition. Excel does not support the simple content of the complex type, but does support the child tags and attributes defined in that complex type.
I get a message saying the map cannot be exported.
An XML mapping cannot be exported if the mapped element's relationship with other elements cannot be preserved. This relationship may not be preserved for the following reasons:
- The schema definition of a mapped element is contained within a sequence for which the following are true:
- The maxoccurs attribute is not equal to 1.
- The sequence has more than one direct child element defined, or has another compositor as a direct child.
- Nonrepeating sibling elements with the same repeating parent element are mapped to different XML lists.
- Multiple repeating elements are mapped to the same XML list, and the repetition is not defined by an ancestor element.
- Child elements from different parents are mapped to the same XML list.
Additionally, the contents of an XML mapping cannot be exported if
it contains one of the following XML schema constructs:
- List of lists One list of items contains a second list of items.
- Denormalized data An XML list contains an element that has been defined in the schema to occur once (the maxoccurs attribute is set to 1). When you add such an element to an XML list, Excel fills the list column with multiple instances of the element.
- Choice This is a mapped element that is part of a <choice> schema construct.
I get a message saying the map can be exported but some required elements have not been mapped.
There may be several reasons for this message:- The XML map associated with this XML list contains one or more required elements that have not been mapped to the XML list.
The hierarchical list of elements in the XML source task pane indicates required elements by the red asterisk on the top right corner of the icon to the left of each element. To map a required element, drag it to the worksheet location where you want them to appear.
- The element is a recursive structure.
A common example of a recursive structure is a hierarchy of employees and managers in which the same XML elements are nested several levels. Although you may have mapped all the elements in the XML Source task pane, Excel does not support recursive structures more than one level deep and therefore cannot map all the elements.
- The XML list contains mixed content.
Mixed content occurs when an element contains a child element and simple text outside of a child element. One common case is where formatting tags (such as the bold tags) are being used to mark up data within an element. Although the child element
(if Excel supports it) can be displayed, the text content will be lost when the data is imported, and therefore cannot be round-tripped.
Working with mapped cells
All the XML commands are dimmed and I cannot map XML elements to any cells.
Check to see if the workbook is shared (Tools menu, Share Workbook command). Once 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 that you would also like to share, you must map the XML elements to the cells you want, import the XML data, remove all the XML maps, and then create a shared workbook.
I'm having problems copying an XML list to another workbook.
An XML list (which contains data) has an associated XML map
(which defines the data structure). This XML map is stored in the workbook. However, when you copy an XML list to a new workbook, the XML map is not automatically included. So, instead of an XML list, Excel creates an Excel list containing the same data. If you want the new list to be an XML list, you need to do the following:
- Add an XML map to the new workbook by using the .xml or .xsd file you used to create the original XML map.
Note It’s a good idea to save these files if you want to add XML maps to other workbooks.
- Map the XML elements to the list to make it an XML list.
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 cannot map a repeating element to a merged cell. If you attempt to map a repeating element — or an element that contains a repeating element — to a merged cell, Excel unmerges the cell and then maps the element to the cell where the pointer is located. This is the expected behavior, as repeating elements are designed to work only with unmerged cells.Importing XML data
I get errors when I import XML data.
Excel displays the XML Import Error dialog box when it cannot validate data according to the XML map. Click the Details button in this dialog box for additional information about each error. The following table describes errors that you may encounter.
| Error | Meaning |
|---|
| Failed schema validation | You have selected
Validate data against schema for import and export in the XML Map Properties dialog box. The data failed to be validated against the specified XML map. However, the data was imported. |
| Some data was imported as text | Some or all of the data you imported was converted from its declared data type to text. If you want to use this data in a calculation, you must convert it from text to numbers or dates. For example, a date value converted to text will not work as intended in the YEAR function until you convert it to the Date data type. Excel converts data to text when:
- The data was expressed in a format that Excel is unable to support.
- The data is incompatible with Excel's internal representation of the XSD data type. To fix this problem, make sure your XML data is valid according to the XML schema by checking each data type declaration.
|
| XML parse error | The specified XML file could not be loaded by the XML parser.
Make sure the XML file has no syntax errors and that the XML is well-formed. |
| Cannot find an XML map that corresponds to the data | This problem can occur when you have selected more than one XML data file to import and Excel cannot find a matching XML map for one of the files. For the file named in the title bar of the dialog box, import an appropriate schema first, and then import the file again. |
| An XML list cannot be resized to accommodate data | You are attempting to add rows by importing or appending data to the XML list, but there is no room for the list to expand. An XML list can only expand from the bottom down. For example, there may be an object, such as a picture or even another list, immediately below the XML list that prevents the XML list from growing. Or, for the XML list to expand, it will exceed the Excel row limit of 65,536. To fix this problem, rearrange the lists and objects on the worksheet to allow the XML list to expand from the bottom down. |
When I open an XML file, I get the message, "The specified XML file does not refer to a schema".
You are opening an XML file that doesn't refer to an XML schema (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.). Microsoft Office Excel 2003 must create a schema based on the contents of the XML file in order to work with the XML data contained in the file.
If the schema that Excel creates is incorrect or insufficient for your needs, then you should create an XML schema file and then edit the XML data file to so that it refers to the schema.
Note You cannot export Excel's inferred schema as a separate XML schema data file (.xsd). Although there are XML schema editors and other methods for creating an XML schema file, you may not have convenient access to them or know how to use them. As an alternative, you can use the Excel 2003 XML Tools Add-in Version 1.1, which can create a schema file from an XML map. For more information, see Using the Excel 2003 XML Tools Add-in Version 1.1.
Follow these steps to remove the schema that Excel created from your workbook:
- On the Data menu, point to XML, and then click XML Source.
- Click XML Maps.
- Click the XML map that represents the schema that Excel created and then click Delete.
I am having problems importing multiple XML files that have the same namespace but different schemas.
In general when you are working with multiple XML data files and XML schemas, it's common to create an XML map for each schema, map the desired elements, and then import each XML data file to the appropriate XML map. The Import command (Data menu, XML submenu)
was designed so that if you import multiple XML files with the same namespace, you can only use one XML schema. If you use this command to import multiple XML files that have the same namespace but different schemas, you can get unexpected results. For example, data may get overwritten or the operation may fail.
If you want to import multiple XML files that have the same namespace but different XML schemas, you can use the Import Data command (Data menu, Import External Data submenu) which was designed so that multiple XML files with the same namespace can use multiple XML schemas. Excel will create a unique XML map and XML mapping for each XML data file you import.
Note If you
are importing multiple XML files that do not define a namespace, these will be treated as if they have the same namespace.
Exporting XML data
When I export XML data, a message tells me that the XML maps in my workbook are not exportable.
An XML mapping cannot be exported if the mapped element’s relationship with other elements cannot be preserved. This relationship may not be preserved for the following reasons:
- The schema definition of a mapped element is contained within a sequence with the following attributes:
- The maxoccurs attribute is not equal to 1.
- The sequence has more than one direct child element defined, or has another compositor as a direct child.
- Non-repeating sibling elements with the same repeating parent element are mapped to different XML lists.
- Multiple repeating elements are mapped to the same XML list, and the repetition not defined by an ancestor element.
- Child elements from different parents are mapped to the same XML list.
Additionally, the XML mapping cannot be exported if it contains one of the following XML schema constructs:
- List of lists One list of items contains a second list of items.
- Denormalized data An XML list contains an element that has been defined in the schema to occur once (the maxoccurs attribute is set to 1). When you add such an element to an XML list, Excel fills the list column with multiple instances of the element.
- Choice A mapped element is part of a <choice> schema construct.
When I save my workbook as XML data, a message tells me that Excel cannot save the workbook because it does not contain any XML mappings.
Excel cannot save your workbook in
the XML Data if you have not created one or more mapped ranges (mapped range: A range in an XML list that has been linked to an element in an XML map.).
If you have added an XML map to your workbook, follow these steps to map XML elements to your worksheet:
- On the Data menu, point to XML, and then click XML Source to open the XML Source task pane.
- 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.
- Drag the selected elements to the worksheet location where you want them to appear.
If you haven't yet added an XML map to your workbook, or if your data is not contained within an XML list, you can instead save your workbook in the XML Spreadsheet file format.
United Kingdom
Printer-Friendly Version
Hide All
