What is your SQL Server version? If it's 2008 and up, then instead of passing XML you can pass a table with new values.


>What do you see as a downside of this approach?
>
>>Every time I see something like that I have a feeling that it's not a right way accomplish the goal.
>>
>>>I am trying to build a dynamic UPDATE statement in a SQL Server Stored Procedure.
>>>
>>>I pass to the SQL Stored procedure an XML string (@XmlDocWo) that has field name/value pairs. Then I extract a value for a field from the XML as following:
>>>
>>>

>>>SELECT @Fld1Value = [Fld FldName].value('.','char(10)') FROM @XmlDocWo.nodes('/TABLE_NAME/Fld') Fld([Fld FldName]) 
>>>	 WHERE [Fld FldName].value('@name','varchar(20)') = 'FLD1NAME'
>>>
>>>SELECT @ Fld2Value = [Fld FldName].value('.','char(10)') FROM @XmlDocWo.nodes('/TABLE_NAME/Fld') Fld([Fld FldName]) 
>>>	 WHERE [Fld FldName].value('@name','varchar(20)') = 'FLD2NAME'
>>>

>>>
>>>Then the Update command would be as follows:
>>>
>>>

>>>UPDATE MyTable SET FLD1NAME = @Fld1Value, FLD2NAME = @Fld2Value where PK_FLD = @PkFldValue
>>>

>>>
>>>The above expression would have more than 2 fields, of course. And I am looking for the way to eliminate one or some fields if they are not included in XML. For example, if XML string does not have value for FLD2NAME the UPDATE expression would be:
>>>
>>>

>>>UPDATE MyTable SET FLD1NAME = @Fld1Value where PK_FLD = @PkFldValue
>>>

>>>
>>>How would you suggest I go about it?