February 20, 2012 at 8:58 am
I'm trying to get information on subscriptions, including who they are to be sent to, and what model they use.
Here's where I am. I need the name spaces to get the model name from content in catalog, but it's messing up getting the email address from subscriptions. Any thoughts?
;WITH XMLNAMESPACES('http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS cl,
DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition')
,base AS (
SELECT
rptpath = REPLACE(REPLACE(cat.path,'/{60024184-b184-4936-be92-844b0610c754}',''),cat.name,''),
ShortmodelName = REVERSE(LEFT(REVERSE(CAST(CAST(cat.CONTENT AS VARBINARY(MAX)) AS XML).value('(/Report/DataSources/DataSource/DataSourceReference)[1]','varchar(max)')),CHARINDEX('/',REVERSE(CAST(CAST(cat.CONTENT AS VARBINARY(MAX)) AS XML).value('(/Report/DataSources/DataSource/DataSourceReference)[1]','varchar(max)')))-1)),
cat.name,
sub.SubscriptionID,
email = CAST(sub.ExtensionSettings AS XML).value('(//ParameterValue/Value)[1]','varchar(max)'),
extset = CAST(sub.ExtensionSettings AS XML)
FROM
dbo.Catalog cat JOIN dbo.Subscriptions sub ON
cat.ItemID = sub.Report_OID
WHERE
cat.type = 2
)
SELECT
rptPath,
Name,
shortmodelname,
SubscriptionID,
email,
extset
FROM
base
ORDER BY
rptpath,
name
February 20, 2012 at 10:09 am
One day I will understand XML.... and then it will change.
Anyways, a developer and I came up with this. I will never get how I can change namespaces to a reference that doesn't exist... and then references it like we do here. But it does work.
;WITH XMLNAMESPACES('http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd,
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/componentdefinition' AS cl,
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' AS cd)
,base AS (
SELECT
rptpath = REPLACE(REPLACE(cat.path,'/{60024184-b184-4936-be92-844b0610c754}',''),cat.name,''),
ShortmodelName = REVERSE(LEFT(REVERSE(CAST(CAST(cat.CONTENT AS VARBINARY(MAX)) AS XML).value('(/cd:Report/cd:DataSources/cd:DataSource/cd:DataSourceReference)[1]','varchar(max)')),CHARINDEX('/',REVERSE(CAST(CAST(cat.CONTENT AS VARBINARY(MAX)) AS XML).value('(/cd:Report/cd:DataSources/cd:DataSource/cd:DataSourceReference)[1]','varchar(max)')))-1)),
cat.name,
sub.SubscriptionID,
email = CAST(sub.ExtensionSettings AS XML).value('(//ParameterValue/Value)[1]','varchar(max)'),
extset = CAST(sub.ExtensionSettings AS XML)
FROM
dbo.Catalog cat JOIN dbo.Subscriptions sub ON
cat.ItemID = sub.Report_OID
WHERE
cat.type = 2
)
SELECT
rptPath,
Name,
shortmodelname,
SubscriptionID,
email,
extset
FROM
base
ORDER BY
rptpath,
name
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply