quote:

---

Originally posted by Pete_N

I have been trying to read an xml file stored in a text field. With help i have got so far, but I am only getting 1 record back when I know the XML has 123 records

WITH XMLNAMESPACES (DEFAULT 'http://bacs.co.uk/submissions')
Select
t.u.value('(./PaymentFile/@status)[1]','varchar(20)') AS Result,
t.u.value('(./PaymentFile/@index)[1]','int') AS FileIndex,
t.u.value('(./PaymentFile/@paymentFileIdentifier)[1]','varchar(6)') AS FileSerialNumber,
t.u.value('(./PaymentFile/@processingDay)[1]','varchar(10)') AS ProcessingDate ,
t.u.value('(./PaymentFile/@creditRecordCount)[1]','int') AS CreditCount,
t.u.value('(./PaymentFile/@creditValueTotal)[1]','varchar(11)') AS CreditTotal,
t.u.value('(./PaymentFile/@debitRecordCount)[1]','int') AS DebitCount,
t.u.value('(./PaymentFile/@debitValueTotal)[1]','varchar(11)') AS DebitTotal,
t.u.value('(./PaymentFile/@ddiRecordCount)[1]','int') AS AUDDISCount,
t.u.value('(./PaymentFile/OriginatingServiceUser/@userNumber)[1]','varchar(6)') AS SUN,
t.u.value('(./PaymentFile/OriginatingServiceUser/@name)[1]','varchar(18)') AS OAccountName,
t.u.value('(./SigningContact/@fullName)[1]','varchar(30)') AS FileSignedBy,
t.u.value('(./SubmittingContact/@fullName)[1]','varchar(30)') AS FileSubmittedBy,
UPPER(t.u.value('(@submissionType)[1]','varchar(30)')) AS SubmissionType,
t.u.value('(@submissionSerialNumber)[1]','varchar(30)') AS SubmissionSerialNumber,
t.u.value('(@submissionIdentifier)[1]','varchar(100)') AS BacsSubmissionIdentifier
from (SELECT CAST(CAST(SubmissionResults AS NTEXT) AS xml) AS Xmlreport
FROM [TESTDB].[dbo].[TESTTABLE] WHERE SerialNumber = 9) d
cross apply Xmlreport.nodes('/SubmissionResults')t(u)

---

WITH XMLNAMESPACES (DEFAULT 'http://bacs.co.uk/submissions')
SELECT 
	t.u.value('(./@status)[1]','varchar(20)') AS Result,
	t.u.value('(./@index)[1]','int') AS FileIndex,
	t.u.value('(./@paymentFileIdentifier)[1]','varchar(6)') AS FileSerialNumber,
	t.u.value('(./@processingDay)[1]','varchar(10)') AS ProcessingDate ,
	t.u.value('(./@creditRecordCount)[1]','int') AS CreditCount, 
	t.u.value('(./@creditValueTotal)[1]','varchar(11)') AS CreditTotal,
	t.u.value('(./@debitRecordCount)[1]','int') AS DebitCount,
	t.u.value('(./@debitValueTotal)[1]','varchar(11)') AS DebitTotal,
	t.u.value('(./@ddiRecordCount)[1]','int') AS AUDDISCount,
	t.u.value('(./OriginatingServiceUser/@userNumber)[1]','varchar(6)') AS SUN,
	t.u.value('(./OriginatingServiceUser/@name)[1]','varchar(18)') AS OAccountName,
	t.u.value('(../SigningContact/@fullName)[1]','varchar(30)') AS FileSignedBy,
	t.u.value('(../SubmittingContact/@fullName)[1]','varchar(30)') AS FileSubmittedBy,
	UPPER(t.u.value('(../@submissionType)[1]','varchar(30)')) AS SubmissionType,
	t.u.value('(../@submissionSerialNumber)[1]','varchar(30)') AS SubmissionSerialNumber,
	t.u.value('(../@submissionIdentifier)[1]','varchar(100)') AS BacsSubmissionIdentifier
FROM 
	(
		SELECT CAST(CAST(SubmissionResults AS NTEXT) AS XML) AS Xmlreport 
		FROM [TESTDB].[dbo].[TESTTABLE] WHERE SerialNumber = 9
	) d
CROSS APPLY Xmlreport.nodes('/SubmissionResults/PaymentFile')t(u)