Parsing XML not finding child entity

Hello, I am trying to create a python script which extract the XML value stored in a SQL database table and then extract particular fields from the XML. So far i have managed to create a script which will get the XML value, get the data from the top levels. However i am having trouble extracting data from the reference section (i have included a picture of the XML and highlighted the section i am trying to export). Below is the script i am using it works up until “message =” section and then reference number variable keeps coming back blank as it cannot find it. Does anyone know what i am doing wrong as it just stops finding things when it gets to the sections with the XLMS parts in the attributes.

import openpyxl
from openpyxl.utils.dataframe import dataframe_to_rows
import pyodbc
from xml.etree import ElementTree as ET
import re
import datetime
from datetime import datetime

def XMLExistsCheck(XMLSec, XMLValue, IsAtt):
    '''
    Checks whether XML tag requested is in the XML script

    Args:
        res (str): String version of response XML.
        XMLSec: this is the XML Section the value to be found in
        XMLValue: this is the XML value which is being looked for
        IsAtt: if its an attribute being looked for then its 1 else 0

    Returns:
        if it finds the value then the string 'found' is returned
    '''
    import xml.etree.ElementTree as ET
    import re
    # Compile once on import. Regular expression to strip XML namespace.
    # We don't need it and it amkes aprsing wqith elementree a pain.
    strip_namespace_regex = re.compile(' xmlns="[^"]+"')
    if IsAtt > 0:
        if XMLValue in XMLSec.attrib:
            return 'found'
    else:
        if XMLValue == '':
            if XMLSec.text is not None:
                return 'found'
        if XMLSec.find(XMLValue) is not None:
            return 'found'


cnxn = pyodbc.connect(r'Driver=SQL Server;Server=server01;Database=testXMLDB;Trusted_Connection=yes;')
cursor = cnxn.cursor()
cursor.execute("SELECT TOP 1 * FROM XMLtesttable WHERE YEAR(LoadedDateTime) > 2023")
while 1:
    row = cursor.fetchone()
    if not row:
        break
    COMMDATE = row.LoadedDateTime
    COMMXML = row.XMLData
    root = ET.fromstring(COMMXML)
    #root = tree.getroot()
    Addressee = root.findall('.//Addressee')
    for Addressee in Addressee:
        ComName = Addressee.attrib['CompanyName'] if XMLExistsCheck(Addressee,'CompanyName',1) == 'found' else ''
    Address = Addressee.findall('.//Address')
    for Address in Address:
        ComPostcode = Address.attrib['PostCode'] if XMLExistsCheck(Address,'PostCode',1) == 'found' else ''
    Content = root.findall('.//Content')
    for Content in Content:
        CommonExtract = Content.findall('.//CommonExtract')
        for CommonExtract in CommonExtract:
            SystemDate = CommonExtract.attrib['SystemDate'] if XMLExistsCheck(CommonExtract,'SystemDate',1) == 'found' else ''
            SystemDateconvert = datetime.strptime(SystemDate, '%Y-%m-%d')
            SystemDateYear = SystemDateconvert.year
            message = CommonExtract.findall('.//{http://www.origostandards.com/schema/soap/v1}message')
            for message in message:
                m_content = message.findall('.//{http://www.origostandards.com/schema/ReceiveRemunerationStatement/v1/LoadStatementRequest}m_content')
                for m_content in m_content:
                    payee_firm = m_content.findall('.//{http://www.origostandards.com/schema/ReceiveRemunerationStatement/v1/LoadStatementRequest}payee_firm')
                    for payee_firm in payee_firm:
                        reference = payee_firm.findall('.//reference')
                        for reference in reference:
                            reference_number = reference.find('reference_number').text
    print(ComName)
    print(ComPostcode)
    print(SystemDate)
    print(SystemDateYear)
cursor.close()
cnxn.close()

Please replace the screen shot of the xml with preformatter text.
Its too hard to read the image.

below is the XML, sorry i through image might help make it clearer

