Excel XML table headings with spaces

Question

I'm exposing tables in my system via a webservice to use within Excel worksheets (as an XML Source). The tables can have headers that contain spaces and other characters not valid in XML element names. Excel is fine with that, and my system is fine with that, but the intermediate format (XML) is not.

So, can the XML Source tell Excel (via schema or data) to use a table heading different from the element name? E.g. something like:

<xs:element name="place-of-origin">
  <xs:annotation>
    <xs:appinfo><od:displayName>Place of origin</od:displayName></xs:appinfo>
  </xs:annotation>
</xs:element>

Edit: here's a sample XML file and corresponding XSD. Opening this in Excel yields a table with a column heading “sex_of_person” — so the question is: how can the XML or schema express that this heading should be “sex of person” (with spaces), or “#$!%” (excuse me), or any other string not valid as an XML name?

Answer 1

You say you use your XML as a "data-source" thereby using the XML-functionality of Excel (beginning with version 2003).

When you open an XML-file in Excel, Excel offers you the option to transform it into a readable form (and giving you the option to automatically infere an XML-schema).

Once you have done this, Excel has -- in the background -- 'used' the XML-file (and the implicitly generated XML-schema, but you can use your excisting schema) as an XML-Source.

From now on for Excel the column title can be changed (Excel has an internal way to link sex_of_person from the XML file to the second column of the table).

To demonstrate this I have in my example deleted all the lines except one. Note how Excel selects the second row "Sex of person" (without the header) when I choose "sex_of_person" in the XML pane. Now when I click refresh (in my german version: "Daten aktualisieren") Excel reloads the XML file and inserts the values in the corresponding columns. Using "Import" you can even change the XML source (provided that the format stays the same).

So from now on I don't open the XML file in Excel (as you possibly have done) but I open this Excel-file that from now on defines the presentation (i.e. links from XML-element to a column) and includes the link to the XML-file.

I am not absolutely sure this is what you meant.

Regards Andreas