I have several thousands xml-files that I need to convert to csv. The files have different structures. I managed to convert most of the files, but I struggle with one type and I don't get why.
This is my xml-file:
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<eahv-iv-2469-000101:message minorVersion="1" xmlns:eCH-0058="http://www.ech.ch/xmlns/eCH-0058/5" xmlns:eCH-0135="http://www.ech.ch/xmlns/eCH-0135/1" xmlns:eahv-iv-2469-000201="http://www.eahv-iv.ch/xmlns/eahv-iv-2469-000201/2" xmlns:dossiers="http://xmlns.sasta.ch/dossiers/15" xmlns:eahv-iv-2469-000301="http://www.eahv-iv.ch/xmlns/eahv-iv-2469-000301/2" xmlns:eCH-0011="http://www.ech.ch/xmlns/eCH-0011/8" xmlns:ma-sd="http://ma.zh.ch/xmlns/sozialedienste/v1" xmlns:eahv-iv-2469-000101="http://www.eahv-iv.ch/xmlns/eahv-iv-2469-000101/2" xmlns:eCH-0010="http://www.ech.ch/xmlns/eCH-0010/5" xmlns:ns8="http://www.eahv-iv.ch/xmlns/eahv-iv-2469-000201/1" xmlns:ns7="http://www.eahv-iv.ch/xmlns/eahv-iv-2469-000101/1" xmlns:common="http://www.eahv-iv.ch/xmlns/eahv-iv-2469-common/2" xmlns:ns9="http://www.eahv-iv.ch/xmlns/eahv-iv-2469-000301/1" xmlns:ns11="http://www.eahv-iv.ch/xmlns/eahv-iv-2469-common/1" xmlns:ns10="http://www.eahv-iv.ch/xmlns/eahv-iv-2469-000501/1" xmlns:eahv-iv-2469-000501="http://www.eahv-iv.ch/xmlns/eahv-iv-2469-000501/2" xmlns:eCH-0046="http://www.ech.ch/xmlns/eCH-0046/5" xmlns:eCH-0044="http://www.ech.ch/xmlns/eCH-0044/4" xmlns:zas-2702-000101="http://www.zas.admin.ch/xmlns/zas-2702-000101/1" xmlns:eCH-0006="http://www.ech.ch/xmlns/eCH-0006/2" xmlns:eCH-0008="http://www.ech.ch/xmlns/eCH-0008/3" xmlns:eCH-0007="http://www.ech.ch/xmlns/eCH-0007/5" xmlns:ns21="http://www.ech.ch/xmlns/eCH-0010/7" xmlns:plausis="http://xmlns.sasta.ch/plausis/15" xmlns:eahv-iv-2905-000001="http://www.eahv-iv.ch/xmlns/eahv-iv-2905-000001/1" xmlns:eCH-0090="http://www.ech.ch/xmlns/eCH-0090/1">
<eahv-iv-2469-000101:header>
<common:senderId>99999</common:senderId>
<common:recipientId>99999</common:recipientId>
<common:subject>AJ, BJ</common:subject>
<common:messageDate>2021-12-31</common:messageDate>
<common:action>99</common:action>
<common:testDeliveryFlag>false</common:testDeliveryFlag>
<eahv-iv-2469-000101:subMessageType>99999</eahv-iv-2469-000101:subMessageType>
</eahv-iv-2469-000101:header>
<eahv-iv-2469-000101:content>
<eahv-iv-2469-000101:case>
<eahv-iv-2469-000101:businessCaseIdRPC>TZ54455</eahv-iv-2469-000101:businessCaseIdRPC>
<eahv-iv-2469-000101:decisions>
<eahv-iv-2469-000101:decisionRef0>
<common:decisionId>TZ88k999</common:decisionId>
<common:decisionDate>2021-12-31</common:decisionDate>
<common:validFrom>2021-12</common:validFrom>
<common:deliveryOffice>
<common:elOffice>999</common:elOffice>
</common:deliveryOffice>
<eahv-iv-2469-000101:decisionKind>9</eahv-iv-2469-000101:decisionKind>
<eahv-iv-2469-000101:decisionCause>9</eahv-iv-2469-000101:decisionCause>
<eahv-iv-2469-000101:elAmounts>
<eahv-iv-2469-000101:amountNoHC>9999</eahv-iv-2469-000101:amountNoHC>
<eahv-iv-2469-000101:amountWithHC>999999</eahv-iv-2469-000101:amountWithHC>
<eahv-iv-2469-000101:elLimit>9</eahv-iv-2469-000101:elLimit>
</eahv-iv-2469-000101:elAmounts>
<eahv-iv-2469-000101:calculationElements>
<eahv-iv-2469-000101:otherWealth>99999</eahv-iv-2469-000101:otherWealth>
<eahv-iv-2469-000101:divestedWealth>0</eahv-iv-2469-000101:divestedWealth>
<eahv-iv-2469-000101:otherDebts>0</eahv-iv-2469-000101:otherDebts>
<eahv-iv-2469-000101:wealthDeductible>99999</eahv-iv-2469-000101:wealthDeductible>
<eahv-iv-2469-000101:wealthConsidered>0</eahv-iv-2469-000101:wealthConsidered>
<eahv-iv-2469-000101:wealthIncome>0</eahv-iv-2469-000101:wealthIncome>
<eahv-iv-2469-000101:usufructIncome>0</eahv-iv-2469-000101:usufructIncome>
<eahv-iv-2469-000101:wealthIncomeConsidered>0</eahv-iv-2469-000101:wealthIncomeConsidered>
<eahv-iv-2469-000101:incomeConsideredTotal>0</eahv-iv-2469-000101:incomeConsideredTotal>
<eahv-iv-2469-000101:wealthIncomeRate>0</eahv-iv-2469-000101:wealthIncomeRate>
<eahv-iv-2469-000101:vitalNeeds>99999</eahv-iv-2469-000101:vitalNeeds>
<eahv-iv-2469-000101:children>0</eahv-iv-2469-000101:children>
<eahv-iv-2469-000101:livingSituation>test</eahv-iv-2469-000101:livingSituation>
<eahv-iv-2469-000101:rents>
<eahv-iv-2469-000101:grossRental>999</eahv-iv-2469-000101:grossRental>
<eahv-iv-2469-000101:rentCategory>Test</eahv-iv-2469-000101:rentCategory>
<eahv-iv-2469-000101:rentGrossTotal>99999</eahv-iv-2469-000101:rentGrossTotal>
<eahv-iv-2469-000101:rentGrossTotalPart>99999</eahv-iv-2469-000101:rentGrossTotalPart>
<eahv-iv-2469-000101:maxRent>99999</eahv-iv-2469-000101:maxRent>
<eahv-iv-2469-000101:wheelchairSurcharge>false</eahv-iv-2469-000101:wheelchairSurcharge>
</eahv-iv-2469-000101:rents>
</eahv-iv-2469-000101:calculationElements>
<eahv-iv-2469-000101:persons>
<eahv-iv-2469-000101:person>
<eahv-iv-2469-000101:vn>987876567899</eahv-iv-2469-000101:vn>
<eahv-iv-2469-000101:representative>true</eahv-iv-2469-000101:representative>
<eahv-iv-2469-000101:pensionKind>99</eahv-iv-2469-000101:pensionKind>
<eahv-iv-2469-000101:maritalStatus>99</eahv-iv-2469-000101:maritalStatus>
<eahv-iv-2469-000101:housingMode>Test</eahv-iv-2469-000101:housingMode>
<eahv-iv-2469-000101:legalAddress>
<eahv-iv-2469-000101:canton>TZ</eahv-iv-2469-000101:canton>
<eahv-iv-2469-000101:municipality>9999</eahv-iv-2469-000101:municipality>
</eahv-iv-2469-000101:legalAddress>
<eahv-iv-2469-000101:vitalNeedsCategory>Test</eahv-iv-2469-000101:vitalNeedsCategory>
<eahv-iv-2469-000101:personalCalculationElements>
<eahv-iv-2469-000101:pensionCategory>
<eahv-iv-2469-000101:pension>
<eahv-iv-2469-000101:compensationOffice>
<eahv-iv-2469-000101:compensationOffice>99</eahv-iv-2469-000101:compensationOffice>
<eahv-iv-2469-000101:compensationAgency>0</eahv-iv-2469-000101:compensationAgency>
</eahv-iv-2469-000101:compensationOffice>
<eahv-iv-2469-000101:avsAipension>99999</eahv-iv-2469-000101:avsAipension>
<eahv-iv-2469-000101:disabledAllowance>0</eahv-iv-2469-000101:disabledAllowance>
<eahv-iv-2469-000101:dailyAllowance>0</eahv-iv-2469-000101:dailyAllowance>
</eahv-iv-2469-000101:pension>
</eahv-iv-2469-000101:pensionCategory>
<eahv-iv-2469-000101:hcLcaAllowance>0</eahv-iv-2469-000101:hcLcaAllowance>
<eahv-iv-2469-000101:lucrativeGrossIncome>0</eahv-iv-2469-000101:lucrativeGrossIncome>
<eahv-iv-2469-000101:hypotheticalGrossIncome>0</eahv-iv-2469-000101:hypotheticalGrossIncome>
<eahv-iv-2469-000101:totalPension>0</eahv-iv-2469-000101:totalPension>
<eahv-iv-2469-000101:lppPension>0</eahv-iv-2469-000101:lppPension>
<eahv-iv-2469-000101:foreignPension>0</eahv-iv-2469-000101:foreignPension>
<eahv-iv-2469-000101:otherIncomes>0</eahv-iv-2469-000101:otherIncomes>
<eahv-iv-2469-000101:lppWithdrawalAmount>0</eahv-iv-2469-000101:lppWithdrawalAmount>
<eahv-iv-2469-000101:patientContributionCategory>IGNORED</eahv-iv-2469-000101:patientContributionCategory>
<eahv-iv-2469-000101:hcFlatHelp>9999</eahv-iv-2469-000101:hcFlatHelp>
<eahv-iv-2469-000101:otherExpenses>0</eahv-iv-2469-000101:otherExpenses>
<eahv-iv-2469-000101:disabledAllowanceRecipient>false</eahv-iv-2469-000101:disabledAllowanceRecipient>
<eahv-iv-2469-000101:hcEffectiveHelp>99999</eahv-iv-2469-000101:hcEffectiveHelp>
</eahv-iv-2469-000101:personalCalculationElements>
</eahv-iv-2469-000101:person>
</eahv-iv-2469-000101:persons>
<eahv-iv-2469-000101:decisionIdPartnerDecision>TZ99989988</eahv-iv-2469-000101:decisionIdPartnerDecision>
</eahv-iv-2469-000101:decisionRef0>
<eahv-iv-2469-000101:decisionRef0>
<common:decisionId>TZ99989988</common:decisionId>
<common:decisionDate>2021-12-31</common:decisionDate>
<common:validFrom>2021-12</common:validFrom>
<common:deliveryOffice>
<common:elOffice>999</common:elOffice>
</common:deliveryOffice>
<eahv-iv-2469-000101:decisionKind>9</eahv-iv-2469-000101:decisionKind>
<eahv-iv-2469-000101:decisionCause>9</eahv-iv-2469-000101:decisionCause>
<eahv-iv-2469-000101:elAmounts>
<eahv-iv-2469-000101:amountNoHC>9999</eahv-iv-2469-000101:amountNoHC>
<eahv-iv-2469-000101:amountWithHC>9999</eahv-iv-2469-000101:amountWithHC>
<eahv-iv-2469-000101:elLimit>0</eahv-iv-2469-000101:elLimit>
</eahv-iv-2469-000101:elAmounts>
<eahv-iv-2469-000101:calculationElements>
<eahv-iv-2469-000101:otherWealth>9999</eahv-iv-2469-000101:otherWealth>
<eahv-iv-2469-000101:divestedWealth>0</eahv-iv-2469-000101:divestedWealth>
<eahv-iv-2469-000101:otherDebts>0</eahv-iv-2469-000101:otherDebts>
<eahv-iv-2469-000101:wealthDeductible>9999</eahv-iv-2469-000101:wealthDeductible>
<eahv-iv-2469-000101:wealthConsidered>0</eahv-iv-2469-000101:wealthConsidered>
<eahv-iv-2469-000101:wealthIncome>0</eahv-iv-2469-000101:wealthIncome>
<eahv-iv-2469-000101:usufructIncome>0</eahv-iv-2469-000101:usufructIncome>
<eahv-iv-2469-000101:wealthIncomeConsidered>0</eahv-iv-2469-000101:wealthIncomeConsidered>
<eahv-iv-2469-000101:incomeConsideredTotal>0</eahv-iv-2469-000101:incomeConsideredTotal>
<eahv-iv-2469-000101:wealthIncomeRate>0</eahv-iv-2469-000101:wealthIncomeRate>
<eahv-iv-2469-000101:vitalNeeds>0</eahv-iv-2469-000101:vitalNeeds>
<eahv-iv-2469-000101:children>0</eahv-iv-2469-000101:children>
<eahv-iv-2469-000101:livingSituation>test</eahv-iv-2469-000101:livingSituation>
<eahv-iv-2469-000101:rents>
<eahv-iv-2469-000101:grossRental>0</eahv-iv-2469-000101:grossRental>
<eahv-iv-2469-000101:rentCategory>test</eahv-iv-2469-000101:rentCategory>
<eahv-iv-2469-000101:rentGrossTotal>0</eahv-iv-2469-000101:rentGrossTotal>
<eahv-iv-2469-000101:rentGrossTotalPart>0</eahv-iv-2469-000101:rentGrossTotalPart>
<eahv-iv-2469-000101:maxRent>9999</eahv-iv-2469-000101:maxRent>
<eahv-iv-2469-000101:wheelchairSurcharge>false</eahv-iv-2469-000101:wheelchairSurcharge>
</eahv-iv-2469-000101:rents>
</eahv-iv-2469-000101:calculationElements>
<eahv-iv-2469-000101:persons>
<eahv-iv-2469-000101:person>
<eahv-iv-2469-000101:vn>987732488234</eahv-iv-2469-000101:vn>
<eahv-iv-2469-000101:representative>true</eahv-iv-2469-000101:representative>
<eahv-iv-2469-000101:pensionKind>99</eahv-iv-2469-000101:pensionKind>
<eahv-iv-2469-000101:maritalStatus>9</eahv-iv-2469-000101:maritalStatus>
<eahv-iv-2469-000101:housingMode>test</eahv-iv-2469-000101:housingMode>
<eahv-iv-2469-000101:legalAddress>
<eahv-iv-2469-000101:canton>te</eahv-iv-2469-000101:canton>
<eahv-iv-2469-000101:municipality>9999</eahv-iv-2469-000101:municipality>
</eahv-iv-2469-000101:legalAddress>
<eahv-iv-2469-000101:vitalNeedsCategory>Test</eahv-iv-2469-000101:vitalNeedsCategory>
<eahv-iv-2469-000101:personalCalculationElements>
<eahv-iv-2469-000101:pensionCategory>
<eahv-iv-2469-000101:pension>
<eahv-iv-2469-000101:compensationOffice>
<eahv-iv-2469-000101:compensationOffice>99</eahv-iv-2469-000101:compensationOffice>
<eahv-iv-2469-000101:compensationAgency>0</eahv-iv-2469-000101:compensationAgency>
</eahv-iv-2469-000101:compensationOffice>
<eahv-iv-2469-000101:avsAipension>9999</eahv-iv-2469-000101:avsAipension>
<eahv-iv-2469-000101:disabledAllowance>9999</eahv-iv-2469-000101:disabledAllowance>
<eahv-iv-2469-000101:dailyAllowance>0</eahv-iv-2469-000101:dailyAllowance>
</eahv-iv-2469-000101:pension>
</eahv-iv-2469-000101:pensionCategory>
<eahv-iv-2469-000101:hcLcaAllowance>0</eahv-iv-2469-000101:hcLcaAllowance>
<eahv-iv-2469-000101:lucrativeGrossIncome>0</eahv-iv-2469-000101:lucrativeGrossIncome>
<eahv-iv-2469-000101:hypotheticalGrossIncome>0</eahv-iv-2469-000101:hypotheticalGrossIncome>
<eahv-iv-2469-000101:totalPension>9999</eahv-iv-2469-000101:totalPension>
<eahv-iv-2469-000101:lppPension>9999</eahv-iv-2469-000101:lppPension>
<eahv-iv-2469-000101:foreignPension>0</eahv-iv-2469-000101:foreignPension>
<eahv-iv-2469-000101:otherIncomes>0</eahv-iv-2469-000101:otherIncomes>
<eahv-iv-2469-000101:lppWithdrawalAmount>0</eahv-iv-2469-000101:lppWithdrawalAmount>
<eahv-iv-2469-000101:patientContributionCategory>IN_ADDITION</eahv-iv-2469-000101:patientContributionCategory>
<eahv-iv-2469-000101:hcFlatHelp>9999</eahv-iv-2469-000101:hcFlatHelp>
<eahv-iv-2469-000101:otherExpenses>0</eahv-iv-2469-000101:otherExpenses>
<eahv-iv-2469-000101:disabledAllowanceRecipient>true</eahv-iv-2469-000101:disabledAllowanceRecipient>
<eahv-iv-2469-000101:residenceCosts>
<eahv-iv-2469-000101:residenceCostsLodging>9999</eahv-iv-2469-000101:residenceCostsLodging>
<eahv-iv-2469-000101:residenceCostsCare>0</eahv-iv-2469-000101:residenceCostsCare>
<eahv-iv-2469-000101:residenceCostsAssistance>9999</eahv-iv-2469-000101:residenceCostsAssistance>
<eahv-iv-2469-000101:residenceCostsPatientContribution>9999</eahv-iv-2469-000101:residenceCostsPatientContribution>
<eahv-iv-2469-000101:residenceCostsTotal>99999</eahv-iv-2469-000101:residenceCostsTotal>
<eahv-iv-2469-000101:residenceCostsConsidered>99999</eahv-iv-2469-000101:residenceCostsConsidered>
<eahv-iv-2469-000101:residencePatientContribution>9999</eahv-iv-2469-000101:residencePatientContribution>
<eahv-iv-2469-000101:residencePatientExpenses>99999</eahv-iv-2469-000101:residencePatientExpenses>
</eahv-iv-2469-000101:residenceCosts>
<eahv-iv-2469-000101:hcEffectiveHelp>9999</eahv-iv-2469-000101:hcEffectiveHelp>
</eahv-iv-2469-000101:personalCalculationElements>
</eahv-iv-2469-000101:person>
</eahv-iv-2469-000101:persons>
<eahv-iv-2469-000101:decisionIdPartnerDecision>TZ88k999</eahv-iv-2469-000101:decisionIdPartnerDecision>
</eahv-iv-2469-000101:decisionRef0>
</eahv-iv-2469-000101:decisions>
</eahv-iv-2469-000101:case>
</eahv-iv-2469-000101:content>
</eahv-iv-2469-000101:message>
I would like to have each person eahv-iv-2469-000101:person in a row. Usually, the persons are all in the node eahv-iv-2469-000101:decisionRef0 and this node occurs normally only once. However, there are some files as above, where there are several nodes eahv-iv-2469-000101:decisionRef0. This is the point, where my code doesn't work properly.
Here is my code snippet:
##################################################################################################
# Konvertierung von xml zu csv.
##################################################################################################
doc <- read_xml(path/my_file)
# Namespace bestimmen
nmsp <- c(doc = "http://www.eahv-iv.ch/xmlns/eahv-iv-2469-000101/2")
# alle relevanten Nodes auslesen
person <- xml_find_all(doc, "//doc:person", ns=nmsp)
###############
# zuerst lesen wir alle Variablen (children & grandchildren) im Node "person" aus.
###############
xml_path(xml_find_all(person, "eahv-iv-2469-000101:person"))
dfs_person<-lapply(seq_along(person), function(p) {
ch_recs <- xml2::xml_find_all(
doc,
# XPATH PARSES TWO SETS:
paste0(
"//eahv-iv-2469-000101:person[", p, "]/*[not(descendant::*)]|", # CHILDREN W/O DESCENDANTS
"//eahv-iv-2469-000101:person[", p, "]/*/*[not(descendant::*)]|",
"//eahv-iv-2469-000101:person[", p, "]/*/*/*[not(descendant::*)]|",
"//eahv-iv-2469-000101:person[", p, "]/*/*/*/*[not(descendant::*)]|",
"//eahv-iv-2469-000101:person[", p, "]/*/*/*/*/*[not(descendant::*)]|",
"//eahv-iv-2469-000101:person[", p, "]/*/*/*/*/*/*[not(descendant::*)]|",
"//eahv-iv-2469-000101:person[", p, "]/*/*/*/*/*/*/*[not(descendant::*)]|",
"//eahv-iv-2469-000101:person[", p, "]/*/*/*/*/*/*/*/*[not(descendant::*)]|",
"//eahv-iv-2469-000101:person[", p, "]/*/*/*/*/*/*/*/*/*[not(descendant::*)]|",
"//eahv-iv-2469-000101:person[", p, "]/*/*/*/*/*/*/*/*/*/*") # GRANDCHILDREN
)
# BIND NODE NAMES AND TEXT TO DATA FRAME
data.frame(rbind(setNames(
c(xml2::xml_text(ch_recs)),
c(xml2::xml_name(ch_recs))
)))
})
# alle Zeilen zusammenführen
df_person <- dplyr::bind_rows(dfs_person)
In this case, all the information of person2 are appended to person1, so that I have all the information in one row. It generates a second row, but it is empty.
Does anyone have an idea of how to solve this?