Help with parsing xml to get an element value

  • 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!

  • 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?

  • 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