Monday, August 9, 2010

Reporting Services and xml

I am currently struggling to create method of scheduling Reporting service subscriptions without having that stupid GUID as the job name. First I needed to learn more about querying the dubiously formatted Reporting services xml.

USE [ReportServer];

/****** Script for SelectTopNRows command from SSMS  ******/

WITH cteSubs (SubscriptionId, Params) AS
(
SELECT
SubscriptionID
,  CAST(CAST([ExtensionSettings] AS NVARCHAR(max)) AS XML) AS params
  FROM [dbo].[Subscriptions] subs
  INNER JOIN [dbo].[Schedule] sch
  ON subs.SubscriptionID = sch.[EventData]
 )
SELECT
SubscriptionID
,(SELECT nref.value('Value[1]', 'nvarchar(50)') Comment FROM Params.nodes('/ParameterValues/ParameterValue') AS R(nref) WHERE  nref.exist('.[Name = "Comment"]') = 1) AS Comment
 FROM cteSubs

This extracts the value of the comment so I can use it for the Job Name later