Excel – Opening XML+XSD files in Microsoft Excel 2013

microsoft excelxml

I have found Excel 2013 supports opening, editing and saving XML files. I have created an XML file + XSD which Excel opens without complaints and errors. (I.e. it does not state it has to create schema from the XML data source)

However, Excel still does not respect e.g. restrictions setup in the .XSD file. Is there any way to have Excel do that? (I know Excel is not an XML editor, so I fully accept if it is not possible.)

file data.xsd :

<?xml version="1.0"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema" targetNamespace="http://example.com" elementFormDefault="qualified">


<xs:element name="data">
<xs:complexType>
<xs:sequence>


<xs:element name="item" minOccurs="0" maxOccurs="unbounded">
  <xs:complexType>
    <xs:all>

      <xs:element name="title" type="xs:string" minOccurs="0" maxOccurs="1">
      </xs:element>

      <xs:element name="type" minOccurs="0" maxOccurs="1">
        <xs:simpleType>
          <xs:restriction base="xs:string">
            <xs:enumeration value="header"/>
            <xs:enumeration value="normal"/>
          </xs:restriction>
        </xs:simpleType>
      </xs:element>

      <xs:element name="backgroundcolor" minOccurs="0" maxOccurs="1">
        <xs:simpleType>
          <xs:restriction base="xs:string">
            <xs:pattern value="#[0-9A-Fa-f]+"/>
          </xs:restriction>
        </xs:simpleType>
      </xs:element>

      <xs:element name="fontcolor" minOccurs="0" maxOccurs="1">
        <xs:simpleType>
          <xs:restriction base="xs:string">
            <xs:pattern value="#[0-9A-Fa-f]+"/>
          </xs:restriction>
        </xs:simpleType>
      </xs:element>


    </xs:all>
  </xs:complexType>
</xs:element>


</xs:sequence>
</xs:complexType>
</xs:element>


</xs:schema>

file test.xml :

<?xml version="1.0" encoding="UTF-8"?>
<j:data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://example.com data.xsd" xmlns:j="http://example.com">

        <j:item>
        <j:title>test title</j:title>
        <j:backgroundcolor>#aaf8941e</j:backgroundcolor>
        <j:fontcolor>#ffffff</j:fontcolor>
        <j:type>header</j:type>
        </j:item>

</j:data>

Best Answer

First of all, there are certain XSD data type attributes that are not supported by Excel. You can see a full list here.

To get Excel to associate your XSD properly with its corresponding XML looks like you'll have to create an XML map and then painstakingly map all the XML elements, as detailed here.

Honestly, I'd advise you to use a proper XML editor and save yourself the headache. :)

Related Question