Assuming XML structure is constant and element/attributes are retrieved by the xpath expression in the same order

from lxml import etree
import pandas as pd

df_cols = ["part_number", "manufacturer", "name", "retail", "product"]
rows = []
tree = etree.parse('/home/luis/tmp/tmp.xml')
root = tree.getroot()
steps = tree.xpath('//product/attribute::*[name()="name" or name()="part_number" or name()="manufacturer_name"] | //product/URL/product/text() | //product/price/retail/text()')
i=0
d=dict()
for s in steps:

    if i == 0:
        d[df_cols[2]]=s
    if i == 1:
        d[df_cols[0]]=s
    if i == 2:
        d[df_cols[1]]=s
    if i == 3:
        d[df_cols[3]]=s
    if i == 4:
        d[df_cols[4]]=s
        rows.append(d)
        i=0
        d=dict()
        continue
    i+=1


out_df = pd.DataFrame(rows, columns = df_cols)

print(out_df.head())

Result:

     part_number              manufacturer                                               name                                             retail product
0     Champ Golf  19CHPSPWRCH1111111111101                   Champ Golf- Max Pro Spike Wrench  https://click.linksynergy.com/link?id=83wh4zNK...    9.99
1   Stinger Tees  19STGTEEMID3CO1111111101  Stinger Tees- 3" Stinger Pro XL Competition Ca...  https://click.linksynergy.com/link?id=83wh4zNK...    7.99
2     Vegas Golf  19VEGORIGIN1111111111101                          Vegas Golf- Original Game  https://click.linksynergy.com/link?id=83wh4zNK...   14.99
3  Ray Cook Golf  19RAYBALRET1111111111201      Ray Cook Golf- 12' Compact Cup Ball Retriever  https://click.linksynergy.com/link?id=83wh4zNK...   19.99

You start by reading the xml file and also making a placeholder file for you to write the output in a csv format (or any other text format - you might have to tweak the code a bit).

Then you specify the names of columns in your final dataframe (after you have parsed the xml file). But this information is already in your xml file anyways, so you just to make sure you understand the contents.

Lastly, loop over the entries and find the keywords (column names) to read and write to the csv.

Once done, you can read the csv using pd.read_csv('output.csv').

import xml.etree.ElementTree as ET
import csv

# Load and parse the XML file
tree = ET.parse('your_xml_file.xml')
root = tree.getroot()

# Define the CSV file and writer
csv_file = open('output.csv', 'w', newline='', encoding='utf-8')
csv_writer = csv.writer(csv_file)

# Write header row
header = ['column1', 'column2', 'column3', 'column4', 'column5']
csv_writer.writerow(header)

# Extract data and write to CSV
for id in root.findall('.//main_identifier'):
    column1_text = id.find('column1').text if id.find('column') is not None else ''
    column2_text = id.find('.//column2').text if id.find('.//column2') is not None else ''
    column3_text = id.find('.//column3').text if id.find('.//column3') is not None else ''
    column4 = id.find('.//column4').text if id.find('.//column4') is not None else ''
    column5_text = id.find('.//column5').text if id.find('.//column5') is not None else ''
    
    # Write data to CSV
    csv_writer.writerow([column1_text, column2_text, column3_text, column4_text, column5_text])

# Close the CSV file
csv_file.close()

As of v1.3, you can simply use:

pandas.read_xml(path_or_file)

You'll need a recursive function to flatten rows, and a mechanism for dealing with duplicate data.

This is messy and depending on the data and nesting, you may end up with rather strange dataframes.

import xml.etree.ElementTree as et
from collections import defaultdict
import pandas as pd


def flatten_xml(node, key_prefix=()):
    """
    Walk an XML node, generating tuples of key parts and values.
    """

    # Copy tag content if any
    text = (node.text or '').strip()
    if text:
        yield key_prefix, text

    # Copy attributes
    for attr, value in node.items():
        yield key_prefix + (attr,), value

    # Recurse into children
    for child in node:
        yield from flatten_xml(child, key_prefix + (child.tag,))


def dictify_key_pairs(pairs, key_sep='-'):
    """
    Dictify key pairs from flatten_xml, taking care of duplicate keys.
    """
    out = {}

    # Group by candidate key.
    key_map = defaultdict(list)
    for key_parts, value in pairs:
        key_map[key_sep.join(key_parts)].append(value)

    # Figure out the final dict with suffixes if required.
    for key, values in key_map.items():
        if len(values) == 1:  # No need to suffix keys.
            out[key] = values[0]
        else:  # More than one value for this key.
            for suffix, value in enumerate(values, 1):
                out[f'{key}{key_sep}{suffix}'] = value

    return out


# Parse XML with etree
tree = et.XML("""<?xml version="1.0"?>
<data>
    <country name="Liechtenstein">
        <rank>1</rank>
        <year>2008</year>
        <gdppc>141100</gdppc>
        <neighbor name="Austria" direction="E"/>
        <neighbor name="Switzerland" direction="W"/>
        <neighbor2 name="Italy" direction="S"/>
    </country>
    <country name="Singapore">
        <rank>4</rank>
        <year>2011</year>
        <gdppc>59900</gdppc>
        <neighbor name="Malaysia" direction="N"/>
        <cities>
            <city name="Chargin" population="1234" />
            <city name="Firin" population="4567" />
        </cities>
    </country>
    <country name="Panama">
        <rank>68</rank>
        <year>2011</year>
        <gdppc>13600</gdppc>
        <neighbor name="Costa Rica" direction="W"/>
        <neighbor name="Colombia" direction="E"/>
    </country>
</data>
""")

# Generate flat rows out of the root nodes in the tree
rows = [dictify_key_pairs(flatten_xml(row)) for row in tree]
df = pd.DataFrame(rows)
print(df)

outputs

            name rank  year   gdppc neighbor-name-1 neighbor-name-2 neighbor-direction-1 neighbor-direction-2 neighbor2-name neighbor2-direction neighbor-name neighbor-direction cities-city-name-1 cities-city-name-2 cities-city-population-1 cities-city-population-2
0  Liechtenstein    1  2008  141100         Austria     Switzerland                    E                    W          Italy                   S           NaN                NaN                NaN                NaN                      NaN                      NaN
1      Singapore    4  2011   59900             NaN             NaN                  NaN                  NaN            NaN                 NaN      Malaysia                  N            Chargin              Firin                     1234                     4567
2         Panama   68  2011   13600      Costa Rica        Colombia                    W                    E            NaN                 NaN           NaN                NaN                NaN                NaN                      NaN                      NaN

I hope this is the expected output:

import xml.etree.ElementTree as ET
import pandas as pd
data = 'example_shops.xml'
tree = ET.parse(data)
root = tree.getroot()
shops_items = []
all_shops_items = []
for ashop in root.iter('shop'):
    items = []
    shop_Nr = ashop.attrib.get('shopNr')
    for anitem in ashop.iter('ItemNr'):
        items.append(anitem.text)
    shops_items = [shop_Nr,items]
    all_shops_items.append(shops_items)
df = pd.DataFrame(all_shops_items,columns=['SHOP_NUMBER','ITEM_NUMBER'])        
print(df)

Output:

  SHOP_NUMBER                     ITEM_NUMBER
0          01  [1001, 1002, 1003, 1004, 1010]
1          02              [1002, 1006, 1005]
2          03        [1009, 1006, 1005, 1002]

If you want shops with individual items :

import xml.etree.ElementTree as ET
import pandas as pd
data = 'example_shops.xml'
tree = ET.parse(data)
root = tree.getroot()
shops_items = []
all_shops_items = []
for ashop in root.iter('shop'):
    shop_Nr = ashop.attrib.get('shopNr')
    for anitem in ashop.iter('ItemNr'):
        item_Nr = anitem.text
        shops_items = [shop_Nr,item_Nr]
        all_shops_items.append(shops_items)
df = pd.DataFrame(all_shops_items,columns=['SHOP_NUMBER','ITEM_NUMBER'])        
print(df)

output:

   SHOP_NUMBER ITEM_NUMBER
0           01        1001
1           01        1002
2           01        1003
3           01        1004
4           01        1010
5           02        1002
6           02        1006
7           02        1005
8           03        1009
9           03        1006
10          03        1005
11          03        1002

A lightweight xml_to_dict converter can be found here. It can be improved by this to handle namespaces.

def xml_to_dict(xml='', remove_namespace=True):
    """Converts an XML string into a dict

    Args:
        xml: The XML as string
        remove_namespace: True (default) if namespaces are to be removed

    Returns:
        The XML string as dict

    Examples:
        >>> xml_to_dict('<text><para>hello world</para></text>')
        {'text': {'para': 'hello world'}}

    """
    def _xml_remove_namespace(buf):
        # Reference: https://stackoverflow.com/a/25920989/1498199
        it = ElementTree.iterparse(buf)
        for _, el in it:
            if '}' in el.tag:
                el.tag = el.tag.split('}', 1)[1]
        return it.root

    def _xml_to_dict(t):
        # Reference: https://stackoverflow.com/a/10077069/1498199
        from collections import defaultdict

        d = {t.tag: {} if t.attrib else None}
        children = list(t)
        if children:
            dd = defaultdict(list)
            for dc in map(_xml_to_dict, children):
                for k, v in dc.items():
                    dd[k].append(v)
            d = {t.tag: {k: v[0] if len(v) == 1 else v for k, v in dd.items()}}

        if t.attrib:
            d[t.tag].update(('@' + k, v) for k, v in t.attrib.items())

        if t.text:
            text = t.text.strip()
            if children or t.attrib:
                if text:
                    d[t.tag]['#text'] = text
            else:
                d[t.tag] = text

        return d

    buffer = io.StringIO(xml.strip())
    if remove_namespace:
        root = _xml_remove_namespace(buffer)
    else:
        root = ElementTree.parse(buffer).getroot()

    return _xml_to_dict(root)

So let s be the string which holds your xml. We can convert it to a dict via

d = xml_to_dict(s, remove_namespace=True)

Now the solution is straight forward:

rows = []
for program in d['programs']['program']:
    cols = []
    cols.append(program['orgUnitId'])
    cols.append(program['programDescriptionText']['#text'])
    try:
        cols.append(','.join(program['requiredLevel']))
    except KeyError:
        cols.append('')

    try:
         searchwords = program['searchword']['#text']
    except TypeError:
         searchwords = []
         for searchword in program['searchword']:
            searchwords.append(searchword['#text'])
         searchwords = ','.join(searchwords)
    cols.append(searchwords)

    rows.append(cols)

df = pd.DataFrame(rows, columns=['organization', 'description', 'level', 'keyword'])