Need Help Working with XML and Excel

Factual Questions
1

I need to create an XML file containing data from Excel from an existing schema. The schema includes nested repeating elements, and Excel doesn’t seem to like nested repeating elements. When I try to verify that the XML will export, I get the “lists of lists” error indicating that I won’t be able to export the data.

As an example of what I’m trying to do, imagine annual sales of games, where the types of games are not explicitly defined in the schema but are assigned to the GameName element instead.

Here is a simplified example of the structure of the schema. Please excuse any syntax issues; I’m only trying to get data into the format and don’t require a complete grasp of the schema formatting.


 <xsd:extension gYear=”YearGameSales”>
        <xsd:extension string=”GameNameDetail”>
            <xsd:extension name=”GameSales”>

Where YearGameSales repeats for each year and is therefore complex, GameName repeats for each game and is therefore comples, and GameSales is the value of the sales and is simple.
And here is the type of table I have in Excel:


Year    Jacks    Chess    Checkers
2000    100      40       120
2001    400      30       100
2002    50       100      50

Can I even use Excel to get these data into the required schema? If so, how? If not, how do I get the data from Excel into XML that works with the schema? I’m fairly new to XML and schema, but I’m reasonable proficient in Excel.

Finally, the Excel and Office help files and online help have been incredibly opaque or unhelpful for working with XML in Excel.

2

I’m not much help here. I’ve run into problems similar to yours in that I couldn’t get Excel to do the proper mapping from it’s tables.

My advice to you doesn’t carry expert status but may give you some ideas of your own.

Since excel didn’t like repeated elements I wrote VBA code that explicitly created my desired XML files.
That meant that I had to keep a list of the desired tags somewhere and do some extra calculations that recognized the start and stop areas on the tables etc.

By knowing the tables structure and my desired structure for the XML file my VBA assembled the XML text line by line in my workbook on a separate sheet. Then the VBA code opened an output file, copied the XML sheet to the the new workbook and saved it as a text file with and XML suffix.

3

Not an expert either, but on a similar project I used ADO.NET in Visual basic .net to query an Access database. The same method supports Excel.

http://support.microsoft.com/kb/311731 <- the example is ASP, but the relevant code will work in winforms.

Assuming someone doesn’t come along with a better idea you could use ADO to import the data as a table in a dataset, and then loop through the datatable writing it’s values to an XML file with your desired scheme.