XML schemas, Excel, and getting what you want

In my character generator, there is a lot of data.  There's races, classes, skill, feats, and powers.  On top of that, they're not static lists.  Wizards of the Coast (and other people) are always adding and expanding on everything.  With that in mind, I knew I needed to have all that data extensible in my character generator.  XML is the obvious choice, especially since Flex is so good at parsing it.

But how was I to get the data in the PHB into XML in the easiest way possible?  Microsoft Excel and XML Schemas is the answer.  The basic idea is that you enter all your data into Microsoft Excel as a big list.  Then you tell Excel to export it as XML.  That XML is then fed into the Flex app.  

XML schemas tell Excel how to format the XML you export.  With an XML schema, you're defining the basic format of your XML (in XML, ironically).  There's an article here that gives a good way to create a simple Xml Schema Definition (XSD) using Excel (see step 6, specifically).  That will give you the schema, and then you can attach that to your Excel spreadsheet and have Excel export XML for you.

But there's a HUGE caveat.  Excel cannot handle (that is, export, import, deal with at all) XML with a list of lists.  This looks something like this:

<feats>

  <feat name="a">

    <prerequisites>

      <prerequisite name="x"/>

      <prerequisite name="y"/>

    </prerequisites>

  </feat>

  <feat name="b">

    <prerequisites>

      <prerequisite name="p"/>

      <prerequisite name="q"/>

    </prerequisites>

  </feat>

</feats>

We've got a list of feats, each containing a list of prerequisites.  The issue, specifically, is that we have many elements.  Now, if you think about this, it makes sense.  Excel allows you to easily edit a two dimensional array of data.  More than that and it gets complicated.

The solution I went with to get around this (because I require being able to add and remove from various lists of data, and it makes the XML easier to read) is to make use of the really good XML functionality in Flex.  My solution works like this:

1.  enter you data in Excel.  any time you have a list of things, enter them as a single cell and separate each "thing" with a comma.

2.  export to a simple xml format.

3.  feed the xml into a Flex app that takes the XML and converts any comma separated lists to a proper XML list.

A sample of this is here.  My flex app takes a list of feats I exported from Excel (shown on the left side), and then prints the proper XML for me to save to a file (shown on the right side).  There's now an extra step if I update my Excel spreadsheet, but it's better than having to edit XML all day long.

Unfortunately, I don't know of a way to get Flex to do magic things with XML schemas, so the dream app of being able to give one XML format and export another is still off my radar.  Also, you are effectively writing your XML schema in Flex code, so if you need to make a schema change, it's instead a Flex code change.  This is not necessarily bad.  I feel more comfortable making changes in code instead of in fancy XML schema editors.

Now, I've heard that Altova makes a really nice application that can handle lists of lists, but I haven't tried it (and the price is outside my budget).  Something for next time, I suppose.  For now, I've got a "good enough" solution for tricky XML, and it will be easy to update in the future if I need to.