Python解析XML提取节点数据转DataFrame写入SQL Server

我是XML与Python初学者，目标是将大型XML文件解析后通过Python写入SQL Server数据库，以下是待处理XML的精简样例：

<?xml version="1.0" encoding="utf-8"?>
<MyOrgRefData:OrgRefData xmlns:MyOrgRefData="http://refdata.org/org/v2-0-0" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://refdata.org/org/v2-0-0/MyOrgRefData.xsd">
  <Manifest>
    <Version value="2-0-0" />
    <PublicationType value="Full" />
    <PublicationSource value="TEST123" />
    <PublicationDate value="2022-05-23" />
    <PublicationSeqNum value="1659" />
    <FileCreationDateTime value="2022-05-23T22:14:47" />
    <RecordCount value="287654" />
    <ContentDescription value="FullFile_20220523" />
    <PrimaryRoleScope>
      <PrimaryRole id="123" displayName="Free beer for me" />
      <PrimaryRole id="456" displayName="Free air for you" />
    </PrimaryRoleScope>
  </Manifest>
  <CodeSystems>
    <CodeSystem name="OrganisationRecordClass" oid="1.2.3.4.5">
      <concept id="RC2" code="2" displayName="World1" />
      <concept id="RC1" code="1" displayName="World2" />
    </CodeSystem>
    <CodeSystem name="OrganisationRole" oid="5.4.7.8">
      <concept id="B1ng0" code="179" displayName="BoomBastic" />
      <concept id="R2D2a" code="180" displayName="Fantastic" />
    </CodeSystem>
  </CodeSystems>
</MyOrgRefData:OrgRefData>

此前尝试使用lxml、pandas.read_xml、xml.etree等库解析，均未得到符合预期的结果，具体需求如下：

• 分别将<Manifest>、<CodeSystems>节点的数据提取为独立DataFrame，后续通过pd.to_sql()写入SQL Server，文件中其他未展示节点后续按相同逻辑处理
• 提取Manifest节点数据时，原有运行代码仅返回数字索引+value列的结果，无法获取对应节点标签名，期望输出可二选一：
  • 以Version、PublicationType等节点标签作为行索引，对应value属性值为内容
  • 以节点标签作为列名，属性值横向排列为单行数据
• Manifest节点下的<PrimaryRoleScope>子节点需单独提取为独立DataFrame，提取Manifest基础属性时排除该子节点

具体实现代码 #

由于XML带自定义命名空间，直接使用pandas.read_xml的默认xpath逻辑容易出现字段匹配错误，采用lxml做前置节点解析再转DataFrame的方案，逻辑可控且方便后续扩展其他节点。

import pandas as pd
from lxml import etree

# 配置文件路径
dataFolder = '/Some/directory'
# 加载XML文件
tree = etree.parse(dataFolder + 'Data_Full_20220523.xml')
root = tree.getroot()
# 定义XML命名空间映射，解决带前缀节点无法定位的问题
ns = {'org': 'http://refdata.org/org/v2-0-0'}

1. 提取Manifest基础属性（排除PrimaryRoleScope） #

# 定位Manifest根节点
manifest_node = root.xpath('//org:Manifest', namespaces=ns)[0]
manifest_base = {}

for child in manifest_node:
    # 跳过PrimaryRoleScope子节点
    if etree.QName(child).localname == 'PrimaryRoleScope':
        continue
    # 取节点本地名作为字段名，value属性作为字段值
    field_name = etree.QName(child).localname
    manifest_base[field_name] = child.get('value')

# 生成单行DataFrame：列名为节点名，值为对应属性值
df_manifest_base = pd.DataFrame([manifest_base])

# 如果需要节点名作为行索引的版本，执行以下转置代码即可
# df_manifest_base = df_manifest_base.T.reset_index()
# df_manifest_base.columns = ['field_name', 'value']

2. 提取PrimaryRoleScope独立DataFrame #

primary_role_list = []
for role in manifest_node.xpath('./org:PrimaryRoleScope/org:PrimaryRole', namespaces=ns):
    primary_role_list.append({
        'id': role.get('id'),
        'displayName': role.get('displayName')
    })
df_primary_role = pd.DataFrame(primary_role_list)

3. 提取CodeSystems全量数据 #

提取时会关联每个concept所属CodeSystem的name和oid属性，避免入库后数据断层：

codesystem_list = []
for cs in root.xpath('//org:CodeSystems/org:CodeSystem', namespaces=ns):
    cs_name = cs.get('name')
    cs_oid = cs.get('oid')
    # 遍历当前CodeSystem下的所有概念节点
    for concept in cs.xpath('./org:concept', namespaces=ns):
        codesystem_list.append({
            'code_system_name': cs_name,
            'code_system_oid': cs_oid,
            'concept_id': concept.get('id'),
            'concept_code': concept.get('code'),
            'concept_displayName': concept.get('displayName')
        })
df_codesystems = pd.DataFrame(codesystem_list)

以上生成的所有DataFrame均可直接传入pd.to_sql()写入SQL Server，后续处理其他同结构节点时，仅需调整xpath路径和待提取的属性字段即可。

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