Using Excel 2003 XML features for a Poor Mans Schema Generator

Do you have an XML document but don’t have an accompanying XSD Schema? There are a lot of great tools that will help you build a schema, however they all cost money. If you already own Excel 2003 you already own a schema generator. You may be suprised to find out that Excel already generates schemas for XML documents that don’t have defined schema. For example, assume you have the following XML:

In Excel 2003, select the Data menu, then click Import External Data, and finally click Import Data. You then  browse to the XML document you want to import.  Once I tell Excel which XML file to use, it gives me the following message:

Excel is telling us that its going to create a schema for this XML document since it doesn’t have one. Now this is very handy, as we can now use the nifty new Excel 2003 features in our workbook. Excel not only creates a schema, but imports the data as a List into the active workbook:

Excel does a great job allowing you to get XML into a workbook. That XML can come from a local file on your disk or if you have a URL to a web service, it can come remotetly import the data via HTTP. Their is one downside: Excel doesn’t give us a way to save the schema it created off to disk. It so happens that Excel internally stores the schema as part of the workbook. This is different from the Word model, where the schema is stored externally of the document.

Even though we can’t get to the schema through the Excel user interface, we can programmatically get to the schema. Before I show you how to do this in code, let me explain the XML Map. When you bring XML or a XSD schema into Excel, Excel creates an XML Map. An XML Map is an object that is a part of the workbook and defines the available XML elements and attributes. The XML Map defines not just the available elements, but where they are “mapped” into the workbook.  In other words, Excel is storing the schema data as meta information and how it relates to the workbook. This allows Excel to work with XML and at the same time keep it separate. It’s a great design that allows for a lot of flexibility. It kind of makes me wish that the Word team used a similiar map concept.

The XML Map corresponds to a an programmable object called XMLMap in the Excel object model. Attached to the workbook object, is an collection called XMLMaps. This collection returns each instance of an XML Map in our workbook.

The following code shows how I use the XMLMap object to save the XSD schema to the disk:

Note: You can download the sample and get the code in the file named Poormanxsd.zip (8 KB)

This code basically calls the first XML Map in the active workbook and saves the first schema as XML to disk.

It is really that simple! So the next time you need a schema, don’t forget you can use Excel. Granted, their might be easier ways, but its pretty cool that Excel will do the work for you.