Cannot Import "XML Spreadsheet 2003" using "XML Data Import" in XL07; "does not refer to a schema"

[travistubbs]

One of the features of Excel 2007 I have truly been enjoying is the XML Data Import feature (in the Data tab of the Ribbon). It has made reviewing and updating information of web-based information a LOT easier.

Unfortunately, when attempting to import an XML File over the web created in the Microsoft Excel "XML Spreadsheet 2003" file format, I get the error that "The specified XML source does not refer to a schema." Excel, as it says, attempts to create a schema, but fails miserably.

Upon reviewing the XML, I noticed that Microsoft takes a VERY different approach to rendering/creating XML data.

If I open the file normally (again, over the web) using the Open command, the file opens without any issues. This doesn't do me any good, though, as I am wanting to be able to manipulate the data in other Worksheets of a new Workbook.

My question is, how can I use the XML Data Import feature with XML Spreadsheet 2003 files so that I can map the XML data properly in a new Excel Workbook? Is there an alternate method to consider?


Sample Output of Generated XML Spreadsheet 2003 store on web

<?xml version="1.0"?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:x="urn:schemas-microsoft-com:office:excel" xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml" xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:html="http://www.w3.org/TR/REC-html40" xmlns:c="urn:schemas-microsoft-com:office:component:spreadsheet">
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office"></OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"></ExcelWorkbook>
 <Worksheet ss:Name="productsExport">
  <Table>
   <Row>
    <Cell>
     <Data ss:Type="String">store</Data>
    </Cell>
    <Cell>
     <Data ss:Type="String">sku</Data>
    </Cell>
    <Cell>
      <Data ss:Type="String">category_ids</Data>
    </Cell>
   </Row>
   <Row>
    <Cell>
     <Data ss:Type="String">Default</Data>
    </Cell>
    <Cell>
     <Data ss:Type="String">BLKT-SM624</Data>
    </Cell>
    <Cell>
     <Data ss:Type="Number">10</Data></Cell>
    <Cell>
   </Row>
  </Table>
 </Worksheet>
</Workbook>