>>>Hi,
>>>
>>>The only way I found that I can assign a value selected in SQL Select to a variable as following:
>>>
>>>

>>>select @MyVar = (select MyColumn1 from MyTable where MyColumn2 = 'SomeValue')
>>>

>>>
>>>Is the above the only way to do it? Or there is a better approach? TIA.
>>
>>Another way is
>>

>>select @MyVar = MyColumn1 from myTable

>>
>>The difference between these two methods is that the first will always change the @MyVar variable. In the second case, if the select statement does not produce rows, the original value of @MyVar will not change.
>
>
>Can you think of why the following SLQ Select (selecting from an XML string):
>
>

>SELECT @Var1 = [Fld FldName].value('.','varchar(20)'),  [Fld FldName].value('@Name','varchar(20)') AS FldName
>  FROM @myxml.nodes('/TABLENAME/Fld') Fld([Fld FldName]) where [Fld FldName].value('@Name','varchar(20)')='Field1'
>

>
>gives error:
>

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

You can not mix assigning the variables and selecting a column in the same statement. It should be

SELECT @Var1 = [Fld FldName].value('.','varchar(20)'),  @Var2 = [Fld FldName].value('@Name','varchar(20)')
  FROM @myxml.nodes('/TABLENAME/Fld') Fld([Fld FldName]) where [Fld FldName].value('@Name','varchar(20)')='Field1'