In R, how can I loop over repeated XML nodes, and

Question

I'm working with XML files from clinicaltrials.gov, which have a structure like this:

<clinical_study>
  ...
  <brief_title>
  ...
  <location>
    <facility>
      <name>
      <address>
        <city>
        <state>
        <zip>
        <country>
    </facility>
  </location>
  <location>
    ...
  </location>
  ...
</clinical_study>

I'm gathering information from multiple XML files, so the number of locations in each file is unknown and could even be zero. I need to extract all the information about each location and save into an SQL table. I've had some success using functions from the XML package to extract information from single nodes, e.g.

library(XML)
nct_url <- "http://clinicaltrials.gov/ct2/show/NCT00112281?resultsxml=true"
xml_doc <- xmlParse(nct_url, useInternalNode=TRUE)
title_path <- "/clinical_study/brief_title" 
title_text <- xpathSApply(xml_doc, title_path, xmlValue)

I'm experimenting with getNodeSet, and this gives me a set of the right length:

doc <- xmlParse("NCT00007501.xml")
locations <- getNodeSet(doc, "/clinical_study/location")
length(locations)
[1] 22
> class(locations)
[1] "XMLNodeSet"

but my attempts to extract information from this set have been mostly fruitless. Any suggestions?

Answer 1

This code will put a subset of nodes that correspond to <location> from a clinical trial into a data frame:

library(XML)
clinicalTrialUrl <- "http://clinicaltrials.gov/ct2/show/NCT01480479?resultsxml=true"
xmlDoc <- xmlParse(clinicalTrialUrl, useInternalNode=TRUE)
locations <- xmlToDataFrame(getNodeSet(xmlDoc,"//location"))

In this case there are 221 locations. However, the code assumes sort of a flat structure and lumps subnodes together. For example, anything under <facility> gets concatenated into a single string. I can go into the subnodes and put them one by one into a dataframe.

Answer 2

I don't understand why do you not use again xpathSApply, to retrieve locations as you already did for titles?!

xpathSApply(xml_doc, "//clinical_study/location" , xmlValue)

Answer 3

Here is an example

 ns <- getNodeSet(xml, '//clinical_results/outcome_list/outcome/analysis_list/analysis/method')
 element_cnt <-length(ns))
 strings<-paste(sapply(ns, function(x) { xmlValue(x) }),collapse="|"))