Excel mapping to XML Schema

While OpenXML’s WordProcessingML has the capability to attach a custom XML instance and data bind with Content Controls, Excel achieve similar goal (similar only, not exactly) by attaching a XML schema (from xl\xmlMaps.xml file) and have xpath mapping to cells. Please refer to my previous post, I can even map a sequence of XML elements as a table in Excel. This post is more like a beginner’s guide to how all this work.

1. Enable Developer tab in the Ribbon UX. Click on the Office button and open Excel options.

2. Click on the Popular tab, and check "Show

Now the Developer is visible

3. Now click on the Source button to open up the XML Source task pane on the right.

4. Click on the XML Maps button on the bottom to open up XML Maps window

5. The click on Add button and attach the XML Schema (.xsd) file you want.

6. Click OK at XML Maps window and you will see the XML Source task pane XML tree got populated with the elements of your XML schema.

7.  To map the elements onto the Excel sheet, I just drag and drop the elements onto the cell. Should I want to add the item list as a table, I drag the item over.

8. Let me populate the sheet with some data.

9. Now to export the data I key in just now as a XML instance of Claims, click on the Export button on the Developer tab.

10. Give a proper name to the XML file in the Save dialog, then open it with IE.

 

Wala!! You can see data in the XML file is same as the one in Excel. So you can export this XML instance to any Line of Business application for data processing.