Read XML

Transact-SQL
1

Going round in circles. need to get SubmittingServiceUser userNumber , SubmittingContact contactidentifier and OriginatingServiceUser User Number

I have had to replace the front tag with * to be able to post

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>

DECLARE @WORKINGXML TABLE
(
XMLTEXT XML
)
SELECT
a.c.value('(SubmittingServiceUser)[1]/@userNumber','varchar(30)') As 'UserNumber'
FROM ( SELECT XMLTEXT AS Xmlreport FROM @WORKINGXML) d
OUTER APPLY Xmlreport.nodes('/SubmissionResults') AS a(c)
indent preformatted text by 4 spaces

2 
;WITH XMLNAMESPACES ('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)

output:

UserNumber contactIdentifier userNumber_Orig
123456 MR SMITH 010101

dbfiddle

1 Like