CODEWITHSUNDEEP
sql-server-2005
user1745679
user1745679

Reputation: 249

initializing xml variable

DECLARE @XML1 XML

SET @XML1=SELECT XMLCOLUMN FROM TABLE WHERE ID='123-456'

when i do like this i am getting following error message:

Incorrect syntax near the keyword 'SELECT'.

Could anyone please help me how to assign xmlcolumn value to xml variable.

Upvotes: 1

Views: 324

Answers (1)

Martin Smith
Martin Smith

Reputation: 453426

Use

SELECT @XML1= XMLCOLUMN FROM TABLE WHERE ID='123-456'

Or

SET @XML1= (SELECT XMLCOLUMN FROM TABLE WHERE ID='123-456')

They behave the same if the query returns exactly one row but differ otherwise.

+----+-------------------------------------------------------+------------------------------------------+
|    |                        SELECT                         |                   SET                    |
+----+-------------------------------------------------------+------------------------------------------+
| 0  | No Assignment made. Variable has original value.      | Variable set to null                     |
| 1  | Variable assigned the one matching value              | Variable assigned the one matching value |
| >1 | Variable assigned arbitrary value from matching rows. | Error raised                             |
+----+-------------------------------------------------------+------------------------------------------+

Upvotes: 2

Related Questions