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

# 🔹 Set your file paths here
xml_file = r"C:\Users\YourName\Documents\input.xml"   # full path to your XML
excel_file = r"C:\Users\YourName\Documents\output.xlsx"  # where Excel will be saved

def xml_to_excel(xml_file, excel_file):
    tree = ET.parse(xml_file)
    root = tree.getroot()

    # Try to guess the repeating entry (the most common child tag under root)
    children_tags = [child.tag for child in root]
    if not children_tags:
        raise ValueError("XML has no child nodes under root.")
    entry_tag = max(set(children_tags), key=children_tags.count)

    data = []
    for entry in root.findall(entry_tag):
        row = {}

        # Add attributes (if any)
        for attr_name, attr_value in entry.attrib.items():
            row[attr_name] = attr_value

        # Add child elements (dynamic)
        for child in entry:
            row[child.tag] = child.text.strip() if child.text else ""

        data.append(row)

    if not data:
        raise ValueError(f"No entries found for tag '{entry_tag}'")

    # Convert to DataFrame and save
    df = pd.DataFrame(data)
    df.to_excel(excel_file, index=False)

    print(f"✅ Parsed XML saved to {excel_file}")
    print(f"Detected root: <{root.tag}> | Entry tag: <{entry_tag}> | Columns: {list(df.columns)}")

# Run conversion
xml_to_excel(xml_file, excel_file)