Assigned XQuery results to variables

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)

Assigned XQuery results to variables

Author

Topic

cpender
Starting Member

11 Posts

Posted - 2010-07-08 : 07:38:47

I have the following XQuery code:


;WITH XMLNAMESPACES('SIMATIC_BATCH_V7_0_1' AS y)
SELECT	a.value('@timestamp', 'datetimeoffset(0)') AS Date,
		a.value('@state', 'nvarchar(20)') AS State,
		a.value('@exstate', 'int') AS ExState
		
FROM		#ModifTable AS T

CROSS APPLY	XmlData.nodes('/y:Archivebatch/y:Cr/y:Crdatacltn/y:Crdata') AS x(a)
WHERE		T.ID = 132

which produces:

Date                      State    ExState
2010-04-22 09:37:09 +00:00	2	0
2010-04-22 09:37:17 +00:00	4	0
2010-04-22 09:37:17 +00:00	5	0
2010-04-22 09:37:33 +00:00	7	0
2010-04-22 09:37:34 +00:00	8	0
2010-04-22 09:40:10 +00:00	8	128
2010-04-22 09:40:23 +00:00	8	0
2010-04-22 09:40:33 +00:00	8	128
2010-04-22 09:40:43 +00:00	8	0
2010-04-22 09:42:18 +00:00	8	128

Now, what I would like to do have another column called "Description" which, depending on the values of State and ExState displays a description of the state. For example:

State=8 AND ExState=0 displays "Running"
State=8 AND ExState=128 displays "Running + Subelement Active"

My initial idea was to assign the result of:

a.value('@state', 'nvarchar(20)')

to a local variable "@XMLState" like:

SELECT	a.value('@timestamp', 'datetimeoffset(0)') AS Date,
		@XMLSate = a.value('@state', 'nvarchar(20)'),
		a.value('@exstate', 'int') AS ExState

This does not work. I get the error:

A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.

Firstly, is there a way of assigning the value of an XQuery result to a local variable? Is my syntax incorrect?

Or is there a way of displaying different values to a local variable @Description depending on the results from the XQueries above?

Sachin.Nand

2937 Posts

Posted - 2010-07-08 : 07:56:43

It has nothing to do with Xquery.Even in a simple select statement you are not allowed to this.

Example


declare @id int
select @id=id,name from table

will generate the same error.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-08 : 08:18:36

[code];WITH XMLNAMESPACES('SIMATIC_BATCH_V7_0_1' AS y)
, cteSource
AS (
SELECT n.value('@timestamp', 'datetimeoffset(0)') AS [Date],
n.value('@state', 'nvarchar(20)') AS [State],
n.value('@exstate', 'int') AS [ExState]
FROM @Sample AS s
CROSS APPLY Data.nodes('/y:Archivebatch/y:Cr/y:Modifcltn/y:Modif') AS x(n)
WHERE s.ID = 132
)
SELECT [Date],
[State],
[ExState],
CASE
WHEN State = 8 AND ExState = 0 THEN 'Running'
WHEN State = 8 AND ExState = 128 THEN 'Running + Subelement Active'
ELSE NULL
END AS theStatus
FROM cte[/code]

N 56°04'39.26"
E 12°55'05.63"

cpender
Starting Member

11 Posts

Posted - 2010-07-08 : 08:49:01

Peso, I am forever indebted to you