Urgent replacement in part of column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Urgent replacement in part of column

Author

Topic

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-02 : 04:34:06

I have 4900 rows in a sql table that I need to update part of it on.

The end date is what I need to change but the create date is unique

example

<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2010-11-19T13:44:15.253</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-01T00:00:00</ExpirationDate></PromotionRuleBase></ArrayOfPromotionRuleBase>

How can I change all with jus the Expiration date part?

raindear
Yak Posting Veteran

64 Posts

Posted - 2014-01-02 : 05:56:26

was thinking of something like this? Anybody have any ideas?

use temp2jan
update Promotions
set PromotionRuleData = REPLACE(PromotionRuleData, '<ExpirationDate>2014-01-01T00:00:00</ExpirationDate>', '<ExpirationDate>2015-01-01T00:00:00</ExpirationDate>')
where PromotionRuleData = '<ExpirationDate>2014-01-01T00:00:00</ExpirationDate>'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-02 : 12:22:03

Assuming you'll have only one ExpirationDate value per XML you can use a method like this


declare @t table
(
x xml
) 
declare @var datetime = getdate()
insert @t
values('<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><PromotionRuleBase xsi:type="StartDatePromotionRule"><StartDate>2010-11-19T13:44:15.253</StartDate></PromotionRuleBase><PromotionRuleBase xsi:type="ExpirationDatePromotionRule"><ExpirationDate>2014-01-01T00:00:00</ExpirationDate></PromotionRuleBase></ArrayOfPromotionRuleBase>')

UPDATE @t
SET x.modify('replace value of (/ArrayOfPromotionRuleBase/PromotionRuleBase/ExpirationDate/text())[1] with sql:variable("@var")')

select * from @t


output
------------------------------
<ArrayOfPromotionRuleBase xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <PromotionRuleBase xsi:type="StartDatePromotionRule">
    <StartDate>2010-11-19T13:44:15.253</StartDate>
  </PromotionRuleBase>
  <PromotionRuleBase xsi:type="ExpirationDatePromotionRule">
    <ExpirationDate>2014-01-01T00:00:00</ExpirationDate>
  </PromotionRuleBase>
</ArrayOfPromotionRuleBase>

Here @t corresponds to your table, x corresponds to column containing xml and @var is variable through which you pass value to be replaced

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources