Bash XML转CSV脚本非完全动态且输出内容为空问题排查

预期输出 #

需要生成两个符合规范的CSV文件：

faculty.csv

faculty,building,room,capacity
"Faculty of Art","B59","R1000","240"
"Faculty of Art","B59","R1001","360"
"Faculty of Art","B59","R1002","210"
...

students.csv

student_name,student_id,student_email,programme,year,address,contact,module_id,module_name,module_leader,lecturer1,lecturer2,faculty,building,room,exam_mark,coursework1,coursework2,coursework3
"Michael Jennings","273644","MJ1Y24@UoNEHT.ac.uk","Fine Art","4","08832 Kim Groves Suite 335 North Mathewview, RI 43333","3976680297","FINE4401","Theoretical Sketching","Maria Tyler","","","Faculty of Art","B81","R1002","","33","7","26"
...

当前脚本 #

#!/bin/bash

# Check for input file
if [[ -z "$1" ]]; then
    echo "Usage: $0 <input.xml> [output.csv]"
    exit 1
fi

input_xml="$1"
output_csv="${2:-${input_xml%.xml}.csv}"

# Extract unique tags (excluding XML declaration and root tags)
tags=$(grep -oP '<\K[^/]+(?=>[^<]+</[^>]+>)' "$input_xml" | grep -vP '^(faculties|students|\?xml)' | sort | uniq | tr '\n' ',' | sed 's/,$//')

# Create CSV header
echo "$tags" > "$output_csv"

# Process data records
awk -v tags="$tags" '
    BEGIN {
        FS="[<>]"
        OFS=","
        split(tags, header, /,/)
        gsub(/ /, "", header[1])  # Remove any accidental spaces
    }
    /<faculty>/,</faculty>/ || /<student>/,</student>/ {
        if ($2 in header) values[$2] = $3
        if ($0 ~ /<\/faculty>|<\/student>/) {
            for (i=1; i<=length(header); i++) {
                printf "%s%s", (header[i] in values ? values[header[i]] : ""), (i<length(header) ? OFS : ORS)
            }
            delete values
        }
    }
' "$input_xml" >> "$output_csv"

echo "CSV generated: $output_csv"

XML文件结构 #

faculty.xml

<?xml version="1.0" ?>
<faculties>
  <faculty>
    <faculty>Faculty of Art</faculty>
    <building>B59</building>
    <room>R1000</room>
    <capacity>240</capacity>
  </faculty>
  ...
</faculties>

students.xml

<?xml version="1.0" ?>
<students>
  <student>
    <student_name>Michael Jennings</student_name>
    <student_id>273644</student_id>
    <student_email>MJ1Y24@UoNEHT.ac.uk</student_email>
    <programme>Fine Art</programme>
    <year>4</year>
    <address>08832 Kim Groves Suite 335 North Mathewview, RI 43333</address>
    <contact>3976680297</contact>
    <module_id>FINE4401</module_id>
    <module_name>Theoretical Sketching</module_name>
    <module_leader>Maria Tyler</module_leader>
    <lecturer1/>
    <lecturer2/>
    <faculty>Faculty of Art</faculty>
    <building>B81</building>
    <room>R1002</room>
    <exam_mark/>
    <coursework1>33</coursework1>
    <coursework2>7</coursework2>
    <coursework3>26</coursework3>
  </student>
  ...
</students>

存在的问题 #

• 脚本通用性不足，依赖硬编码排除标签，无法自动适配不同XML结构
• 输出CSV为空或格式异常：空标签被忽略、字段未加引号（含特殊字符的内容会破坏CSV结构）、记录范围匹配逻辑错误（内部同名标签导致范围混乱）

补充说明 #

要求避免使用xmllint等外部工具，优先通用方案，半通用方案也可接受。

优化后的脚本 #

脚本功能 #

• 自动识别XML中的记录标签（如faculty、student）
• 提取所有标签（包括空标签）作为CSV表头
• 自动给字段添加双引号，处理含逗号、空格的内容
• 针对不同XML文件生成对应名称的CSV（如faculty.xml生成faculty.csv）

#!/bin/bash

# 检查输入文件
if [[ -z "$1" ]]; then
    echo "用法: $0 <input.xml>"
    exit 1
fi

input_xml="$1"
# 自动生成输出文件名，比如faculty.xml -> faculty.csv
output_csv="${input_xml%.xml}.csv"

# 第一步：提取根标签下的记录标签（如faculty、student）
record_tag=$(grep -oP '<\K[^/]+(?=>\s*<)' "$input_xml" | grep -vP '^\?xml' | head -n1)
if [[ -z "$record_tag" ]]; then
    echo "无法识别XML中的记录标签"
    exit 1
fi

# 第二步：提取所有唯一标签（包括空标签），排除根标签和XML声明
all_tags=$(grep -oP '<\K[^/]+(?=/>|>[^<]*</)' "$input_xml" | grep -vP "^($record_tag?s|\?xml)$" | sort | uniq)
# 转换为逗号分隔的表头
header=$(echo "$all_tags" | tr '\n' ',' | sed 's/,$//')

# 写入CSV表头
echo "$header" > "$output_csv"

# 第三步：处理XML记录，生成CSV内容
awk -v record_tag="$record_tag" -v header="$header" '
    BEGIN {
        FS="[<>]"
        OFS=","
        split(header, fields, /,/)
        # 建立字段索引，快速查找
        for (i in fields) {
            field_index[fields[i]] = i
        }
        field_count = length(fields)
    }
    # 匹配记录开始标签，初始化值数组
    $2 == record_tag {
        delete record_values
        in_record = 1
        next
    }
    # 匹配记录结束标签，输出一行CSV
    $2 == "/" record_tag {
        in_record = 0
        # 遍历所有字段，输出对应值（空值输出""）
        for (i=1; i<=field_count; i++) {
            field = fields[i]
            value = (field in record_values) ? record_values[field] : ""
            # 给值加双引号，处理内部双引号（如果有的话）
            printf "\"%s\"%s", gensub(/"/, "\"\"", "g", value), (i < field_count ? OFS : ORS)
        }
        next
    }
    # 在记录内部时，提取标签和值
    in_record && $2 ~ /^[^/]+$/ {
        tag = $2
        # 处理空标签（如<lecturer1/>）
        if ($0 ~ />$/) {
            record_values[tag] = ""
        } else {
            record_values[tag] = $3
        }
    }
' "$input_xml" >> "$output_csv"

echo "CSV已生成: $output_csv"

脚本使用示例 #

# 处理faculty.xml，生成faculty.csv
./xml2csv.sh faculty.xml

# 处理students.xml，生成students.csv
./xml2csv.sh students.xml

优化点说明 #

1. 自动识别记录标签：通过匹配根标签后的第一个子标签，无需硬编码faculty或student
2. 完整提取标签：同时匹配有内容的标签和空标签，确保表头包含所有字段
3. CSV规范兼容：所有字段添加双引号，内部双引号自动转义，避免特殊字符破坏结构
4. 正确的记录范围处理：通过in_record标记区分是否在记录内部，避免内部同名标签干扰
5. 空值处理：空标签或缺失字段自动填充为空字符串，符合CSV格式要求

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