How do I map XML sequences to Excel columns?

Question

I'm new to XML. What I have is a load of XML from Adobe Illustrator which I'm trying to map excel data onto. I have sequences in the original data which look like this

<Market_Pie>
<datanumDataColumns="5">
<values>
<row>
<valuekey="name"></value>
<value>503.931</value>
<value>268.301</value>
<value>285.561</value>
<value>152.037</value>
</row>
</values>
</data>
</Market_Pie>

Excel has created an XML map from this which sees this as a single sequence. When I try to associate this with various cells, it seems to want to see this as a single column. My data, however, is structured so that this data needs to be in a series of separate columns, one column for each value.

I'm using Excel 2007, and just can't see how this is supposed to work. The help files seem remarkably unhelpful as well.

Answer 1

In order to have each value get its own column, each value needs a unique key, something that would correspond to the the header for each column. There are a couple of ways to cheat this:

1. Import the data in one sheet, and have a formula or function in another sheet that works down the column and puts all values in columns.

2. Give each value a unique element name or id. Either:

   <Market_Pie>
   <data numDataColumns="5">
   <values>
   <row>
   <valuekey="name"></value>
   <value1>503.931</value>
   <value2>268.301</value>
   <value3>285.561</value>
   <value4>152.037</value>
   </row>
   </values>
   </data>
   </Market_Pie>
   

or

   <Market_Pie>
   <data numDataColumns="5">
   <values>
   <row>
   <valuekey="name"></value>
   <value id="col1">503.931</value>
   <value id="col2">268.301</value>
   <value id="col3">285.561</value>
   <value id="col4">152.037</value>
   </row>
   </values>
   </data>
   </Market_Pie>

The problem with either solution reveals the actual problem with what you have in mind, which is: what if you had 200 values? Do you actually want 200 columns? Typically sets of values go into columns, and each set gets its own column. If the values all belong to the same set, you probably want to have them in one column. But if columns fit your model, as your question suggests, I would go with using a function.