How can I convert XML to something simple such as Excel?

I have transactional data coming to me in XML form, and I need to convert it to Excel  because the end user is only comfortable in Excel.

My situation is similar to that of company that operates a little league. Kid's pay to be on a team. Kids buy hats, shirts, gloves, or shoes from the little league operators. The little league operators have all the purchases coming through a generic e-commerce package offered by GoMommy, or some other random e-commerce shopping cart provider. I can download the XML from the web host. The end result needs to be rows of kids, and columns of purchases. It would look something like this:
 desired, flat-record table
The kids make multiple visits to the e-commerce site. On visit #1, they pay for their participation in the program, and maybe other items. Sometimes, they make 2nd, 3rd, 4th, etc. visits to buy their team hats, or some other items. Any one child can have multiple e-commerce transactions. The e-commerce application treats each transaction as a separate, self-contained event. The e-commerce application doesn't group the transactions. The XML has all the details one would expect from an e-commerce situation.

The end-user is just one guy. He wants to have a simple way to see who the kids are, and what they've purchased. When the kids show up for their first practice session, the user will hand out the gloves, hats, etc., based on what he sees in the table. He wants it in Excel so that he can sort the data and do other simple Excel actions.

I've tried to connect Crystal Reports XI to the XML file by using the e-commerce vendor's XSD file. I was hoping to connect Crystal and make a report that simply grouped all of the sales transactions, based on the children's names.  Crystal doesn't like the XSD that I've been provided. I have no faith that the e-commerce provider has maintained the XSD file to match incremental changes in their e-commerce application and the subsequent XML.

To get the simple table, where each row represents one child, with all of their associated purchases, what tools should I be using? There seem to be tons of XML conversion tools out there, but I can't differentiate amongst them. My budget is <$100.

oakie22Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
Can you upload a sample XML file?

mlmcc
0
mark_harris231Commented:
What version of Excel are you/end-user using?  2007 will recognize an XML file and attempt to translate/organize it on file-open.  You can also map cells to pull in specific XML-tagged data.  See the following URL: http://office.microsoft.com/en-us/excel-help/import-xml-data-HP010206405.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
sybeCommented:
I'd use xslt to convert the data to an html-table (which can be easily opened by (all versions of) Excel.
0
WiesjeCommented:
Do have a look at the following as well. It outlines how you can view an xml file in excel:
http://office.microsoft.com/en-us/excel-help/viewing-an-xml-file-in-excel-HA001034645.aspx

I personally would use an XSLT stylesheet to transform the XML into a CSV file which can then be opened in excel.
To create an xslt stylesheet read: http://msdn.microsoft.com/en-us/library/ms172510(v=sql.90).aspx



0
oakie22Author Commented:
It will be several days before I can pick up this topic again.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
XML

From novice to tech pro — start learning today.