如何将复杂嵌套XML提取为结构化表（SQL Server/Pandas方案）

DevOps 小满

2026-8-26

这个需求完全可以实现，以下提供两种可直接落地的实现方案，按需选择即可。

SQL Server 原生XQuery实现

该方案无需额外组件，直接在数据库内完成解析，适合需要直接在库内输出结果集的场景。

-- 示例：假设你的业务表名为YourTable，存储varchar格式XML的字段名为XmlColumn
WITH XmlData AS (
    SELECT CAST(XmlColumn AS XML) AS XmlContent
    FROM YourTable
    -- 可在此处添加筛选条件限定需要解析的数据范围
)
SELECT
    -- 提取ObjectAttribute节点的基础属性
    OA.value('@displayName', 'nvarchar(200)') AS Displayname,
    OA.value('@editMode', 'nvarchar(50)') AS editMode,
    OA.value('@name', 'nvarchar(200)') AS name,
    OA.value('@namedColumn', 'nvarchar(10)') AS namedColumn,
    OA.value('@type', 'nvarchar(50)') AS type,
    -- 聚合所有AttributeSource的name属性，多个值用换行分隔
    STUFF(
        OA.query('
            for $src in AttributeSource
            return concat($src/@name, "&#x0D;")
        ').value('.', 'nvarchar(max)'),
        LEN(OA.query('
            for $src in AttributeSource
            return concat($src/@name, "&#x0D;")
        ').value('.', 'nvarchar(max)')), 1, ''
    ) AS SourceName,
    -- 聚合Source App(s)关联信息
    STUFF(
        OA.query('
            for $src in AttributeSource
            return concat(
                "App: ", $src/ApplicationRef/Reference/@name,
                if ($src/RuleRef/Reference/@name) then concat(" | Rule: ", $src/RuleRef/Reference/@name) else "",
                "&#x0D;"
            )
        ').value('.', 'nvarchar(max)'),
        LEN(OA.query('
            for $src in AttributeSource
            return concat(
                "App: ", $src/ApplicationRef/Reference/@name,
                if ($src/RuleRef/Reference/@name) then concat(" | Rule: ", $src/RuleRef/Reference/@name) else "",
                "&#x0D;"
            )
        ').value('.', 'nvarchar(max)')), 1, ''
    ) AS [Source App(s)],
    -- 聚合Target App(s)关联信息
    STUFF(
        OA.query('
            for $tgt in AttributeTargets/AttributeTarget
            return concat(
                "Target Attr: ", $tgt/@name,
                " | App: ", $tgt/ApplicationRef/Reference/@name,
                if ($tgt/RuleRef/Reference/@name) then concat(" | Rule: ", $tgt/RuleRef/Reference/@name) else "",
                "&#x0D;"
            )
        ').value('.', 'nvarchar(max)'),
        LEN(OA.query('
            for $tgt in AttributeTargets/AttributeTarget
            return concat(
                "Target Attr: ", $tgt/@name,
                " | App: ", $tgt/ApplicationRef/Reference/@name,
                if ($tgt/RuleRef/Reference/@name) then concat(" | Rule: ", $tgt/RuleRef/Reference/@name) else "",
                "&#x0D;"
            )
        ').value('.', 'nvarchar(max)')), 1, ''
    ) AS [Target App(s)]
FROM XmlData
CROSS APPLY XmlContent.nodes('/List/ObjectAttribute') AS O(OA)

核心逻辑说明：

先通过CTE将varchar类型的原始字段统一转换为XML类型，避免重复类型转换
使用nodes()方法拆分所有<ObjectAttribute>节点，保证单个节点对应输出一行
基础属性直接通过value()方法读取节点属性值
子节点聚合通过XQuery的FLWOR语法循环遍历所有子节点，按规则拼接字段内容，用换行符分隔，最后通过STUFF函数移除末尾多余的换行
通过XQuery内置的if判断处理可选的RuleRef节点，无对应节点时自动跳过规则字段，适配子节点结构不固定的特征

Python/Pandas 实现

该方案逻辑更直观、易调试维护，适合数据量较大、需要后续做二次数据处理的场景。
首先安装依赖：

pip install pandas lxml

实现代码：

import pandas as pd
from lxml import etree

# 此处可替换为从数据库/文件读取XML数据的逻辑，示例直接使用提供的XML文本
xml_content = """
<List>
    <ObjectAttribute displayName="Unique Identity Number" editMode="ReadOnly" name="Uin" namedColumn="true" type="string"/>
    <ObjectAttribute displayName="Enterprise Account Name" editMode="ReadOnly" name="EnterpriseAccountName" namedColumn="true" type="string">
        <AttributeSource name="enterpriseAccountName">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="Active Directory"/>
            </ApplicationRef>
        </AttributeSource>
    </ObjectAttribute>
    <ObjectAttribute displayName="Termination Date" editMode="ReadOnly" name="TerminationDate" namedColumn="true" type="string">
        <AttributeSource name="TERMINATION_DATE">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 1"/>
            </ApplicationRef>
        </AttributeSource>
        <AttributeSource name="TerminationDate">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 2"/>
            </ApplicationRef>
        </AttributeSource>
    </ObjectAttribute>
    <ObjectAttribute displayName="Country" editMode="ReadOnly" name="Country" namedColumn="true" type="string">
        <AttributeSource name="AppRule: HR APPLICATION 1 Mapping Rule">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 1"/>
            </ApplicationRef>
            <RuleRef>
                <Reference class="sailpoint.object.Rule" name="HR APPLICATION 1 Mapping Rule"/>
            </RuleRef>
        </AttributeSource>
        <AttributeSource name="CountryTwoLetter">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 2"/>
            </ApplicationRef>
        </AttributeSource>
        <AttributeTargets>
            <AttributeTarget name="c" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="Active Directory"/>
                </ApplicationRef>
            </AttributeTarget>
            <AttributeTarget name="country" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="Destination App 1"/>
                </ApplicationRef>
            </AttributeTarget>
        </AttributeTargets>
    </ObjectAttribute>
    <ObjectAttribute displayName="Email" editMode="ReadOnly" name="email" standard="true" type="string">
        <AttributeSource name="AppRule: HR APPLICATION 1 Mapping Rule">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 1"/>
            </ApplicationRef>
            <RuleRef>
                <Reference class="sailpoint.object.Rule" name="HR APPLICATION 1 Mapping Rule"/>
            </RuleRef>
        </AttributeSource>
        <AttributeSource name="AppRule: HR APPLICATION 2 Mapping Rule">
            <ApplicationRef>
                <Reference class="sailpoint.object.Application" name="HR APPLICATION 2"/>
            </ApplicationRef>
            <RuleRef>
                <Reference class="sailpoint.object.Rule" name="HR APPLICATION 2 Mapping Rule"/>
            </RuleRef>
        </AttributeSource>
        <AttributeTargets>
            <AttributeTarget name="email" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="Destination App 1"/>
                </ApplicationRef>
            </AttributeTarget>
            <AttributeTarget name="Email" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="Destination App 2"/>
                </ApplicationRef>
            </AttributeTarget>
            <AttributeTarget name="Email" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="HR APPLICATION 2"/>
                </ApplicationRef>
            </AttributeTarget>
            <AttributeTarget name="EmailEffectiveDate" provisionAllAccounts="true">
                <ApplicationRef>
                    <Reference class="sailpoint.object.Application" name="HR APPLICATION 2"/>
                </ApplicationRef>
                <RuleRef>
                    <Reference class="sailpoint.object.Rule" name="HR APPLICATION 2 EmailEffectiveDate TargetTransformation Rule"/>
                </RuleRef>
            </AttributeTarget>
        </AttributeTargets>
    </ObjectAttribute>
</List>
"""

# 加载解析XML
root = etree.fromstring(xml_content.encode('utf-8'))
result = []

# 遍历所有ObjectAttribute节点
for oa in root.xpath('//ObjectAttribute'):
    # 读取基础属性
    row = {
        'Displayname': oa.get('displayName', ''),
        'editMode': oa.get('editMode', ''),
        'name': oa.get('name', ''),
        'namedColumn': oa.get('namedColumn', ''),
        'type': oa.get('type', '')
    }

    # 解析Source类字段
    source_names = []
    source_apps = []
    for src in oa.xpath('./AttributeSource'):
        source_names.append(src.get('name', ''))
        app_name = src.xpath('./ApplicationRef/Reference/@name')
        app_name = app_name[0] if app_name else ''
        rule_name = src.xpath('./RuleRef/Reference/@name')
        rule_part = f' | Rule: {rule_name[0]}' if rule_name else ''
        source_apps.append(f'App: {app_name}{rule_part}')
    
    row['SourceName'] = '\n'.join(source_names)
    row['Source App(s)'] = '\n'.join(source_apps)

    # 解析Target类字段
    target_apps = []
    for tgt in oa.xpath('./AttributeTargets/AttributeTarget'):
        tgt_name = tgt.get('name', '')
        app_name = tgt.xpath('./ApplicationRef/Reference/@name')
        app_name = app_name[0] if app_name else ''
        rule_name = tgt.xpath('./RuleRef/Reference/@name')
        rule_part = f' | Rule: {rule_name[0]}' if rule_name else ''
        target_apps.append(f'Target Attr: {tgt_name} | App: {app_name}{rule_part}')
    
    row['Target App(s)'] = '\n'.join(target_apps)
    result.append(row)

# 转换为DataFrame，可直接打印或导出为文件
df = pd.DataFrame(result)
print(df)
# 导出到Excel示例：df.to_excel('xml_parse_result.xlsx', index=False)

核心逻辑说明：

用lxml库解析XML结构，XPath语法比SQL Server内置XQuery更简洁
逐一遍历每个<ObjectAttribute>节点，不存在的属性/子节点自动返回空值，完全适配节点结构不固定的情况
同列下多个条目用\n换行拼接，导出到Excel等文件时会自动识别为单元格内换行
后续如果需要调整字段规则、新增解析字段，修改成本远低于SQL脚本

注意：两种方案生成的单元格内换行，在SSMS查询结果预览、Pandas终端打印时可能显示为空格，导出为Excel/CSV文件后会正常展示为单元格内换行效果。

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