{"id":30,"date":"2008-10-04T23:44:00","date_gmt":"2008-10-05T04:44:00","guid":{"rendered":"http:\/\/lukerymarz.com\/alearningexperience\/2008\/10\/xml-schemas-excel-and-getting-what-you-want.html"},"modified":"2008-10-04T23:44:00","modified_gmt":"2008-10-05T04:44:00","slug":"xml-schemas-excel-and-getting-what-you-want","status":"publish","type":"post","link":"http:\/\/lukerymarz.com\/alearningexperience\/2008\/10\/xml-schemas-excel-and-getting-what-you-want.html","title":{"rendered":"XML schemas, Excel, and getting what you want"},"content":{"rendered":"

In my character generator, there is a lot of data. \u00a0There’s races, classes, skill, feats, and powers. \u00a0On top of that, they’re not static lists. \u00a0Wizards of the Coast (and other people) are always adding and expanding on everything. \u00a0With that in mind, I knew I needed to have all that data extensible in my character generator. \u00a0XML is the obvious choice, especially since Flex is so good at parsing it.<\/p>\n

<\/div>\n
But how was I to get the data in the PHB into XML in the easiest way possible? \u00a0Microsoft Excel and XML Schemas is the answer. \u00a0The basic idea is that you enter all your data into Microsoft Excel as a big list. \u00a0Then you tell Excel to export it as XML. \u00a0That XML is then fed into the Flex app. \u00a0<\/div>\n
<\/div>\n
XML schemas tell Excel how to format the XML you export. \u00a0With an XML schema, you’re defining the basic format of your XML (in XML, ironically). \u00a0There’s an article here <\/a>that gives a good way to create a simple Xml Schema Definition (XSD) using Excel (see step 6, specifically). \u00a0That will give you the schema, and then you can attach that to your Excel spreadsheet and have Excel export XML for you.<\/div>\n
<\/div>\n
But there’s a HUGE caveat. \u00a0Excel cannot handle (that is, export, import, deal with at all) XML with a list of lists. \u00a0This looks something like this:<\/div>\n
<\/div>\n
\n
\n
\n
<feats><\/div>\n
\u00a0\u00a0<feat name=”a”><\/div>\n
\u00a0\u00a0 \u00a0<prerequisites><\/div>\n
\u00a0\u00a0 \u00a0 \u00a0<prerequisite name=”x”\/><\/div>\n
\u00a0\u00a0 \u00a0 \u00a0<prerequisite name=”y”\/><\/div>\n
\u00a0\u00a0 \u00a0<\/prerequisites><\/div>\n
\u00a0\u00a0<\/feat><\/div>\n
\u00a0\u00a0<feat name=”b”><\/div>\n
\u00a0\u00a0 \u00a0<prerequisites><\/div>\n
\u00a0\u00a0 \u00a0 \u00a0<prerequisite name=”p”\/><\/div>\n
\u00a0\u00a0 \u00a0 \u00a0<prerequisite name=”q”\/><\/div>\n
\u00a0\u00a0 \u00a0<\/prerequisites><\/div>\n
\u00a0\u00a0<\/feat><\/div>\n
<\/feats><\/div>\n<\/div>\n<\/div>\n<\/div>\n
<\/div>\n
We’ve got a list of feats, each containing a list of prerequisites. \u00a0The issue, specifically, is that we have many elements. \u00a0Now, if you think about this, it makes sense. \u00a0Excel allows you to easily edit a two dimensional array of data. \u00a0More than that and it gets complicated.<\/prerequisite><\/div>\n
<\/div>\n
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. \u00a0My solution works like this:<\/div>\n
<\/div>\n
1. \u00a0enter you data in Excel. \u00a0any time you have a list of things, enter them as a single cell and separate each “thing” with a comma.<\/div>\n
2. \u00a0export to a simple xml format.<\/div>\n
3. \u00a0feed the xml into a Flex app that takes the XML and converts any comma separated lists to a proper XML list.<\/div>\n
<\/div>\n
A sample of this is here<\/a>. \u00a0My 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). \u00a0There’s now an extra step if I update my Excel spreadsheet, but it’s better than having to edit XML all day long.<\/div>\n
<\/div>\n
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. \u00a0Also, 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. \u00a0This is not necessarily bad. \u00a0I feel more comfortable making changes in code instead of in fancy XML schema editors.<\/div>\n
<\/div>\n
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). \u00a0Something for next time, I suppose. \u00a0For 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.<\/div>\n","protected":false},"excerpt":{"rendered":"

In my character generator, there is a lot of data. \u00a0There’s races, classes, skill, feats, and powers. \u00a0On top of that, they’re not static lists. \u00a0Wizards of the Coast (and other people) are always adding and expanding on everything. \u00a0With that in mind, I knew I needed to have all that data extensible in my […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"footnotes":""},"categories":[11,10,8],"tags":[],"class_list":["post-30","post","type-post","status-publish","format-standard","hentry","category-adobe-air","category-adobe-flex","category-flex"],"_links":{"self":[{"href":"http:\/\/lukerymarz.com\/alearningexperience\/wp-json\/wp\/v2\/posts\/30","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/lukerymarz.com\/alearningexperience\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/lukerymarz.com\/alearningexperience\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/lukerymarz.com\/alearningexperience\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/lukerymarz.com\/alearningexperience\/wp-json\/wp\/v2\/comments?post=30"}],"version-history":[{"count":0,"href":"http:\/\/lukerymarz.com\/alearningexperience\/wp-json\/wp\/v2\/posts\/30\/revisions"}],"wp:attachment":[{"href":"http:\/\/lukerymarz.com\/alearningexperience\/wp-json\/wp\/v2\/media?parent=30"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/lukerymarz.com\/alearningexperience\/wp-json\/wp\/v2\/categories?post=30"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/lukerymarz.com\/alearningexperience\/wp-json\/wp\/v2\/tags?post=30"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}