Results 1 to 3 of 3
Thread: XML Export = I M Stoopid (2003)
2007-01-31, 18:20 #1jimdoriaGuest
XML Export = I M Stoopid (2003)
OK, I guess this is supposed to be easy, but I've been bad about keeping up with all things XML, and now I'm pulling my hair out trying to do something simple. On a deadline, of course.
I have a huge but simple worksheet - 3 columns, almost 50,000 rows. I want to export it to XML. I didn't think this would be hard. (Insert snickering here.)
I made up a nice little XML file in the structure I wanted. Paraphrased :
I used this as my XML map. Excel happily exported an XML file <img src=/S/cool.gif border=0 alt=cool width=15 height=15> containing a single row of data <img src=/S/beep.gif border=0 alt=beep width=15 height=15>.
Google led me to this tutorial http://www.codeproject.com/soap/ExcelListToXml.asp which showed I was on the right track, just needed to duplicate the "conversion" and its children so that Excel would "know" that it was supposed to apply the XML to multiple rows. Did this, fired up the XML Source task pane, and BOOM everything worked as planned! Maybe XML isn't so hard after all, I thought...
...for about a second because when I tried to export my data I got a cryptic error message that in typical MS style was backed up with a page of even more cryptic help text that gave me NO idea of what was wrong. Apparently my "map is not exportable" because "a mapped element's relationship with other elements cannot be preserved."
The help screen gleefully informs me about incorrect settings of the maxoccurs attribute and sternly warns that one direct child element could have another compositor as a child. Any of which might help if a] I had any idea where the maxoccurs attribute is (it's my understanding that Excel itself creates the schema containing this attribute) or b] I had the foggiest notion which elements were the "direct child" ones and what a compositor was.
I throw myself upon the mercy of the forum. Can anybody help me get a clue?
2007-02-01, 00:38 #2jimdoriaGuest
XML Export = I M REALLY Stoopid (2003)
It turns out I was stupider than even I thought possible. The answer to this was trivial.
Once I had imported my XML map, I was dragging the INDIVIDUAL sub-elements (oldcode, description, newcode) from the XML Source task pane over onto their respective columns in the spreadsheet. Each column turned into a list and the element name was added as a heading, so I thought this was how it was supposed to work.
I was working too hard! You don't drag the individual sub-elements at all, you drag the top level element (conversion, in my case) to the top-left cell of the data range. Excel then automatically assigns the sub-elements to each column, and the whole thing just works. Exports exactly as expected.
I am abashed. However, I still think the help file could have been a bit more helpful.
2007-02-01, 00:48 #3
- Join Date
- Mar 2002
- Thanked 29 Times in 29 Posts
Re: XML Export = I M REALLY Stoopid (2003)
Thanks for sharing the solution. It might help someone else struggling with it.