My solution. I was feeling pretty snazzy with all my new tips & tricks for RegEx (thanks for the links, @SeanAdams!)... only to realize with this challenge that I also know next to nothing about XML parsing. But a few searches later and I figured it out enough to fumble my way through this one... I'm finding all sorts of new tools with these challenges that I'll never ever use at work. :)
Thanks for including the data!
Do I get bonus point for fewest tools used? First picture is a solution with RegEx parsing. 7 tools! I'm not counting the browse ;)
Second solution is with formulas/cross tab/text to columns
best hint I ever got on regex came from Mark ( @MarqueeCrew )
The tip is that http://regex101.com allows you to play with Regex phrases in real time, and it's a really easy way to learn and practice. I now use it for all my regex work, especially web-scraping!
Have a look - it really is a fantastic site, and if Alteryx could build something similar into the product, it would be legendary!