XML query / change issue in SQL Server 2008 r2

Question

I am new to SQL, and I am trying to filter and change values ​​inside a column that contains an XML document for a purchased order. Here is an example of what an XML document looks like and what I'm looking for.

<TenderLines> <TenderLineItem> <tenderTypeId>S0001-00000001</tenderTypeId> .. .. </TenderLineItem> <TenderLines> 

I have 6000+ lines, and not all of them have the same TypeId tender. I want to filter the values ​​in tenderTypeId that have "S0001-00000001" and change them to "2"

So far this is what I came up with.

 USE LSPOS80 DECLARE @replacement as varchar(50) DECLARE @redundant as varchar(50) SET @replacement = '2' SET @redundant = 'S0001-00000001' Update dbo.POSISTRANSACTIONTABLE SET TRANSACTIONXML.modify ('replace value of(/RetailTransaction/TenderLines/TenderLineItem/tenderTypeId/@redundant) [1] with sql:variable("@replacement")') 

The request is successful, but nothing is changing, and I was wondering if any of you could read this and maybe give me some advice.

Thanks for your time, best regards, Valdi.

PS I am using Microsoft SQL Server 2008 R2 - Express Edition

Answer 1

 update dbo.POSISTRANSACTIONTABLE set TRANSACTIONXML.modify('replace value of (/TenderLines/TenderLineItem/tenderTypeId/text())[1] with (sql:variable("@replacement"))') where XMLCol.exist('/TenderLines/TenderLineItem/tenderTypeId[. = sql:variable("@redundant")]') = 1 

Something to check:

 declare @T table(XMLCol xml) insert into @T values ('<TenderLines> <TenderLineItem> <tenderTypeId>S0001-00000001</tenderTypeId> </TenderLineItem> </TenderLines>'), ('<TenderLines> <TenderLineItem> <tenderTypeId>S0003-00000003</tenderTypeId> </TenderLineItem> </TenderLines>') DECLARE @replacement as varchar(50) DECLARE @redundant as varchar(50) SET @replacement = '2' SET @redundant = 'S0001-00000001' update @T set XMLCol.modify('replace value of (/TenderLines/TenderLineItem/tenderTypeId/text())[1] with (sql:variable("@replacement"))') where XMLCol.exist('/TenderLines/TenderLineItem/tenderTypeId[. = sql:variable("@redundant")]') = 1 select * from @T 

Note that this will only replace one tenderTypeId value in XML for each row. If there are several tender tags in the xml (on one line) and you want to replace them, you need to put the update statement in the while and replace it until where XMLCol.exist('/TenderLines/TenderLineItem/tenderTypeId[. = sql:variable("@redundant")]') = 1 .