如何将嵌套XML中的Entity数据提取为Python DataFrame？

无法将嵌套XML中的数据提取转换为Python DataFrame。XML样本包含多个Entity节点，每个节点对应目标结果中的一行数据（以下为单个Entity实例）：

<EntityCollection>

  <Entities>

    <Entity>

      <Attributes>

        <KeyValuePairOfstringanyType>

          <key>client_number</key>

          <value type="b:string">ABC123345</value>

        </KeyValuePairOfstringanyType>

        <KeyValuePairOfstringanyType>

          <key>my_data.client_type</key>

          <value type="AliasedValue">

            <AttributeLogicalName>my_type</AttributeLogicalName>

            <EntityLogicalName>my_data1</EntityLogicalName>

            <NeedFormatting>true</NeedFormatting>

            <ReturnType>123</ReturnType>

            <Value type="OptionSetValue">

              <Value>345</Value>

            </Value>

          </value>

        </KeyValuePairOfstringanyType>

        <KeyValuePairOfstringanyType>

          <key>my_data.status</key>

          <value type="AliasedValue">

            <AttributeLogicalName>status</AttributeLogicalName>

            <EntityLogicalName>my_data1</EntityLogicalName>

            <NeedFormatting>true</NeedFormatting>

            <ReturnType>123</ReturnType>

            <Value type="OptionSetValue">

              <Value>67</Value>

            </Value>

          </value>

        </KeyValuePairOfstringanyType>

        <KeyValuePairOfstringanyType>

          <key>my_data.date</key>

          <value type="AliasedValue">

            <AttributeLogicalName>date</AttributeLogicalName>

            <EntityLogicalName>my_data1</EntityLogicalName>

            <NeedFormatting>true</NeedFormatting>

            <ReturnType>89</ReturnType>

            <Value type="b:string">2024-01-01</Value>

          </value>

        </KeyValuePairOfstringanyType>

        <KeyValuePairOfstringanyType>

          <key>my_data.country</key>

          <value type="AliasedValue">

            <AttributeLogicalName>country</AttributeLogicalName>

            <EntityLogicalName>my_data1</EntityLogicalName>

            <NeedFormatting>true</NeedFormatting>

            <ReturnType>123</ReturnType>

            <Value type="OptionSetValue">

              <Value>456</Value>

            </Value>

          </value>

        </KeyValuePairOfstringanyType>

        <KeyValuePairOfstringanyType>

          <key>client_code</key>

          <value type="b:guid">some_code456</value>

        </KeyValuePairOfstringanyType>

        <KeyValuePairOfstringanyType>

          <key>my_data.my_data1id</key>

          <value type="AliasedValue">

           <AttributeLogicalName>my_data1id</AttributeLogicalName>

            <EntityLogicalName>my_data1</EntityLogicalName>

            <NeedFormatting>true</NeedFormatting>

            <ReturnType>13</ReturnType>

            <Value type="b:guid">some_code123</Value>

          </value>

        </KeyValuePairOfstringanyType>

      </Attributes>

      <EntityState nil="true"/>

      <FormattedValues>

        <KeyValuePairOfstringstring>

          <key>my_data.client_type</key>

          <value>client123</value>

        </KeyValuePairOfstringstring>

        <KeyValuePairOfstringstring>

          <key>my_data.status</key>

          <value>OK</value>

        </KeyValuePairOfstringstring>

        <KeyValuePairOfstringstring>

          <key>my_data.country</key>

          <value>some_country</value>

        </KeyValuePairOfstringstring>

      </FormattedValues>

      <Id>some_code456</Id>

      <KeyAttributes/>

      <LogicalName>some-id</LogicalName>

      <RelatedEntities/>

      <RowVersion>ID123</RowVersion>

    </Entity>

</Entities>

</EntityCollection>

要求将key或AttributeLogicalName作为表头，对应的value/Value作为实际数据生成DataFrame（预期结果为一行结构化数据）。尝试用xml.etree处理，写出以下代码后不知如何继续：

for _, elem in ET.iterparse(my_xml):
    if len(elem) == 0:
        print(f'{elem.tag} {elem.attrib} text={elem.text}')
    else:
        print(f'{elem.tag} {elem.attrib}')

要实现需求，你可以按以下步骤解析XML并转换为DataFrame：

步骤1：导入所需库 #

import xml.etree.ElementTree as ET
import pandas as pd

步骤2：编写单个Entity节点解析函数 #

定义函数提取单个Entity内的所有目标数据，处理嵌套的AliasedValue类型字段：

def parse_entity(entity):
    data_dict = {}
    
    # 解析Attributes中的键值对
    attributes = entity.find('Attributes')
    for kv_pair in attributes.findall('KeyValuePairOfstringanyType'):
        key = kv_pair.find('key').text
        value_elem = kv_pair.find('value')
        
        # 处理AliasedValue类型的嵌套字段
        if value_elem.attrib.get('type') == 'AliasedValue':
            # 用AttributeLogicalName作为列名
            col_name = value_elem.find('AttributeLogicalName').text
            # 提取最内层的Value值
            nested_value = value_elem.find('Value').find('Value').text
            data_dict[col_name] = nested_value
        else:
            # 普通类型直接取值
            data_dict[key] = value_elem.text
    
    # 可选：替换为FormattedValues中的格式化显示值（比如用client123代替345）
    formatted_values = entity.find('FormattedValues')
    if formatted_values is not None:
        for fmt_kv in formatted_values.findall('KeyValuePairOfstringstring'):
            fmt_key = fmt_kv.find('key').text
            # 匹配对应的AttributeLogicalName
            for attr_kv in attributes.findall('KeyValuePairOfstringanyType'):
                if attr_kv.find('key').text == fmt_key:
                    col_name = attr_kv.find('value').find('AttributeLogicalName').text
                    data_dict[col_name] = fmt_kv.find('value').text
                    break
    
    # 可选：提取Entity顶层的额外字段
    data_dict['Id'] = entity.find('Id').text
    data_dict['LogicalName'] = entity.find('LogicalName').text
    data_dict['RowVersion'] = entity.find('RowVersion').text
    
    return data_dict

步骤3：遍历所有Entity并生成DataFrame #

# 加载XML文件（如果是XML字符串，改用ET.fromstring(xml_content)）
tree = ET.parse('your_xml_file.xml')
root = tree.getroot()

# 收集所有Entity的数据
all_entity_data = []
for entity in root.findall('.//Entity'):
    all_entity_data.append(parse_entity(entity))

# 转换为DataFrame
df = pd.DataFrame(all_entity_data)
print(df)

关键说明 #

• 对于AliasedValue类型字段，优先使用AttributeLogicalName作为列名，提取最内层Value的文本值；
• 若需要显示友好的格式化值（如OK代替67），保留FormattedValues的替换逻辑即可；
• 每个Entity节点对应DataFrame的一行，自动适配多个Entity的场景。

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