Multiple Values from Excel cell to XML

Question

I need to turn an Excel file into a very specific XML file for a Microsoft Search Thesaurus.

This is a snippet of the Excel file: Excel Example

And this is an example of what the XML needs to look like: XML Example

I am finding it difficult to make the XML file because it has multiple of the same tag (i.e. sub) and Excel does not seem to like a maxOccurs="unbounded" in an XML map nor does it like multiple elements of the same name.

There is also the fact of having multiple values separated by commas in one cell.

This is my current XSD file if it helps:

<xs:schema attributeFormDefault="unqualified" elementFormDefault="qualified" targetNamespace="x-schema:tsSchema.xml" xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="thesaurus">
    <xs:complexType>
      <xs:sequence>
        <xs:element type="xs:byte" name="diacritics_sensitive"/>
        <xs:element name="expansion" maxOccurs="unbounded" minOccurs="0">
          <xs:complexType>
            <xs:sequence>
              <xs:element type="xs:string" name="sub" maxOccurs="unbounded" minOccurs="0"/>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:sequence>
    </xs:complexType>
  </xs:element>
</xs:schema>

Any help would be greatly appreciated.

Thanks, Clint

Answer 1

If anyone else is trying this I ended up putting each value I wanted into a separate column with a separate column name. And the each in the XML file was called , , etc. Then after I made the XML I use VBA to edit the file and replace the with .