<Publication>
  <Addressee SubscriberReference="XXX000XXX" CompanyName="TEST LTD" CompanyNumber="">
    <Address PostalAddressRef="A00000001" FlatNumber="" BuildingName="" StreetNumber="" PostCode="TEST" AddressLine1="TEST" AddressLine2="" AddressLine3="" AddressLine4="TEST" AddressLine5="TEST" CountryCode="" POBoxNumber="" CountryCodeDesc="" UKStandardAddressInd="false" AddressLocationTypeId="B" AddressLocationTypeDesc="British" SystemEventRef="" SubEventRef="0000G.000Q" EndingSubEventRef="" SystemEventId="0" SystemEvent="Unknown" />
  </Addressee>
  <Content>
    <CommonExtract SystemDate="2023-12-05" SystemProcessRef="00000XX00X">
      <BankDetail BankSortCode="XX-XX-XX" BankAccountNumber="00000000" RollNumber="" BankAccountName="TEST LTD" BankName="" BranchName="" AddressLine1="" AddressLine2="" AddressLine3="" AddressLine4="" Town="" County="" PostCode="" TelePhone="" CountryCode="" />
      <message xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="http://www.origostandards.com/schema/soap/v1">
        <m_control>
          <control_timestamp>2023-12-05T22:33:37.7290361</control_timestamp>
          <message_id>XXX</message_id>
          <message_type>Remuneration Statement</message_type>
          <message_version>/schema/2.0/RPAlert.XSD</message_version>
          <expected_response_type>none</expected_response_type>
          <initiator_id></initiator_id>
        </m_control>
        <m_content xmlns="http://www.origostandards.com/schema/ReceiveRemunerationStatement/v1/LoadStatementRequest">
          <reference xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1"></reference>
          <alternative_statement_reference xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1"></alternative_statement_reference>
          <product_provider_name xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1"></product_provider_name>
          <date xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1">2023-12-05</date>
          <currency xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1">GBP</currency>
          <adviser_charge_total xmlns:p5="http://www.origostandards.com/schema/RemunerationStatement/v1" xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1" p5:currency="GBP">0.00</adviser_charge_total>
          <amount_released xmlns:p5="http://www.origostandards.com/schema/RemunerationStatement/v1" xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1" p5:currency="GBP">0.00</amount_released>
          <closing_balance xmlns:p5="http://www.origostandards.com/schema/RemunerationStatement/v1" xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1" p5:currency="GBP">0.00</closing_balance>
          <commission_total xmlns:p5="http://www.origostandards.com/schema/RemunerationStatement/v1" xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1" p5:currency="GBP">0.00</commission_total>
          <consultancy_charge_total xmlns:p5="http://www.origostandards.com/schema/RemunerationStatement/v1" xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1" p5:currency="GBP">0.00</consultancy_charge_total>
          <opening_balance xmlns:p5="http://www.origostandards.com/schema/RemunerationStatement/v1" xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1" p5:currency="GBP">0.00</opening_balance>
          <remuneration_total xmlns:p5="http://www.origostandards.com/schema/RemunerationStatement/v1" xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1" p5:currency="GBP">0.00</remuneration_total>
          <payee_firm xmlns="http://www.origostandards.com/schema/RemunerationStatement/v1">
            <company_name>NAME</company_name>
            <reference>
              <type>FSA</type>
              <reference_number>999999</reference_number>
            </reference>
          </payee_firm>
        </m_content>
      </message>
    </CommonExtract>
  </Content>
</Publication>

Can you write a short python program that only does the xml parse logic on that piece of xml? Having odbc stuff means others cannot run the code.

does the below help?

import xml.etree.ElementTree as ET
import datetime
from datetime import datetime

def XMLExistsCheck(XMLSec, XMLValue, IsAtt):
    '''
    Checks whether XML tag requested is in the XML script

    Args:
        res (str): String version of response XML.
        XMLSec: this is the XML Section the value to be found in
        XMLValue: this is the XML value which is being looked for
        IsAtt: if its an attribute being looked for then its 1 else 0

    Returns:
        if it finds the value then the string 'found' is returned
    '''
    import xml.etree.ElementTree as ET
    import re
    # Compile once on import. Regular expression to strip XML namespace.
    # We don't need it and it amkes aprsing wqith elementree a pain.
    strip_namespace_regex = re.compile(' xmlns="[^"]+"')
    if IsAtt > 0:
        if XMLValue in XMLSec.attrib:
            return 'found'
    else:
        if XMLValue == '':
            if XMLSec.text is not None:
                return 'found'
        if XMLSec.find(XMLValue) is not None:
            return 'found'

tree = ET.parse('c:\TEST.xml')
root = tree.getroot()
Addressee = root.findall('.//Addressee')
for Addressee in Addressee:
    ComName = Addressee.attrib['CompanyName'] if XMLExistsCheck(Addressee,'CompanyName',1) == 'found' else ''
Address = Addressee.findall('.//Address')
for Address in Address:
    ComPostcode = Address.attrib['PostCode'] if XMLExistsCheck(Address,'PostCode',1) == 'found' else ''
Content = root.findall('.//Content')
for Content in Content:
    CommonExtract = Content.findall('.//CommonExtract')
    for CommonExtract in CommonExtract:
        SystemDate = CommonExtract.attrib['SystemDate'] if XMLExistsCheck(CommonExtract,'SystemDate',1) == 'found' else ''
        SystemDateconvert = datetime.strptime(SystemDate, '%Y-%m-%d')
        SystemDateYear = SystemDateconvert.year
        message = CommonExtract.findall('.//{http://www.origostandards.com/schema/soap/v1}message')
        for message in message:
            m_content = message.findall('.//{http://www.origostandards.com/schema/ReceiveRemunerationStatement/v1/LoadStatementRequest}m_content')
            for m_content in m_content:
                payee_firm = m_content.findall('.//{http://www.origostandards.com/schema/ReceiveRemunerationStatement/v1/LoadStatementRequest}payee_firm')
                for payee_firm in payee_firm:
                    reference = payee_firm.findall('.//reference')
                    for reference in reference:
                        reference_number = reference.find('reference_number').text
print(ComName)
print(ComPostcode)
print(SystemDate)
print(SystemDateYear)