XML Query Issues

[kentover]

I pull the following XML from a field in SQL Server 2008 and need to query into the string to get the values. Each attempt has returned NULL or nothing at all.

<ScheduleDefinition xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance">[/URL]
	<StartDateTime xmlns="[URL unfurl="true"]http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">2010-11-19T15:35:00.000-06:00</StartDateTime>[/URL]
	<WeeklyRecurrence xmlns="[URL unfurl="true"]http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices">[/URL]
		<WeeksInterval>1</WeeksInterval>
		<DaysOfWeek>
			<Monday>true</Monday>
			<Tuesday>true</Tuesday>
			<Wednesday>true</Wednesday>
			<Thursday>true</Thursday>
			<Friday>true</Friday>
		</DaysOfWeek>
	</WeeklyRecurrence>
</ScheduleDefinition>

The following code is used to return values. I have been able to use the code for other XML strings but this one fails. Just FYI - This is data from the SSRS Subscriptions table in the MatchData field. MSDN articles place the StartDateTime inside WeeklyRecurrence but that is not actually true.

DECLARE		@TO XML 
DECLARE		@String VARCHAR(MAX)


SELECT		@TO = MatchData
FROM		[ReportServer].dbo.Subscriptions 
WHERE		SubscriptionID = '9B1AF007-00AB-45E0-9C26-386E49BE926B'

SELECT		@String = @TO.value (N'(/ScheduleDefinition/StartDateTime)[1]',N'VARCHAR(MAX)')


SELECT		@String AS TEST

Thanks in advance for any help.
KO

 

[kentover]

OK, I found the answer. It is ugly but works when you strip out the XML tags. Here is what worked.

DECLARE		@TO XML 
DECLARE		@String NVARCHAR(MAX)


SELECT		@TO = REPLACE(REPLACE(CAST(MatchData AS NVARCHAR(MAX)),' xmlns:xsd="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema"[/URL] xmlns:xsi="[URL unfurl="true"]http://www.w3.org/2001/XMLSchema-instance"',''),'[/URL] xmlns="[URL unfurl="true"]http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices"','')[/URL]
FROM		[ReportServer].dbo.Subscriptions 
WHERE		SubscriptionID = '9B1AF007-00AB-45E0-9C26-386E49BE926B'


SELECT		@String = @TO.value (N'(/ScheduleDefinition//Monday)[1]',N'VARCHAR(MAX)')


SELECT		@String AS TEST