excel to xml

Here is how to export excel to xml:

1) In notepad, create an XML Schema file using the following as a template.  Save the file as *.xsd:

<?xml version="1.0"?>
<schema xmlns="http://www.w3.org/2001/XMLSchema">
  <element name="YourRootElement">
    <complexType>
      <sequence>
        <element name="Row" maxOccurs="unbounded">
          <complexType>
            <sequence>
              <element name="YourChildElement1_willmaptoColumn1" type="string" />
              <element name="YourChildElement2_willmaptoColumn2" type="string" />
            </sequence>
          </complexType>
        </element>
      </sequence>
    </complexType>
  </element>
</schema>

2) Open your Excel spreadsheet and bring up the XML Source window (Data/Xml/Xml Source)

3) In the XML Source window click on the “XML Maps” button at the bottom.  This will bring up the XML Maps window.

4) In the XML Maps window, click “Add”.  The Select XML Source window will open.  Find the “Files of type” dropdownlist at the bottom and change it to say “All Files”.  Browse to the location where you saved your *.xsd file you created in step 1 and click “Open”.  This will bring you back to the XML Maps window and your file should show under “XML maps in this workbook”.  Click “OK”.  This will add the map to the XML Source:

5) Now we need to connect the elements in the XML Map to the columns in the spreadsheet.  Right click on Elem1 and select “MapElement”

6) The “Map XML Elements” will pop up.  Enter or select the range of rows you want to export for Column1:

7) Repeat steps 5 and 6 for Elem2:

8) Once you’ve mapped each element click on the link “Verify Map for Export” at the bottom of the XML Source window:

9) In the Excel spreadsheet select Data/XML/Export.  A pop up window will allow you to specify the location and name of the output XML file. 

10) That’s it.  The XML file will be generated in the location you specified. 

11) Check the XML file for any special characters (such as ampersands) that you may need to escape. 

12) Also, you may have to do a mass replace on other invalid characters such as “- <Row“ and replace them with “<Row“.

13) Check the very first row in your spreadsheet and verify it was exported over.  If it wasn’t, then insert it manually.