SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

DECLARE @XMLText XML ='
    <SubmissionResults  xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" 
                        xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
                        xmlns="http://bacs.co.uk/submissions"
                        status="complete" submissionIdentifier="1530285184182GY5wb2MZ2" submissionType="live" submissionSerialNumber="005442" submissionDateAndTime="Fri Jun 29 16:13:06 BST 2018" submissionEarliestDate="2018-07-02">
<SubmittingServiceUser userNumber="123456" name="MY NAME" />
<SubmittingContact contactIdentifier="MR SMITH" fullName="MR J SMITH" />
<SigningContact contactIdentifier="J JONES" fullName="JAMES JONES" />

<PaymentFile status="complete" index="1" paymentFileIdentifier="306" processingDay="2018-07-02" 
               currency="GBP" creditRecordCount="0" creditValueTotal="0" debitRecordCount="251" debitValueTotal="19333000" ddiRecordCount="0" workCode="4 MULTI ">
<OriginatingServiceUser userNumber="010101" name="COMPANY" />
</PaymentFile>
</SubmissionResults>'

;WITH XMLNAMESPACES ('http://www.w3.org/2001/XMLSchema-instance' as i
                    ,'http://www.w3.org/2001/XMLSchema' as d
                    ,'http://bacs.co.uk/submissions' as s)

SELECT
     a.c.value('(s:SubmittingServiceUser/@userNumber)[1]','varchar(30)') As UserNumber
     ,a.c.value('(s:SubmittingContact/@contactIdentifier)[1]','varchar(30)') As contactIdentifier
     ,a.c.value('(s:PaymentFile/s:OriginatingServiceUser/@userNumber)[1]','varchar(30)') As userNumber_Orig
FROM 
  @xmlText.nodes('/s:SubmissionResults') AS a(c)

UserNumber	contactIdentifier	userNumber_Orig
123456	MR SMITH	010101