SQL Challenge: Updating parts of fields.

Question

SQL Challenge: Updating parts of fields.

Raymond.Pietrzak

SSC Veteran

Points: 223
More actions
May 30, 2014 at 10:23 am

#307783

I have a table where one of the fields contains XML as in the following. Is there an SQL update statement that can change any occurrences of timezoneIdfrom 0 to 1, and timezone from America/New_York to America/Chicago and time to time - 1 hour?
<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>
After the query is run, rows like the above would be changed to
<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>

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

lorrin.ferdinand SSC Journeyman Points: 81 More actions · Answer 1

Hi,

I'm not going to write the query for you, but will point you in the direction of good documentation on how to update the values in an xml fragment, or xml column...

See the following entry in BOL: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/c310f6df-7adf-493b-b56b-8e3143b13ae7.htm

Good luck!

Raymond.Pietrzak SSC Veteran Points: 223 More actions · Answer 2

Thanks. I did not even know there was an XML Data Type. The data I am working with is stored in a VarChar(4000) column. Good to know.

Raymond.Pietrzak SSC Veteran Points: 223 More actions · Answer 3

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'

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 4

A much better solution would be to "normalize" the XML as a real table with real columns and rows.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182882 More actions · Answer 5

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>

Jeff Moden SSC Guru Points: 1003859 More actions · Answer 6

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>

Just imagine if someone had normalized the data instead of storing it in XML. :sick:

UPDATE tgt

SET TimeZoneID = 1

,TimeZone = 'America/Chicago'

,[Time] = DATEADD(hh,-1,[Time])

FROM dbo.YourTable tgt

WHERE TimeZoneID = 0

AND TimeZone = 'America/New_York'

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)