Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

SQL Challenge: Updating parts of fields. Expand / Collapse
Author
Message
Posted Friday, May 30, 2014 10:23 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:52 PM
Points: 6, Visits: 11
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>
Post #1576190
Posted Friday, May 30, 2014 12:02 PM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, June 23, 2014 7:26 PM
Points: 6, Visits: 25
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!
Post #1576226
Posted Friday, May 30, 2014 12:28 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:52 PM
Points: 6, Visits: 11
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.
Post #1576237
Posted Friday, May 30, 2014 1:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, June 20, 2014 2:52 PM
Points: 6, Visits: 11
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'
Post #1576254
Posted Friday, May 30, 2014 10:13 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 35,792, Visits: 32,473
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1576326
Posted Friday, May 30, 2014 10:14 PM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 8:21 AM
Points: 2,542, Visits: 7,143
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>

Post #1576327
Posted Friday, May 30, 2014 10:36 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:58 PM
Points: 35,792, Visits: 32,473
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.

 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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1576331
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse