Eirikur Eiriksson (5/30/2014)

---

Raymond.Pietrzak (5/30/2014)

---

I was able to write my query against the VarChar(4000) data type. This worked.

Update [WBPROD].[dbo].[TASK_SCHEDULE]

Set TSKSCD_DESCRIPTION = Replace(TSKSCD_DESCRIPTION, '<timezoneId>0</timezoneId><timezone>America/New_York</timezone>', '<timezoneId>1</timezoneId><timezone>America/Chicago</timezone>')

Update [WBPROD].[dbo].[TASK_SCHEDULE]

Set TSKSCD_DESCRIPTION = Replace(TSKSCD_DESCRIPTION,

SubString([TSKSCD_SCHEDULE], CHARINDEX('<time>', [TSKSCD_SCHEDULE]) + 6, 8),

Cast(DateAdd(hour, -1, Cast(SubString([TSKSCD_SCHEDULE], CHARINDEX('<time>', [TSKSCD_SCHEDULE]) + 6, 8) As Time(0))) As Char(8)))

Where Cast(SubString([TSKSCD_SCHEDULE], CHARINDEX('<time>', [TSKSCD_SCHEDULE]) + 6, 8) As Time(0)) >= '01:00:00'

Here is a quick demonstration of the XML and modify, adjusted to your data. It reads the previous date and time values, combines them into a datetime, adjusts the timezone difference and finally updates the XMl.

😎

USE tempdb;

GO

DECLARE @TZ_ID INT = 1;

DECLARE @H_DIFF_MINUTE INT = -60;

DECLARE @TZ_NAME VARCHAR(128) = 'America/Chicago';

DECLARE @NEW_TIME TIME(0);

DECLARE @NEW_DATE DATE;

DECLARE @SXML XML = N'<scheduleitem>

<schedule>

<frequency>

<weekly weeklyInterval="1">

<WED />

</weekly>

</frequency>

<startDate>2008-08-05</startDate>

<time>22:30:00</time>

<timezoneId>0</timezoneId>

<timezone>America/New_York</timezone>

</schedule>

</scheduleitem>';

/*

Retrieve the date and time values from the xml,

combine the values into a datetime2 and add the

timezone offset.

*/

;WITH BDATA AS

(

SELECT TOP (1)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS XRID

,DATEADD(

MINUTE

,@H_DIFF_MINUTE

,DATEADD(

MINUTE

,DATEDIFF(

MINUTE

,CAST(SCH.EDULE.value('startDate[1]','DATETIME2(0)') AS TIME)

,CAST(SCH.EDULE.value('time[1]','DATETIME2(0)') AS TIME)

)

,SCH.EDULE.value('startDate[1]','DATETIME2(0)')

)

) AS TRTIME

FROM @SXML.nodes('scheduleitem') AS SCI(TEM)

OUTER APPLY SCI.TEM.nodes('schedule') AS SCH(EDULE)

)

/*

Split the new datetime value into date and time

*/

SELECT

@NEW_DATE = CAST(BD.TRTIME AS DATE)

,@NEW_TIME = CAST(BD.TRTIME AS TIME(0))

FROM BDATA BD;

/*

Update the xml

*/

SET @SXML.modify('replace value of (/scheduleitem/schedule/time[1]/text())[1] with sql:variable("@NEW_TIME")');

SET @SXML.modify('replace value of (/scheduleitem/schedule/startDate[1]/text())[1] with sql:variable("@NEW_DATE")');

SET @SXML.modify('replace value of (/scheduleitem/schedule/timezoneId[1]/text())[1] with sql:variable("@TZ_ID")' );

SET @SXML.modify('replace value of (/scheduleitem/schedule/timezone[1]/text())[1] with sql:variable("@TZ_NAME")' );

/*

View the changes

*/

SELECT @SXML

Results

<scheduleitem>

<schedule>

<frequency>

<weekly weeklyInterval="1">

<WED />

</weekly>

</frequency>

<startDate>2008-08-05</startDate>

<time>21:30:00</time>

<timezoneId>1</timezoneId>

<timezone>America/Chicago</timezone>

</schedule>

</scheduleitem>

Changing the time to a value less than an hour after midnight, i.e. 00:30:00, the date will adjust as well.

<scheduleitem>

<schedule>

<frequency>

<weekly weeklyInterval="1">

<WED />

</weekly>

</frequency>

<startDate>2008-08-04</startDate>

<time>23:30:00</time>

<timezoneId>1</timezoneId>

<timezone>America/Chicago</timezone>

</schedule>

</scheduleitem>