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:

<Kontaktdaten>
   <Name> Name </Name>
   <ID>12345678</ID>
   <Kontakt_Zugang>
       <Strasse>ABC-Strasse</Strasse>
       <Hausnummer>1</Hausnummer>
       <Postleitzahl>12345</Postleitzahl>
       <Ort>ABC</Ort>
   </Kontakt_Zugang> 
</Kontaktdaten>

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

<Kontaktdaten>
   <Name> Name2 </Name>
   <ID>8765321</ID>
   <Kontakt_Zugang>
       <Strasse>CBA-Strasse</Strasse>
       <Postleitzahl>54321</Postleitzahl>
       <Ort>CBA</Ort>
   </Kontakt_Zugang> 
</Kontaktdaten>

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.

Answer 1

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       <NA> CBA        54321 CBA-Strasse

Answer 2

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)

<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:output method="xml" version="1.0" encoding="UTF-8" indent="yes"/>
<xsl:strip-space elements="*"/>

<!-- identity transform -->
<xsl:template match="@*|node()">
    <xsl:copy>
        <xsl:apply-templates select="@*|node()"/>
    </xsl:copy>
</xsl:template>

<xsl:template match="Kontakt_Zugang">
    <xsl:apply-templates select="@*|node()"/>
</xsl:template>

</xsl:stylesheet>

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       <NA>        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")