May 23, 2013 at 3:53 pm
I have a table with a column Col1 of data type varchar(max).
Col1 contains strings that are in XML format as below.
<?xml version="1.0" encoding="utf-16" standalone="yes"?>
<Schedule xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer">
<StartDateTime>2013-03-25T16:05:26</StartDateTime>
<Timezone>CDT</Timezone>
<WeeklyRecurrence>
<WeeksInterval>2</WeeksInterval>
<DaysOfWeek>
<Sunday>true</Sunday>
<Monday>false</Monday>
<Tuesday>true</Tuesday>
<Wednesday>false</Wednesday>
<Thursday>false</Thursday>
<Friday>false</Friday>
<Saturday>false</Saturday>
</DaysOfWeek>
</WeeklyRecurrence>
</Schedule>
I need to extract the Timezone value (ie CDT).
Can you help? Thank you!
May 23, 2013 at 5:16 pm
Looking at the column data type ( varchar max) I suppose it is safe for me to look for <Timezone> and extract what comes after it up to </Timezone>.
I don't have to parse it as xml.
Comments?
May 23, 2013 at 5:32 pm
This is what I came up with. Please help optimize it. Thank you!
declare @x varchar(max)
set @x = '<?xml version="1.0" encoding="utf-16" standalone="yes"?><Schedule xmlns="http://schemas.microsoft.com/sqlserver/reporting/2010/03/01/ReportServer"><StartDateTime>2013-03-25T16:05:26</StartDateTime><Timezone>CDT</Timezone><WeeklyRecurrence><WeeksInterval>2</WeeksInterval><DaysOfWeek><Sunday>true</Sunday><Monday>false</Monday><Tuesday>true</Tuesday><Wednesday>false</Wednesday><Thursday>false</Thursday><Friday>false</Friday><Saturday>false</Saturday></DaysOfWeek></WeeklyRecurrence></Schedule>'
declare @StartPos int
SELECT @StartPos= PATINDEX('%<Timezone>%', @x) + len('<Timezone>')
select substring(@x,@startPos, PATINDEX('%</Timezone>%' , @x) - @StartPos) as TimezoneName
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply