Importing XML to Excel (problem with schema) RRS feed

  • Question

  • I've previsouly been using XML-sources as data input in Excel, but I'm having problems with the following XML file:

    I get the following error in Excel:
    The following schema elements and structures cannot be mapped to a worksheet. They will not be visible in the XML Source task pane.
    - any
    - anyAttribute
    - Substitution groups
    - Abstract elements
    - Recursive structures
    - Mixed Content

    So the problem seems to be the nested schemas in the XML file.

    I've tried downloading the XML-file to my harddrive, removing the XSD-references which at least allowed Excel to import the data.

    Does anyone have a suggestion to how I can import/map the XML-file in Excel?
    Is it possible to have Excel ignore the schemas?
    Is there another way to import the data in order to get automatic updates from the XML file online?

    I don't have much experience with XML schemas or parsing, etc.
    • Moved by Pawel Kadluczka Friday, May 1, 2009 10:15 PM This is more "how MS Excel uses Xml" rather Xml related question. (From:XML and the .NET Framework)
    • Moved by Cindy Meister MVP Saturday, May 2, 2009 7:57 AM not VSTO-related (From:Visual Studio Tools for Office)
    Friday, May 1, 2009 10:10 PM

All replies

  • I will move this post to Visual Studio Tools for Office forum.
    Friday, May 1, 2009 10:14 PM
  • I'm afraid the VSTO forum isn't the correct venue for this question. A group that specializes in the Excel application would be the better place to ask. You'll find a description of VSTO as well as links to places for non-VSTO, Office-related questions in the VSTO forum's Please Read First message.

    Please remember to always include the version information for an office application when asking a question. What's available and how to approach a problem can vary significantly between versions.

    FWIW, I'm not intimately familiar with Excel's XML specifications, but I seem to recall that there are some things it does not, and cannot, support in an XML schema. These limitations are due to the fact that Excel can really only display data as a "flat table", while XML supports other types of data structures (hierarchical). So if the schema in question contains anything like that (as the error message says), Excel cannot display it. Beyond that, you'll need to find an Excel person with a good grasp of how Excel works with schemas.
    Cindy Meister, VSTO/Word MVP
    Saturday, May 2, 2009 7:57 AM