Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 check 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)
`
share|improve this question
    
What exactly do you need here? Default dates as in 1 week/month/year ago? Do you want to be able to edit these dates in reports and do you want mail subscriptions to automatically enter dates each time it is sent i.e weekly monthhly etc.. –  Axle Mar 14 '12 at 13:37
    
If you run the script, you see that it returns null instead of giving me the EndDate value found in the XML contained within ReportServer.dbo.Subscriptions.MatchData. I have a bigger query working to verify correct data sources based on how we implement and organize our ssrs reports. I want to be able to also include the Subscription's End Date to the results so that I can also provide an alert if this is an active subscription. I think this is more of a "how do I query the XML correctly?" question than a "am I looking in the right place?" question. This is the value that I need. –  dev_etter Mar 14 '12 at 13:42
    
I'm having the same issue - hopefully some dba.se experts can help out! –  JHFB May 7 '12 at 17:56
1  
@JHFB answer is below if you still need it. –  WT_W May 9 '12 at 1:16
add comment

migrated from stackoverflow.com May 7 '12 at 17:48

This question came from our site for professional and enthusiast programmers.

1 Answer

up vote 3 down vote accepted

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

share|improve this answer
    
Thanks, @WT_W!! –  JHFB May 9 '12 at 10:52
1  
Excellent, thank you so much! I had tried one or two namespaces that are used in my other SSRS metadata scripts, but to no avail. Thanks for the link too. –  dev_etter Jun 27 '12 at 16:20
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.