From: matt@... (matt neuburg)
Date: 2009-03-18T00:12:44+09:00
Subject: Re: XML to CSV with REXML - I'm sure this should be easy...

Sandy Thomson <rhubarbcrumble@gmail.com> wrote:

> Ok, I'm taking a fairly simple xml file containing a series of events
> and I want to convert it to csv - nothing new there.
> 
> However, some events have two or more dates listed and I'd like to
> display each as individual lines. My ruby skills are fairly limited but
> from googling around I can extract everything up to the dates, but I'm
> banging my head against a wall to get any further...
> 
> Here's the XML:
> 
> <event id='1234'>
> 
>   <title>Event Title</title>
> 
>   <category>Event Category</category>
> 
>   <venue>
> 
>     <name>Venue Name</name>
> 
>     <address>
> 
>       <address1>1 Some Street</address1>
> 
>       <town>Some Town</town>
> 
>     </address>
> 
>   </venue>
> 
>   <performances>
> 
>     <performance date='2009-04-01 18:00:00' />
> 
>     <performance date='2009-04-03 18:00:00' />
> 
>   </performances>
> 
> </event>
> 
> This is my extraction code:
> 
> require 'rexml/document'
> xml = REXML::Document.new(File.open("data.xml"))
> csv_file = File.new("data.csv", "w")
> xml.elements.each("//event") do |e|
>     csv_file.puts e.attributes['id'] << "|" <<
>             e.elements['title'].text << "|" <<
>       e.elements['category'].text << "|" <<
>       e.elements['venue/name'].text << "|" <<
>       e.elements['venue/address/address1'].text << "|" <<
>       e.elements['venue/address/town'].text
> end
> 
> Which gives me:
> 1234|Event Title|Event Category|Venue Name|1 Some Street|Some Town
> 
> But what I really want is:
> 1234|Event Title|Event Category|Venue Name|1 Some Street|Some
> Town|2009-04-01 18:00:00
> 1234|Event Title|Event Category|Venue Name|1 Some Street|Some
> Town|2009-04-03 18:00:00

What you are really interested in is each performance (each performance
generates one line of output). So simply deepen your loop:

xml.elements.each("//event") do |e|
  e.elements.each("//performance") do |p|

Now do exactly what you're doing and just append the performance date.
So, for example:

require 'rexml/document'
include REXML
output = ""
class REXML::Element
  def textof(xpaths_arr); xpaths_arr.map {|x| elements[x].text}; end
end
xml = Document.new(s)
xp = %w{title category venue/name 
  venue/address/address1 venue/address/town}
xml.elements.each("//event") do |e|
  e.elements.each("//performance") do |p|
    output << 
      [e.attributes['id'], 
      e.textof(xp), 
      p.attributes['date']].flatten.join("|") + "\n"
  end
end

m.

-- 
matt neuburg, phd = matt@tidbits.com, http://www.tidbits.com/matt/
Leopard - http://www.takecontrolbooks.com/leopard-customizing.html
AppleScript - http://www.amazon.com/gp/product/0596102119
Read TidBITS! It's free and smart. http://www.tidbits.com