SQL Server: How to use Update statement with xml input



I use SQL Server 2008 and have a stored procedure with one input parameter formatted as xml. This xml is a list of names, one word each without spaces.


For each of these names I want to check if they already exist in a table, if no then they should get added to the table, if yes then they should get updated there.


So far I have the part to add them if they don't exist yet which works as intended but I can't figure out how to realise the updating part.

Can someone here help me with this ?


Just for demonstration this would be the update part as a stand-alone (if I would have one input instead of the xml):



UPDATE RC_PermissionsUsers
SET ntid = @ntid,
departmentID = @departmentID,
role = @role
WHERE ntid = @ntid


The rest of my procedure for the insert part (working):



BEGIN

SET NOCOUNT ON;

BEGIN TRANSACTION;

BEGIN
INSERT INTO RC_PermissionsUsers
(
ntid,
departmentID,
[role]
)
SELECT ParamValues.ntid.value('.', 'varchar(255)'),
@departmentID,
@role
FROM @xmlUsers.nodes('/users/ntid') AS ParamValues(ntid)
WHERE NOT EXISTS
(
SELECT ntid
FROM RC_PermissionsUsers
WHERE ntid = ParamValues.ntid.value('.', 'varchar(255)')
)

END

COMMIT TRANSACTION;
END


Many thanks in advance, Tim.


Tiada ulasan:

Catat Ulasan