如何从数据库Schema XML生成嵌套字典（JSON）？

输入XML（file.xml） #

<?xml version="1.0" encoding="UTF-8" ?>
<project name="so_project" id="Project-9999">
    <schema name="database1">
        <table name="table1">
            <column name="foo" type="int"/>
            <column name="bar" type="string"/>
            <column name="details_resolution" type="array[object]">
                <column name="timestamp" type="timestamp"/>
                <column name="user_id" type="string"/>
                <column name="user_name" type="string"/>
            </column>
            <column name="details_closure" type="array[object]">
                <column name="timestamp" type="timestamp"/>
                <column name="auto_closure" type="bool"/>
            </column>
        </table>
    </schema>
    <schema name="database2">
        <table name="table1">
            <column name="foo" type="int"/>
            <column name="bar" type="string"/>
            <column name="details" type="array[object]">
                <column name="timestamp" type="timestamp"/>
                <column name="value" type="float"/>
            </column>
        </table>
    </schema>
</project>

期望输出的嵌套字典结构 #

{
    "database1": {
        "table1": {
            "foo": "int",
            "bar": "string",
            "details_resolution": {
                "timestamp": "timestamp",
                "user_id": "string",
                "user_name": "string"
            },
            "details_closure": {
                "timestamp": "timestamp",
                "auto_closure": "bool"
            }
        }
    },
    "database2": {
        "table1": {
            "foo": "int",
            "bar": "string",
            "details": {
                "timestamp": "timestamp",
                "value": "float"
            }
        }
    }
}

解决方案代码 #

使用Python内置的xml.etree.ElementTree模块解析XML，按层级遍历构建目标字典：

import xml.etree.ElementTree as ET

def parse_xml_to_dict(xml_path):
    tree = ET.parse(xml_path)
    root = tree.getroot()
    
    result = {}
    # 遍历每个schema（对应数据库）
    for schema in root.findall('schema'):
        db_name = schema.attrib['name']
        result[db_name] = {}
        
        # 遍历当前数据库下的所有表
        for table in schema.findall('table'):
            table_name = table.attrib['name']
            result[db_name][table_name] = {}
            
            # 遍历当前表下的所有列
            for column in table.findall('column'):
                col_name = column.attrib['name']
                col_type = column.attrib['type']
                
                # 处理嵌套列（类型为array[object]的情况）
                if col_type == 'array[object]':
                    nested_cols = {}
                    for nested_col in column.findall('column'):
                        nested_cols[nested_col.attrib['name']] = nested_col.attrib['type']
                    result[db_name][table_name][col_name] = nested_cols
                else:
                    result[db_name][table_name][col_name] = col_type
    
    return result

# 使用示例
if __name__ == '__main__':
    output = parse_xml_to_dict('file.xml')
    import json
    print(json.dumps(output, indent=4))

代码说明 #

1. 解析XML文件并获取根节点project
2. 外层遍历所有schema节点，提取数据库名作为第一级键
3. 每个数据库下遍历所有table节点，提取表名作为第二级键
4. 遍历表内的column节点：
   • 若列类型为array[object]，则遍历其内部子column，构建嵌套字典作为该列的值
   • 普通列直接将类型作为值存入字典

运行代码后，会输出与期望结构一致的嵌套字典，通过json.dumps格式化后输出更易读。

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