Convert individual rows in an XML doc to a hash?

Question

I have the following XML doc:

<AccountPerformanceReportColumns>
  <Column name="AccountName" />
  <Column name="Impressions" />
  <Column name="Clicks" />
  <Column name="Ctr" />
  <Column name="Conversions" />
  <Column name="CostPerConversion" />
  <Column name="Spend" />
</AccountPerformanceReportColumns>
<Table>
<Row>
  <AccountName value="Cleveland" />
  <Impressions value="5822" />
  <Clicks value="138" />
  <Ctr value="2.37" />
  <Conversions value="0" />
  <CostPerConversion value="" />
  <Spend value="238.28" />
</Row>
<Row>
  <AccountName value="Denver" />
  <Impressions value="8196" />
  <Clicks value="123" />
  <Ctr value="1.50" />
  <Conversions value="0" />
  <CostPerConversion value="" />
  <Spend value="258.32" />
</Row>
<Row>
  <AccountName value="Houston" />
  <Impressions value="7218" />
  <Clicks value="105" />
  <Ctr value="1.45" />
  <Conversions value="3" />
  <CostPerConversion value="75.88" />
  <Spend value="227.63" />
</Row>
<Row>
  <AccountName value="LA" />
  <Impressions value="72290" />
  <Clicks value="713" />
  <Ctr value="0.99" />
  <Conversions value="0" />
  <CostPerConversion value="" />
  <Spend value="932.93" />
</Row>
<Row>
  <AccountName value="Louisville" />
  <Impressions value="2811" />
  <Clicks value="68" />
  <Ctr value="2.42" />
  <Conversions value="0" />
  <CostPerConversion value="" />
  <Spend value="167.09" />
</Row>
</Table>

I'm trying to parse it with Nokogiri and convert it to a hash so that I can create a DB record for the hash like:

BingRecords.create!(conversions: hash[:conversion],
                   spend: hash[:spend],
                   account_name: hash[:account_name],
                   date: date,
                   user_id: user.id)

Where something like:

hash[:account_name] => ["Cleveland", "Denver", "Houston", "LA", "Louisville"]

I tried doing something like:

bingstats = doc.xpath("//AccountName", "//Spend", "//Conversions")

That gives me all the data from those tags followed by stats = bingstats.map {|map| map.values} which returns:

[["Cleveland], ["Denver"], ["Houston"], ["LA"], ["Louisville"], ["238.28"], ["258.32"]

but that doesn't give me the ability to separate those for the separate records.

You can use `//Row[n]/AccountName` to access an `AccountName` for a specific row `n`. `//Row[1]/AccountName/@value` will return the string "Cleveland". You can also retrieve only the row nodes (`//Row`) into an object and extract their child nodes in ruby. — helderdarocha, Feb 27 '14 at 22:49
The way you're using `create!()` you get exactly one record. I'm not sure if this is what you want. Do you want one record per row? — Mark Thomas, Feb 28 '14 at 00:40
@MarkThomas - yes, just one record. Ultimately these snippets are part of an api request that grabs data for just one day and creates a record for it to be called upon later. — macoughl, Feb 28 '14 at 01:24
@helderdarocha - `//Row[1]/AccountName/@value` returns an Nokogiri attribute that I can parse for the string of "Cleveland". This still doesn't get me to a hash which I can call hash[:account_name] to get all the account names. — macoughl, Feb 28 '14 at 01:30

macoughl · Answer 1 · 2014-03-05T01:47:42.593

0

doc = Nokogiri::XML(file).remove_namespaces!
row = doc.xpath("//Row")
account_name_tag = row.xpath("//AccountName")
account_name_values = account_name_tag.map {|map| map.values}
account_name_array = account_name_values.flatten
hash = {account_name: account_name_array}

Then I'll just repeat for spend, conversion, etc. and add them to the hash:

hash = {account_name: account_name_array, spend: spend_array, conversions: conversions_array}

edited Mar 05 '14 at 01:47

answered Feb 28 '14 at 02:57

macoughl

501
1
5
15

Is this an answer? If so, don't ask if there's a more efficient way. If it's a continuation of your question, then it should be appended to the question by editing it, and this answer should be deleted. – the Tin Man Mar 04 '14 at 23:12

score 0 · Accepted Answer · edited May 23 '17 at 10:26

0

How about my answer on this question: https://stackoverflow.com/a/10144623/405017

With it, you can just call to_hash on any single Nokogiri element and get back a nice hash.

Then, you can (if you want) select just the fields of the hash you want with: Slicing params hash for specific values

edited May 23 '17 at 10:26

Community

1
1

answered Mar 01 '14 at 03:35

Phrogz

271,922
98
616
693

Convert individual rows in an XML doc to a hash?

2 Answers2