Sql-server – How to query EndDate value from ReportServer..Subscriptions.MatchData

sql serversql-server-2008ssrsssrs-2008

In SQL 2008 R2, I'm putting together some administrative queries on SSRS metadata to check proper configuration, and other sorts of proactive things.

While querying the ReportServer db, I've been successful in checking reports, shared data sources, etc. The one thing that I am having trouble with is querying for any value within the Subscriptions.MatchData column. Everything from this column comes back as NULL (unless I return the entire contents of the column).

Specifically, I need the value for EndDate. I've tried a number of tweaks to no avail, including toying around with XPath, XMLNamespace, etc. I found this basic example of how it is supposed to work in 2005. What is different about 2008 that this will not work? And how can I query this value in 2008 R2?

`
SELECT
[Description]
,x.m.value('StartDateTime[1]','varchar(30)') AS StartDateTime
,x.m.value('EndDate[1]','varchar(10)') AS EndDate
FROM
(
    SELECT
        [description]
        ,CAST(MatchData AS XML) AS MatchData
    FROM ReportServer.dbo.Subscriptions
) AS P
CROSS APPLY MatchData.nodes('//ScheduleDefinition') x(m)
`

Best Answer

What's throwing it off is the XML name spaces in the tags. To get around this you need to use the with XMLNameSpaces clause.

Give this query a go. I've tested this on a test instance (2008, not 2008 r2), so you may need to change the actual namespace definition to match your xml data.

with xmlnamespaces('http://schemas.microsoft.com/sqlserver/2006/03/15/reporting/reportingservices' as rs)
SELECT
[Description]
,m.value('rs:StartDateTime[1]','varchar(30)') AS StartDateTime
,m.value('rs:EndDate[1]','varchar(30)') AS EndDate
FROM
(
    SELECT
    [description],
    CAST(MatchData AS XML) AS MatchData
    FROM ReportServer.dbo.Subscriptions
) AS P
CROSS APPLY MatchData.nodes('/ScheduleDefinition') x(m)

SQL BOL reference for the WITH XMLNAMESPACES clause:

http://msdn.microsoft.com/en-us/library/ms177400.aspx

Related Question