Hello Friend,
Using SQL CTE(Common table Expression) you can do this thing. below i provide some sample code as per your requirement.
;WITH CTE AS
(
SELECT
t.n.value('@DeptID', 'varchar(10)') as DeptID,
t.n.value('@SchedulePriority', 'varchar(10)') as WeekDayID
FROM @XMLInsertion.nodes('/root/*') as t(n)
)
UPDATE T
SET SCHEDULEPRIORITY = C.WeekDayID
FROM TEMP AS T
INNER JOIN CTE AS C
ON T.DEPTID = C.DEPTID
SELECT * FROM TEMP