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:
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.