XML Data in R different Filestructure

Question

I need to parse 2000 XML Files. I managed setting that I can automatically get my data from the files. Since I am a complete beginner, it maybe looks messy, here an example:

filenames <- list.files("C:/...", recursive=TRUE, full.names=TRUE, pattern=".xml")

name <- unlist(lapply(filenames, function(f) {  
  xml <- xmlParse(f)  
  xpathSApply(xml, "//...", xmlValue)
}))
data <- data.frame(name)

This works for most of my needed data but my current problem is that some files miss a certain data so I can't include them because of different number of rows. An example of what the files look like is: File 1:


    Name 
   12345678
   
       ABC-Strasse
       1
       12345
       ABC

File 2 (where "Hausnummer" is missing for example):


    Name2 
   8765321
   
       CBA-Strasse
       54321
       CBA

Is there any way how I can combine them anyway in one data.frame or create a second data.frame only with the "Hausnummer" and the ID?

EDIT: This is only an example to show my problem. The original files are up to 500 nodes long, some of them are doubled.

user_5792244 · Answer

Here is a solution of parsing each xml file, creating a list of the sub nodes in the individual files, then combining all the lists, and then converting to the desired format.

See the code comments for the step by step instructions.

library(xml2)

#list of files to process
fnames<-c("xml1.xml", "xml2.xml")

dfs<-lapply(fnames, function(fname) {
  doc<-read_xml(fname)


  #find Name and ID
  Name<-trimws(xml_text(xml_find_all(doc, ".//Name")))
  ID<-trimws(xml_text(xml_find_all(doc, ".//ID")))

  #find all of the nodes/records under the Kontakt_Zugang node
  nodes<-xml_children(xml_find_all(doc, ".//Kontakt_Zugang"))

  #find the sub nodes names and values
  nodenames<-xml_name(nodes)
  nodevalues<-trimws(xml_text(nodes))

  #make data frame of all the values
  df<-data.frame(file=fname, Name=Name, ID=ID, node.names=nodenames, 
             values=nodevalues, stringsAsFactors = FALSE)

})

#Make one long df
longdf<-do.call(rbind, dfs)

#make into a wide format
library(tidyr)
finalanswer<-spread(longdf, key=node.names, value=values)

Here is the final result:

#     file  Name       ID Hausnummer Ort Postleitzahl     Strasse
# xml1.xml  Name 12345678          1 ABC        12345 ABC-Strasse
# xml2.xml Name2  8765321        CBA        54321 CBA-Strasse

user_1422451 · Answer

Consider the special purpose language, XSLT, designed to transform XML files for end use solutions such as flattening the nested node Kontakt_Zugang for import into R and migrated into data frame.

XSLT (save as an .xsl file to be parsed like any .xml file into R)

Online Demo

R

library(xml2)
library(xslt)

# RETRIEVE XML FILE NAMES
filenames <- list.files("C:/...", recursive=TRUE, full.names=TRUE, pattern=".xml")
all_cols <- c("Name", "ID", "Strasse", "Hausnummer", "Postleitzahl", "Ort")

# PARSE XSLT
style <- read_xml("/path/to/xslt_script.xsl", package = "xslt")

df_list <- lapply(filenames, function(f) {  
  # PARSE XML
  xml <- xml2::read_xml(f)    
  # TRANSFORM INPUT INTO OUTPUT
  new_xml <- xslt::xml_xslt(xml, style)

  # BUILD DATA FRAME
  vals <- xml_children(xml_find_all(new_xml, "//Kontaktdaten"))
  df <- setNames(data.frame(t(trimws(xml_text(vals)))), xml_name(vals))

  # FILL IN MISSING COLUMNS
  df[all_cols[!(all_cols %in% colnames(df))]] <- NA

  return(df[all_cols])
})

final_df <- do.call(rbind, df_list)
final_df
#    Name       ID     Strasse Hausnummer Postleitzahl Ort
# 1  Name 12345678 ABC-Strasse          1        12345 ABC
# 2 Name2  8765321 CBA-Strasse               54321 CBA

By the way, because XSLT is a special-purpose language, it is not restricted to R but any language such as Java, PHP, Python that supports it and even external processors that R can make a command line call to run. As example, below uses Unix's (i.e., Mac and Linux) xsltproc:

# COMMAND LINE CALL TO UNIX'S XSLTPROC (ALTERNATIVE TO xslt PACKAGE)
system("xsltproc -o /path/to/input.xml /path/to/xslt_script.xsl /path/to/output.xml")
doc <- xmlParse("/path/to/output.xml")

user_10023960 · Answer

I created a single XML file that I believe resembles the one you maybe working with. I parse this file and pull out data using the function get_xml_dat.

file <- list.files('~/R/', pattern = '.xml', full.names = TRUE)


get_xml_dat <- function(xml_file, main_node, values) {
  require(tidyverse)
  require(rvest)
  require(xml2)


  nodeset <- xml_file %>% read_html() %>% html_nodes(tolower(main_node)) # pull out node with data from XML

  node_values <- paste0("<", tolower(values), ">.*") # create pattern to extract values

  dat <-  str_extract_all(as.character(nodeset), paste0(node_values, collapse = "|")) # extract values from nodeset

  lapply(dat, function(foo) {
    foo %>% gsub("|<", "", .) %>% # clean up data values
      tibble(V1 = .) %>% # transform string of values to long data_frames
      separate(., # separate node name from node value
               col = V1,
               into = c("cols", "vals"),
               sep = ">"
      ) %>%
      spread(cols, vals) # long data_frame to wide data_frame
  }) %>%
    bind_rows() %>% # bind list of data_frames
    select(tolower(values)) # orders columns
}

Output

> get_xml_dat(
+   xml_file = file, # XML file you want to get data from
+   main_node = 'Kontaktdaten', # node where the data is located
+   values = c('Name', 'ID', 'Hausnummer', 'Postleitzahl', 'Ort', 'Strasse') # values you want to get from the main_node
+ )
# A tibble: 3 x 6
  name         id     hausnummer postleitzahl ort   strasse    
                                 
1 " Name_ABC " 912283 1          12345        ABC   ABC-Strasse
2 " Name_DEF " 123456 NA         12345        DEF   DEF-Strasse
3 " Name_XYZ " 123456 3          12345        XYZ   XYZ-Strasse

Data

XML file that is loaded into R.


    
        
             Name_ABC 
            912283
            
                ABC-Strasse
                1
                12345
                ABC
            
        
    
     
        
             Name_DEF 
            123456
            
                DEF-Strasse
                12345
                DEF
            
        
    
    
        
             Name_XYZ 
            123456
            
                XYZ-Strasse
                3
                12345
                XYZ
            
        
    
    
        
            100
            100
            100

XML Data in R different Filestructure

3 Answers