Reading XML Only getting one record

Pete_N · April 13, 2020, 11:59pm

Big thank you for the earlier help, but I have another problem. I have cut the xml down to two record for an example. When running my query, I am only getting the first record instead of both records. In normal circumstances there is an undefined number of records

insert into #temptest
select '<SubmissionResults >
  <PaymentFile status="complete" index="1" paymentFileIdentifier="044" processingDay="2020-04-15" currency="GBP" creditRecordCount="0" creditValueTotal="0" debitRecordCount="365" debitValueTotal="10" ddiRecordCount="0" workCode="4 MULTI  ">
    <OriginatingServiceUser userNumber="123456" name="COMPANY 1" />
  </PaymentFile>
  <PaymentFile status="complete" index="2" paymentFileIdentifier="352" processingDay="2020-04-14" currency="GBP" creditRecordCount="11" creditValueTotal="1" debitRecordCount="0" debitValueTotal="0" ddiRecordCount="0" workCode="4 MULTI  ">
    <OriginatingServiceUser userNumber="456456" name="COMPANY 2" />
  </PaymentFile>
 </SubmissionResults>
'

DECLARE @XMLText XML = (select tester from #temptest )


SELECT c.value('(PaymentFile)[1]/@status', 'VARCHAR(100)') AS 'status',
	   c.value('(PaymentFile)[1]/@index', 'VARCHAR(10)') AS 'index',
	   c.value('(PaymentFile)[1]/@paymentFileIdentifier', 'VARCHAR(10)') AS 'paymentFileIdentifier',
	   c.value('(PaymentFile)[1]/@processingDay', 'VARCHAR(10)') AS 'processingDay',
	   c.value('(PaymentFile)[1]/@currency', 'VARCHAR(100)') AS 'currency',
	   c.value('(PaymentFile)[1]/@creditRecordCount', 'VARCHAR(10)') AS 'creditRecordCount',
	   c.value('(PaymentFile)[1]/@creditValueTotal', 'VARCHAR(10)') AS 'creditValueTotal',
	   c.value('(PaymentFile)[1]/@debitRecordCount', 'VARCHAR(100)') AS 'debitRecordCount',
	   c.value('(PaymentFile)[1]/@debitValueTotal', 'VARCHAR(100)') AS 'debitValueTotal',
	   c.value('(PaymentFile)[1]/@ddiRecordCount', 'VARCHAR(100)') AS 'ddiRecordCount',
	   c.value('(PaymentFile)[1]/@workCode', 'VARCHAR(100)') AS 'workCode',
	   c.value('(PaymentFile/OriginatingServiceUser)[1]/@userNumber', 'VARCHAR(100)') AS 'userNumber',
	   c.value('(PaymentFile/OriginatingServiceUser)[1]/@name', 'VARCHAR(100)') AS 'name'
FROM   (SELECT @XMLText  AS 'Xmlreport') d 
        CROSS APPLY Xmlreport.nodes('/SubmissionResults') AS T(c) 

		drop table #temptest
		
		Result 
		
		status	index	paymentFileIdentifier	processingDay	currency	creditRecordCount	creditValueTotal	debitRecordCount	debitValueTotal	ddiRecordCount	workCode	userNumber	name
complete	1	044	2020-04-15	GBP	0	0	365	10	0	4 MULTI  	123456	COMPANY 1

yosiasz · April 14, 2020, 12:08am

because you told it only first record
(PaymentFile)[1]

Pete_N · April 14, 2020, 12:19am

to be fair that does makes sense but if I don't know how many records are in the xml file, how would I go about retrieving them all ….. my logic has gone out of the window

yosiasz · April 14, 2020, 12:25am

 select  dmc.value('@status','varchar(50)') AS status
 FROM (select @xmldoc as xmldoc) p
CROSS APPLY xmldoc.nodes('/SubmissionResults/PaymentFile') AS run(dmc)

yosiasz · April 14, 2020, 12:34am

another way of doing it

select k.value('@status','varchar(50)') AS ID,
    b.value('local-name(.)','VARCHAR(50)') AS ColumnName,  -- get attribute name
    b.value('.','VARCHAR(MAX)') AS Value 
FROM @xmldoc.nodes('/SubmissionResults/PaymentFile') p(k)
CROSS APPLY k.nodes('@*') a(b)

Pete_N · April 14, 2020, 12:43am

HI,

That's a lot better though Im still not getting the @userNumber or @username values from OriginatingServiceUser

yosiasz · April 14, 2020, 12:47am

add them

Pete_N · April 14, 2020, 12:53am

I come up with

SELECT t.c.value('@status', 'VARCHAR(100)') AS 'status',
	   t.c.value('@index', 'VARCHAR(10)') AS 'index',
	   t.c.value('@paymentFileIdentifier', 'VARCHAR(10)') AS 'paymentFileIdentifier',
	   t.c.value('@processingDay', 'VARCHAR(10)') AS 'processingDay',
	   t.c.value('@currency', 'VARCHAR(100)') AS 'currency',
	   t.c.value('@creditRecordCount', 'VARCHAR(10)') AS 'creditRecordCount',
	   t.c.value('@creditValueTotal', 'VARCHAR(10)') AS 'creditValueTotal',
	   t.c.value('@debitRecordCount', 'VARCHAR(100)') AS 'debitRecordCount',
	   t.c.value('@debitValueTotal', 'VARCHAR(100)') AS 'debitValueTotal',
	   t.c.value('@ddiRecordCount', 'VARCHAR(100)') AS 'ddiRecordCount',
	   t.c.value('@workCode', 'VARCHAR(100)') AS 'workCode',
	   S.c.value('@userNumber', 'VARCHAR(100)') AS 'userNumber',
	   S.c.value('@name', 'VARCHAR(100)') AS 'name'
FROM   (SELECT @XMLText  AS 'Xmlreport') d 
        CROSS APPLY Xmlreport.nodes('/SubmissionResults/PaymentFile') AS T(c) 
 CROSS APPLY Xmlreport.nodes('/SubmissionResults/PaymentFile/OriginatingServiceUser') AS S(c)

But although I get all the data I get 4 records, Company 1, Company 2, Company 1 Company 2

Just cannot get my head around it tonight no matter what I try

yosiasz · April 14, 2020, 12:55am

too many cross apply you get multiple rows,

Pete_N · April 14, 2020, 1:07am

Hi Yosiasz

I'm struggling here, think I will call it a night ( 2am ) , and revisit this with a fresh pair of eyes in the morning, hopefully my logic will be back on track. Thank you for all your help tonight ,

Pete_N · April 14, 2020, 7:09am

Hi Yosiasz,

All sorted, once again thank you for your help. Small amendment on my code

SELECT t.c.value('@status', 'VARCHAR(100)') AS 'status',
	   t.c.value('@index', 'VARCHAR(10)') AS 'index',
	   t.c.value('@paymentFileIdentifier', 'VARCHAR(10)') AS 'paymentFileIdentifier',
	   t.c.value('@processingDay', 'VARCHAR(10)') AS 'processingDay',
	   t.c.value('@currency', 'VARCHAR(100)') AS 'currency',
	   t.c.value('@creditRecordCount', 'VARCHAR(10)') AS 'creditRecordCount',
	   t.c.value('@creditValueTotal', 'VARCHAR(10)') AS 'creditValueTotal',
	   t.c.value('@debitRecordCount', 'VARCHAR(100)') AS 'debitRecordCount',
	   t.c.value('@debitValueTotal', 'VARCHAR(100)') AS 'debitValueTotal',
	   t.c.value('@ddiRecordCount', 'VARCHAR(100)') AS 'ddiRecordCount',
	   t.c.value('@workCode', 'VARCHAR(100)') AS 'workCode',
	   S.c.value('@userNumber', 'VARCHAR(100)') AS 'userNumber',
	   S.c.value('@name', 'VARCHAR(100)') AS 'name'
FROM   (SELECT @XMLText  AS 'Xmlreport') d 
        CROSS APPLY Xmlreport.nodes('//SubmissionResults/PaymentFile') AS t(c) 
        CROSS APPLY t.c.nodes('OriginatingServiceUser') AS s(c)

yosiasz · April 14, 2020, 1:48pm

But doesnt that give you a cartesian result

mike01 · April 14, 2020, 2:25pm

Try this without Cross Apply

DECLARE @XMLText XML = '<SubmissionResults>
  <PaymentFile status="complete" index="1" paymentFileIdentifier="044" processingDay="2020-04-15" currency="GBP" creditRecordCount="0" creditValueTotal="0" debitRecordCount="365" debitValueTotal="10" ddiRecordCount="0" workCode="4 MULTI  ">
    <OriginatingServiceUser userNumber="123456" name="COMPANY 1" />
  </PaymentFile>
  <PaymentFile status="complete" index="2" paymentFileIdentifier="352" processingDay="2020-04-14" currency="GBP" creditRecordCount="11" creditValueTotal="1" debitRecordCount="0" debitValueTotal="0" ddiRecordCount="0" workCode="4 MULTI  ">
    <OriginatingServiceUser userNumber="456456" name="COMPANY 2" />
  </PaymentFile>
 </SubmissionResults>
'

SELECT  t.c.value('../@status', 'VARCHAR(100)') AS 'status'
	   ,t.c.value('../@index', 'VARCHAR(10)') AS 'index'
	   ,t.c.value('../@paymentFileIdentifier', 'VARCHAR(10)') AS 'paymentFileIdentifier'
	   ,t.c.value('../@processingDay', 'VARCHAR(10)') AS 'processingDay'
	   ,t.c.value('../@currency', 'VARCHAR(100)') AS 'currency'
	   ,t.c.value('../@creditRecordCount', 'VARCHAR(10)') AS 'creditRecordCount'
	   ,t.c.value('../@creditValueTotal', 'VARCHAR(10)') AS 'creditValueTotal'
	   ,t.c.value('../@debitRecordCount', 'VARCHAR(100)') AS 'debitRecordCount'
	   ,t.c.value('../@debitValueTotal', 'VARCHAR(100)') AS 'debitValueTotal'
	   ,t.c.value('../@ddiRecordCount', 'VARCHAR(100)') AS 'ddiRecordCount'
	   ,t.c.value('../@workCode', 'VARCHAR(100)') AS 'workCode'
	   ,t.c.value('@userNumber', 'VARCHAR(100)') AS 'userNumber'
	   ,t.c.value('@name', 'VARCHAR(100)') AS 'name'
from @XMLText.nodes('//SubmissionResults/PaymentFile/OriginatingServiceUser') as t(c)

yosiasz · April 14, 2020, 2:46pm

nice! less chatty and less cross applies