XML query/modify problem in SQL server 2008 r2

Question

I'm fairly new to SQL and I'm trying to filter and change the values inside of column that holds an XML document for a purchased order. Here's an example of what the XML document looks like and what I'm searching for.

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

I've got 6000+ rows and not all of them have the same tenderTypeId. I want to filter out the values in tenderTypeId that have 'S0001-00000001' and change them to '2'

So far this is what I've come 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 query is executed successfully but nothing changes and I was wondering if any of you could read over this and possibly give me tips.

Thanks for your time, with best regards, Valdi.

P.S. I'm using Microsoft SQL Server 2008 R2 - Express Edition

Mikael Eriksson · Accepted Answer · 2011-07-11 12:22:05Z

3

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 test on:

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 value of tenderTypeId in the XML for each row. If you have multiple tenderTypeId's in the xml (in one row) and you want to replace them all you need to put the update statement in a while loop and replace as long as where XMLCol.exist('/TenderLines/TenderLineItem/tenderTypeId[. = sql:variable("@redundant")]') = 1.

edited Jul 11, 2011 at 12:22

answered Jul 11, 2011 at 12:14

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

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

2 Comments

Ástvaldur Sigurðsson Over a year ago

thank you very much, this works like a charm. Two last question if you don't mind, what does the [1] stand for in the .modify line? Also I was wondering about the = 1 in the .exist clause?

Mikael Eriksson Over a year ago

@Ástvaldur – [1] means, pick the first node that match the xquery expression and since XMLCol.modify can only update one value at a time it is needed here. XMLCol.exist() returns 0 or 1 where 1 means that the xquery gave a hit.

Collectives™ on Stack Overflow

XML query/modify problem in SQL server 2008 r2

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related