reading xml values from - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

reading xml values from

Author

Topic

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-07 : 08:16:28

i have a 3rd party database that holds a xml file in a text fields

if i do Select Xmlreport from Mydatabase when serial = 1 , i can retrieve the file, however i want to use certain nodes to update another table. How can i get the value of 'UserNumber' and 'creditValueTotal'

<?xml version="1.0" encoding="utf-16"?><FileDetailType xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" status="complete" index="1" paymentFileIdentifier="301" processingDay="2013-07-12" currency="GBP" creditRecordCount="0" creditValueTotal="0" debitRecordCount="463" debitValueTotal="2985520" ddiRecordCount="0" workCode="4 MULTI "><OriginatingServiceUser userNumber="111111" name="SOME COMAPNY NAME" xmlns="http://bacs.co.uk/submissions" /></FileDetailType>

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-07 : 08:58:26

something like


Select  t.u.value('(./OriginatingServiceUser/@userNumber)[1]','int') AS UserNumber,
t.u.value('(./@creditValueTotal)[1]','int') AS creditValueTotal
from Mydatabase d
cross apply Xmlreport.nodes('/FileDetailType')t(u)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-07 : 09:11:39

HI,

I have just tried that but am coming up with an error

Msg 9506, Level 16, State 1, Line 3
The XMLDT method 'nodes' can only be invoked on columns of type xml

The XmlReport field is defined as a text field

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-07 : 09:22:13

quote:
Originally posted by Pete_N

HI,

I have just tried that but am coming up with an error

Msg 9506, Level 16, State 1, Line 3
The XMLDT method 'nodes' can only be invoked on columns of type xml

The XmlReport field is defined as a text field

then you need to CAST it to xml first


Select  t.u.value('(./OriginatingServiceUser/@userNumber)[1]','int') AS UserNumber,
t.u.value('(./@creditValueTotal)[1]','int') AS creditValueTotal
from (SELECT CAST(Xmlreport AS xml) AS Xmlreport FROM Mydatabase) d
cross apply Xmlreport.nodes('/FileDetailType')t(u)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-07 : 12:57:42

Hi,

I have been trying the code you suggested as follows

Select
t.u.value('(./OriginatingServiceUser/@userNumber)[1]','int') AS UserNumber,
t.u.value('(./@creditValueTotal)[1]','int') AS creditValueTotal
FROM (SELECT CAST(CAST(Xmlreport AS NTEXT) AS xml) AS Xmlreport FROM Mydatabase.dbo.reports where serialNumber = 1) d
cross apply Xmlreport.nodes('/FileDetailType')t(u)

but no data is being returned. If I just do a a straight forward select in SQL and copy the result to a text file i get the following xml. Im presuming that my code is not finding the correct nodes?

<?xml version="1.0" encoding="utf-16"?>
<SubmissionResults xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"
status="complete"ckow[D))56324"
submissionType="fullTest"
submissionSerialNumber="000001"
submissionDateAndTime="Thu Jul 11 11:28:48 BST 2013"
submissionEarliestDate="2013-07-12"
xmlns="http://bacs.co.uk/submissions">
<SubmittingServiceUser
userNumber="111111"
name="MYCOMPANYNAME" />
<SubmittingContact
contactIdentifier="MYNAME"
fullName="MYNAME" />
<SigningContact
contactIdentifier="NAME123456"
fullName="MY NAME" />
<PaymentFile
status="complete"
index="1"
paymentFileIdentifier="301"
processingDay="2013-07-12"
currency="GBP"
creditRecordCount="0"
creditValueTotal="0"
debitRecordCount="463"
debitValueTotal="2985520"
ddiRecordCount="0"
workCode="4 MULTI ">
<OriginatingServiceUser
userNumber="111111"
name="MYCOMPANYNAME" />
</PaymentFile>
</SubmissionResults>

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 13:09:23

Your XPATH doesn't seem to be right. Also, you need to add the namespace:

WITH XMLNAMESPACES (DEFAULT 'http://bacs.co.uk/submissions')
Select  t.u.value('(./OriginatingServiceUser/@userNumber)[1]','int') AS UserNumber,
t.u.value('(./@creditValueTotal)[1]','int') AS creditValueTotal
from (SELECT CAST(Xmlreport AS xml) AS Xmlreport FROM Mydatabase) d
cross apply Xmlreport.nodes('/SubmissionResults/PaymentFile')t(u)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-08-07 : 13:24:21

You are using a namespace, that's why you don't get any data back.
See this repro.

-- Prepare sample data
CREATE TABLE	#Sample
		(
			Serial TINYINT NOT NULL,
			Data TEXT COLLATE LATIN1_GENERAL_BIN NOT NULL
		);

INSERT	#Sample
	(
		Serial,
		Data
	)
VALUES	(1,	N'<?xml version="1.0" encoding="utf-16"?>
		<SubmissionResults xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" status="complete" ckow="56324" submissionType="fullTest" submissionSerialNumber="000001" submissionDateAndTime="Thu Jul 11 11:28:48 BST 2013" submissionEarliestDate="2013-07-12" xmlns="http://bacs.co.uk/submissions">
			<SubmittingServiceUser userNumber="111111" name="MYCOMPANYNAME" />
			<SubmittingContact contactIdentifier="MYNAME" fullName="MYNAME" />
			<SigningContact contactIdentifier="NAME123456" fullName="MY NAME" />
			<PaymentFile status="complete" index="1" paymentFileIdentifier="301" processingDay="2013-07-12" currency="GBP" creditRecordCount="0" creditValueTotal="-1.23" debitRecordCount="463" debitValueTotal="2985520" ddiRecordCount="0" workCode="4 MULTI ">
				<OriginatingServiceUser userNumber="111111" name="MYCOMPANYNAME" />
			</PaymentFile>
		</SubmissionResults>');

-- SwePeso
WITH XMLNAMESPACES(DEFAULT 'http://bacs.co.uk/submissions')
SELECT		sr.n.value('(@userNumber)', 'VARCHAR(100)') AS UserNumber,
		sr.n.value('(../@creditValueTotal)', 'MONEY') AS Credit
FROM		(
			SELECT	CONVERT(XML, CAST(Data AS NVARCHAR(MAX))) AS Data
			FROM	#Sample
			WHERE	Serial = 1
		) AS s
CROSS APPLY	s.Data.nodes('(/SubmissionResults/PaymentFile/OriginatingServiceUser)') AS sr(n);

-- Clean up
DROP TABLE	#Sample;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-07 : 13:55:35

Hi James,

That's brilliant thank you

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-08-07 : 16:27:38

quote:
Originally posted by Pete_N

Hi James,

That's brilliant thank you

You are very welcome. I am eager and glad to take all the credit with no regard to Visakh who put in all the hard work

Pete_N
Posting Yak Master

181 Posts

Posted - 2013-08-08 : 04:20:33

My apologies to Visakh and the lack of credit for his hard work.

In my defence, i was so excited to have a working sample, i just dived in and expanded the example to fit my exact requirements, so joint credit to both Visakh and James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-08-08 : 06:36:24

quote:
Originally posted by Pete_N

My apologies to Visakh and the lack of credit for his hard work. In my defence, i was so excited to have a working sample, i just dived in and expanded the example to fit my exact requirements, so joint credit to both Visakh and James

No problem

Important thing for me is for you to get working solution for your problem

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources