SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Reading XML from text field only returns 1 row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Pete_N
Posting Yak Master

165 Posts

Posted - 08/19/2013 :  12:26:30  Show Profile  Reply with Quote
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)

Edited by - Pete_N on 08/19/2013 12:29:34

James K
Flowing Fount of Yak Knowledge

3557 Posts

Posted - 08/19/2013 :  12:33:52  Show Profile  Reply with Quote
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)

This is probably because you have only one SubmissionResults node and the 123 nodes are PaymentFile nodes under that single SubmissionResults node.

If that be the case, what you should do is navigate down one more level to the PaymentsFile level in your cross apply (see in red). Then in each of the selects, remove the PaymentFile level, or navigate up one level (also examples in red). Something like this:

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)

Edited by - James K on 08/19/2013 12:35:00
Go to Top of Page

Pete_N
Posting Yak Master

165 Posts

Posted - 08/19/2013 :  12:49:29  Show Profile  Reply with Quote
HI,

Thank you, that's solved that problem and has given a great reference to work with for the other aspects of my Project
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30108 Posts

Posted - 08/19/2013 :  14:13:41  Show Profile  Visit SwePeso's Homepage  Reply with Quote
What datatype is SubmissionResults column?


Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000