Tuesday, 3 February 2015

SQL Server 2008 R2 XML to Relational Data



I don't know a lot about XML and even less about the XML data type in SQL Server 2008 R2. I have been reading a lot of posts and trying to understand them but, I confess, I'm now totally confused.


I have the following XML in a column called 'MatchData':



<ScheduleDefinition xmlns:xsd="http://ift.tt/tphNwY" xmlns:xsi="http://ift.tt/ra1lAU">
<StartDateTime xmlns="http://ift.tt/16qqYsx">2014-02-07T08:05:00.000+00:00</StartDateTime>
<WeeklyRecurrence xmlns="http://ift.tt/16qqYsx">
<WeeksInterval>1</WeeksInterval>
<DaysOfWeek>
<Monday>true</Monday>
</DaysOfWeek>
</WeeklyRecurrence>
</ScheduleDefinition>


I've tried the following:



;with cte as (
select top 1
SubscriptionID,
[MatchData] = cast(MatchData as XML)
from dbo.Subscriptions
)
select MatchData.value('ScheduleDefinition[1]', 'varchar(max)') as XMLValue
from cte


But it gives me every element value as a single, concatenated varchar:



2014-02-07T08:05:00.000+00:001true


WTF?


Please can anyone show me (and explain, if it's not too much trouble) how I extract each of StartDateTime, WeeksInterval and Monday into 3 separate columns?


No comments:

Post a Comment