PRB: BizTalk Editor Error When You Import Excel 2002 XML Worksheet

If you save an Excel 2002 workbook as an Extensible Markup Language (XML) file, when you use the BizTalk Editor to import the workbook, you receive the following error message:

When Excel generates an XML document, it creates several namespaces and uses the namespaces throughout the document. However, BizTalk Server does not support multiple namespaces.

Therefore, when the BizTalk Editor imports an Excel XML document, the BizTalk Editor must change the imported data so that it can be used with BizTalk Server. This change is referred to as name mangling. Because some attributes and elements are changed from their original state, the original data instance does not validate against the imported XML file.

To validate the original XML file against the schema that is generated during the import, you must modify the original XML file. You must remove all of the namespaces and any prefixes for all attributes and elements in the XML document.

To modify the original Excel XML document so that it can validate against the imported schema, follow these steps:

1. Excel defines the following namespaces in the WorkBook root element:

   xmlns="urn:schemas-microsoft-com:office:spreadsheet"
   xmlns:o="urn:schemas-microsoft-com:office:office"
   xmlns:x="urn:schemas-microsoft-com:office:excel"
   xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
   xmlns:html="http://www.w3.org/TR/REC-html40"
   						

   Remove all of these namespaces except for the following namespace:

   xmlns="urn:schemas-microsoft-com:office:spreadsheet"
   					

2. Remove any references to any of these namespaces. If any of these namespaces prefix any attributes or elements, remove the prefixes. For example, remove the x: from the x:FullColumns attribute.
3. Save your changes to the Excel XML file.
4. From the Tools menu, click Validate Instance to validate the modified data instance against the newly imported schema.
   
   NOTE: BizTalk may replace some of the prefixes with underscore characters (_).
5. When you try to validate in the BizTalk Editor, if you receive the following error message (or similar):
   The XML document has failed validation for the following reason:
   The attribute 'FullColumns' on this element is not defined in the DTD/Schema. Line:34, Position:17
   FullRows="1">
   
   you must review the schema to determine the correct name for the attribute. In this case, you must change FullColumns to x_FullColumns in the original Excel XML file.
   
   If multiple, similar errors occur, use the same procedure for all of the attributes or elements that these error messages list.

This behavior is by design.

Excel uses the SS and X namespaces. After you review the raw XML of the Excel document, you may notice that some attributes or elements are prefixed with these namespaces. When you import such a file, you must either remove the prefix or replace the colon (:) with an underscore character (_). For example, you must replace x:FullColumns in the original XML file with x_FullColumns in the imported schema.