You have a parenthesis to many in the line above the IF statement.

You can change that to ...

set @currentdatetime = GETDATE()

Then you need two while loops. One that adds the missing elements and one that updates the ones that need updating.

while exists(select *

...

Do you mean that the value actually is null or that the node Removable is missing?

It is only possible to target one value in the XML with the update. You have to put your update statement in a loop that execute the update statement as...

Items is a context node created from nodes. The update statement has to work with the XML column directly. And because of that you have to move your where clause...

Sure. The "problem" is only that I don't deal with multiple tables in the code I provided to fetch the data...

Ok, then you should be able to use the code above.

Cheers!!

/Micke

Hundreds of tables in one query? I hope not 🙂

The code I provided will be the same regardless of what table you are using. It does however not support multiple...

I don't know of an automated way to do that.

You could however build your XML dynamically using the metadata in the schema.

Here is a little something that could get you...

The XML Version

declare @Param as xml

set @Param = '<I>1</I><I>2</I><I>3</I>'

select IntList.IntField.value('.', 'int')

from @Param.nodes('I') IntList(IntField)

Do you have any idea why there is a requirement for timezone on date and time fields?

According to W3C timezone is optional on date, time and datetime fields.

http://www.w3.org/TR/xmlschema-2/#dateTime

/Micke