How to use Update statement with XML input in SQL Server?

Question

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.

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

radar · Accepted Answer · 2014-09-23 18:32:29Z

2

we can use MERGE, select XML values into a temporary table and use MERGE

BEGIN

    SET NOCOUNT ON; 

    BEGIN TRANSACTION;

        BEGIN    

            IF object_id('tempdb.dbo.#tmpList') IS NOT null DROP TABLE #tmpList
            create table #tmpList(
                id                      varchar(255),
                departmentID            int,
                role                    int
            )

            insert into #tmpList
            SELECT          ParamValues.ntid.value('.', 'varchar(255)'),
                            @departmentID,
                            @role
            FROM            @xmlUsers.nodes('/users/ntid') AS ParamValues(ntid)

            MERGE  RC_PermissionsUsers as PU
            USING ( select id, departmentID, role from #tmpList) T
                    ON PU.ntid = T.id
            WHEN MATCHED THEN 
                UPDATE SET departmentId = T.departmentID,
                           role = T.role
            WHEN NOT MATCHED THEN
                INSERT ( ntid, departmentID, role)  
                   VALUES ( T.id, T.departmentID, T.role)           


        END

    COMMIT TRANSACTION;  
END

answered Sep 23, 2014 at 18:32

radar

13.4k2 gold badges27 silver badges33 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2571510 Over a year ago

Thanks a lot - this is great ! Just one question: could I use a temp table here as well instead of creating a real one and drop this later ?

Collectives™ on Stack Overflow

How to use Update statement with XML input in SQL Server?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related