使用R的xml2与tidyverse提取XML不同层级值转tibble求助

核心逻辑：以单个department节点为单位遍历提取数据，使用相对路径XPath限定搜索范围，避免全局查询导致的字段错位，缺失节点自动填充NA。

完整可运行代码 #

library(tidyverse)
library(xml2)

# 示例XML读取
interimxml <- read_xml("<report>
                          <hospital>
                            <hospital_contact>
                              <name>University Hospital Sidney</name>
                              <hospital_id>1234</hospital_id>
                              <location>Sidney</location>
                            </hospital_contact>
                         </hospital>
                         <hospital_departments>
                          <department>
                            <Name>Neurosurgery</Name>
                            <department_key>20010</department_key>
                            <cases_department>
                              <full_stationary>100</full_stationary>
                              <parttime_inpatient>50</parttime_inpatient>
                            </cases_department>
                            <staff>
                              <doctors>
                                <occupancy_doctors>2</occupancy_doctors>
                                <specialist>10</specialist>
                              </doctors>
                              <care>
                                <nurses>10</nurses>
                                <midwives>0</midwives>
                              </care>
                            </staff>
                            <procedures_done>
                              <mandatory_disclosure>
                                <procedure>
                                  <procedure_id> a.10 </procedure_id>
                                  <amount>10</amount>
                                </procedure>
                                <procedure>
                                  <procedure_id> b.15 </procedure_id>
                                  <amount>12</amount>
                                </procedure>
                              </mandatory_disclosure>
                            </procedures_done>
                          </department>
                          <department>
                            <Name>Pediatrics</Name>
                            <department_key>10020</department_key>
                            <cases_department>
                              <full_stationary>
                              140
                              </full_stationary>
                              <parttime_inpatient>
                              40
                              </parttime_inpatient>
                            </cases_department>
                            <staff>
                              <doctors>
                                <occupancy_doctors></occupancy_doctors>
                                <specialist>20</specialist>
                              </doctors>
                              <care>
                                <nurses>20</nurses>
                                <midwives>2</midwives>
                              </care>
                            </staff>
                            <procedures_done>
                              <mandatory_disclosure>
                                <procedure>
                                  <procedure_id> c.14 </procedure_id>
                                  <amount>5</amount>
                                </procedure>
                                <procedure>
                                  <procedure_id> c.19 </procedure_id>
                                  <amount>20</amount>
                                </procedure>
                              </mandatory_disclosure>
                            </procedures_done>
                          </department>
                          <department>
                            <Name>Stroke_Unit</Name>
                            <department_key>12018</department_key>
                            <cases_department>
                              <full_stationary>
                              20
                              </full_stationary>
                            </cases_department>
                            <staff>
                              <doctors>
                                <specialist>20</specialist>
                              </doctors>
                              <care>
                                <nurses>20</nurses>
                                <midwives>0</midwives>
                            </care>
                            </staff>
                            <procedures_done>
                              <mandatory_disclosure>
                                <procedure>
                                  <procedure_id> g.12 </procedure_id>
                                  <amount>20</amount>
                                </procedure>
                                <procedure>
                                  <procedure_id> c.19 </procedure_id>
                                  <amount>20</amount>
                                </procedure>
                              </mandatory_disclosure>
                            </procedures_done>
                          </department>
                          <department>
                            <Name>Intensive_Care</Name>
                            <department_key>19201</department_key>
                            <cases_department>
                              <full_stationary>
                                190
                              </full_stationary>
                            </cases_department>
                            <staff>
                              <doctors>
                                <specialist>20</specialist>
                              </doctors>
                              <care>
                                <nurses>90</nurses>
                              </care>
                            </staff>
                          <procedures_done>
                            <mandatory_disclosure>
                              <noprocedures/>
                            </mandatory_disclosure>
                          </procedures_done>
                         </department>
                          </hospital_departments>
                         </report>")

# 提取医院公共信息
hospital_name <- xml_find_first(interimxml, "//hospital_contact/name") |> xml_text(trim = TRUE)
hospital_id <- xml_find_first(interimxml, "//hospital_contact/hospital_id") |> xml_text(trim = TRUE) |> as.integer()

# 获取所有科室节点
dept_nodes <- xml_find_all(interimxml, "//department")

# 生成科室详情表
hospital_departments <- map_dfr(dept_nodes, function(node) {
  tibble(
    hospital_name = hospital_name,
    hospital_id = hospital_id,
    department_name = xml_find_first(node, "./Name") |> xml_text(trim = TRUE) |> str_replace("_", " "),
    department_key = xml_find_first(node, "./department_key") |> xml_text(trim = TRUE),
    full_stationary = xml_find_first(node, "./cases_department/full_stationary") |> xml_text(trim = TRUE) |> as.integer(),
    parttime_inpatient = xml_find_first(node, "./cases_department/parttime_inpatient") |> xml_text(trim = TRUE) |> as.integer(),
    occupancy_doctors = xml_find_first(node, "./staff/doctors/occupancy_doctors") |> xml_text(trim = TRUE) |> 
      (\(x) ifelse(length(x) == 0 || x == "", NA_integer_, as.integer(x)))(),
    specialists = xml_find_first(node, "./staff/doctors/specialist") |> xml_text(trim = TRUE) |> as.integer(),
    nurses = xml_find_first(node, "./staff/care/nurses") |> xml_text(trim = TRUE) |> as.integer(),
    midwives = xml_find_first(node, "./staff/care/midwives") |> xml_text(trim = TRUE) |> 
      (\(x) ifelse(length(x) == 0 || x == "", NA_integer_, as.integer(x)))()
  )
})

# 生成手术操作表
department_procedures <- map_dfr(dept_nodes, function(node) {
  dept_key <- xml_find_first(node, "./department_key") |> xml_text(trim = TRUE)
  proc_nodes <- xml_find_all(node, ".//procedure")
  if (length(proc_nodes) == 0) return(NULL)
  map_dfr(proc_nodes, function(pnode) {
    tibble(
      hospital_name = hospital_name,
      hospital_key = hospital_id,
      department_key = dept_key,
      procedure_id = xml_find_first(pnode, "./procedure_id") |> xml_text(trim = TRUE),
      amount = xml_find_first(pnode, "./amount") |> xml_text(trim = TRUE) |> as.integer()
    )
  })
})

实现要点 #

• 所有字段查询使用./开头的相对路径XPath，限定在当前遍历的科室节点下搜索，不会跨节点匹配其他科室的字段，从根源避免数据错位
• 对不存在的节点、内容为空的节点统一转换为NA，自动适配tibble的列结构，无需额外补全
• 遍历逻辑可直接扩展到批量XML文件处理，仅需在外层增加文件读取循环即可

内容的提问来源于stack exchange，提问作者minimouse