Querying XML data

[sds814]

I'm using SQL Server 2008 R2.

I have a table with a xml data type column. I'm trying to query one of the elements from the column's value, but getting the following error:

Msg 2389, Level 16, State 1, Line 12
XQuery [tblEmailFollowup.OptionalParameters.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'

CREATE TABLE tblEmailFollowup
(
	EmailFollowupID int IDENTITY(1,1)
	,OptionalParameters xml
)

DECLARE @Parameter XML
SET @Parameter = '<Parameter><UserType>DonorManager</UserType></Parameter>'

INSERT INTO tblEmailFollowup(OptionalParameters)Values(@Parameter)

SELECT ref.value('UserType', 'nvarchar(50)') AS [UserType]
FROM tblEmailFollowup
CROSS APPLY OptionalParameters.nodes('//Parameter') R(ref)

What am I doing wrong?

Thanks for the help.

 

[bborissov]

DECLARE @tblEmailFollowup TABLE 
(
	EmailFollowupID int IDENTITY(1,1)
	,OptionalParameters xml
)

DECLARE @Parameter XML
SET @Parameter = '<Parameter><UserType>DonorManager</UserType></Parameter>'

INSERT INTO @tblEmailFollowup(OptionalParameters) Values (@Parameter)

SET @Parameter = '<Parameter><UserType>DonorManager1</UserType></Parameter>'
INSERT INTO @tblEmailFollowup(OptionalParameters) Values (@Parameter)

SELECT r.ref.value('(/Parameter/UserType)[1]','varchar(50)')
FROM @tblEmailFollowup
CROSS APPLY  OptionalParameters.nodes('/Parameter') R(ref)

Borislav Borissov
VFP9 SP2, SQL Server

 

[sds814]

Thanks Boris.

What does the [1] do?

Also, do you recommend to use nvarchar only when it's required because of the extra memory it takes?

 

[bborissov]

http://social.msdn.microsoft.com/Fo...-is-a-singleton-and-when-its-not?forum=sqlxml

BTW your original query also works if you place [1]

CREATE TABLE tblEmailFollowup
(
	EmailFollowupID int IDENTITY(1,1)
	,OptionalParameters xml
)

DECLARE @Parameter XML
SET @Parameter = '<Parameter><UserType>DonorManager</UserType></Parameter>'

INSERT INTO tblEmailFollowup(OptionalParameters)Values(@Parameter)

SELECT ref.value('UserType[1]', 'nvarchar(50)') AS [UserType]
FROM tblEmailFollowup
CROSS APPLY OptionalParameters.nodes('//Parameter') R(ref)

Borislav Borissov
VFP9 SP2, SQL Server

 

[sds814]

In your example

DECLARE @tblEmailFollowup TABLE 
(
	EmailFollowupID int IDENTITY(1,1)
	,OptionalParameters xml
)

DECLARE @Parameter XML
SET @Parameter = '<Parameter><UserType>DonorManager</UserType></Parameter>'

INSERT INTO @tblEmailFollowup(OptionalParameters) Values (@Parameter)

SET @Parameter = '<Parameter><UserType>DonorManager1</UserType></Parameter>'
INSERT INTO @tblEmailFollowup(OptionalParameters) Values (@Parameter)

SELECT r.ref.value('(/Parameter/UserType)[1]','varchar(50)')
FROM @tblEmailFollowup
CROSS APPLY  OptionalParameters.nodes('/Parameter') R(ref)

What would be the easiest way to get the EmailFollowupID where OptionalParameters = DonorManager1

 

[sds814]

I used this:

SELECT  EmailFollowupID
FROM tblEmailFollowup
CROSS APPLY OptionalParameters.nodes('//Parameter') R(ref) 
WHERE ref.value('UserType[1]', 'nvarchar(50)') = 'DonorManager'