question

Here is my sample script. What I want to do is to update tags intstatusID And strStatus of xml variable @xmlPlan that satisfy join condition Declare @xmlPlan xml = N' 1 2 Published 1 Loyalty Programs Plan 1 1 Supplier Group 1 8 New Enabled Enabled Enabled 5 HJK * 0XX135 13 New 20160101 20160101 20160101 20161231 20161231 20161231 KGH LABORATORIES LTD 0XX136 13 New 20160101 20160101 20160101 20161231 20161231 20161231 2 1 Supplier Group 2 8 New Enabled Enabled Disabled 5 ABC FOODSERVICE * 0XX134 13 New 20160101 20160101 20160101 20161231 20161231 20161231 3 1 Supplier Group 3 8 New Enabled Enabled Disabled 5 XZ FOODSERVICE * 0XX133 13 New 20160101 20160101 20160101 20161231 20161231 20161231 ' Create Table #SupplierGroups (strName varchar(500), intStatus int, strStatus varchar(200) ) Insert Into #SupplierGroups (strName, intStatus, strStatus) Values ('Supplier Group 1', 10, 'Active'), ('Supplier Group 3', 10, 'Active') SELECT a.*, t.c.value('(strName/text())[1]', 'varchar(500)') FROM @xmlPlan.nodes('planBean/supplierGroupsList/supplierGroup') t(c) Inner Join #SupplierGroups a ON a.strName = t.c.value('(strName/text())[1]', 'varchar(500)') -- I want XML tags intStatusID AND strStatus -- To be updated as [[ 10 , Active ]] of "Supplier Group 1" And "Supplier Group 3" only as per above join query -- I want @xmlPlan variable like below /* 1 2 Published 1 Loyalty Programs Plan 1 1 Supplier Group 1 10 Active Enabled Enabled Enabled 5 HJK * 0XX135 13 New 20160101 20160101 20160101 20161231 20161231 20161231 KGH LABORATORIES LTD 0XX136 13 New 20160101 20160101 20160101 20161231 20161231 20161231 2 1 Supplier Group 2 8 New Enabled Enabled Disabled 5 ABC FOODSERVICE * 0XX134 13 New 20160101 20160101 20160101 20161231 20161231 20161231 3 1 Supplier Group 3 10 Active Enabled Enabled Disabled 5 XZ FOODSERVICE * 0XX133 13 New 20160101 20160101 20160101 20161231 20161231 20161231 */