SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SQL Challenge: Updating parts of fields.


SQL Challenge: Updating parts of fields.

Author
Message
Raymond.Pietrzak
Raymond.Pietrzak
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 16
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>
lorrin.ferdinand
lorrin.ferdinand
SSC Rookie
SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)SSC Rookie (29 reputation)

Group: General Forum Members
Points: 29 Visits: 42
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
Raymond.Pietrzak
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 16
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
Raymond.Pietrzak
Valued Member
Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)Valued Member (59 reputation)

Group: General Forum Members
Points: 59 Visits: 16
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
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218496 Visits: 41996
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Eirikur Eiriksson
Eirikur Eiriksson
SSC-Forever
SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)SSC-Forever (41K reputation)

Group: General Forum Members
Points: 41284 Visits: 19501
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.
Cool
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
Jeff Moden
SSC Guru
SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)SSC Guru (218K reputation)

Group: General Forum Members
Points: 218496 Visits: 41996
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.
Cool
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search