[Python] Importing data from two XML parent nodes to a Pandas DataFrame using read_xml

I am having trouble in importing an XML file to Pandas where I need to grab data from two parent nodes. One parent node (AgentID) has data directly in it, and the other (Sales) has child nodes (Location, Size, Status) that contain data, as given below.

test_xml = '''<TEST_XML>
<Sales>
<AgentID>0001</AgentID>
<Sale>
<Location>0</Location>
<Size>1000</Size>
<Status>Available</Status>
</Sale>
<Sale>
<Location>1</Location>
<Size>500</Size>
<Status>Unavailable</Status>
</Sale>
</Sales>
</TEST_XML>'''


when I try to import this to a Pandas Dataframe below is the only way I was able to grab data under the Sale tag.

import pandas as pd
df = pd.read_xml(test_xml, xpath='//Sale')


which gives me a dataframe like the one below:

Location Size Status
0 0 1000 Available
1 1 500 Unavailable


What I need is including the AgentID tag in the DataFrame too, to get the following, but I was unsuccessful. Expected output is given below for clarity:

AgentID Location Size Status
0 0001 0 1000 Available
1 0001 1 500 Unavailable


Is there a way to manipulate the xpath parameter to include the data inside the AgentID tag as well, or is it impossible to do it using Pandas' read_xml function? I tried passing a list like xpath=['//AgentID', '//Sale'] but of course, it did not work...

Continue reading...