34,590
社区成员
发帖
与我相关
我的任务
分享
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
--Extract all the Datasets of a report, above URI also can be http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition
SELECT C.path AS REPORT_PATH,
T2.DN.value('.', 'varchar(200)') AS DataSetName
FROM dbo.Catalog c
CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(max), c.content)) xmlCriteria) a
CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet/@Name') AS T2(DN)
WHERE C.Type=2
DECLARE @RetVal TABLE
(
REPORT_PATH VARCHAR(500),
DATASETNAME VARCHAR(200)
);
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
--Extract all the Datasets of a report
INSERT INTO @RetVal
SELECT C.path AS REPORT_PATH,
T2.DN.value('.', 'varchar(200)') AS DataSetName
FROM dbo.Catalog c
CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(max), c.content)) xmlCriteria) a
CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet/@Name') AS T2(DN)
WHERE C.Type=2
DECLARE @REPORT_PATH VARCHAR(200)
DECLARE @DataSetName VARCHAR(200)
DECLARE DataSets CURSOR FOR
SELECT * FROM @RetVal
OPEN DataSets
FETCH NEXT FROM DataSets INTO @REPORT_PATH, @DataSetName
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT @DataSetName;
DECLARE @A VARCHAR(10)='';--with should start with ;
--Extract all the parameters of the report Datasets
WITH XMLNAMESPACES(DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
SELECT @REPORT_PATH AS Report_Path,
@DataSetName AS Dataset,
T3.QP.value('.', 'varchar(200)') AS Dataset_Parameter,
T4.CT.value('.', 'varchar(200)') AS Command_Text
FROM dbo.Catalog c
CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(max), c.content)) xmlCriteria) a
CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet[@Name=sql:variable("@DataSetName")]/Query/QueryParameters/QueryParameter/@Name')AS T3(QP)
CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet[@Name=sql:variable("@DataSetName")]/Query/CommandText')AS T4(CT)
WHERE C.path = @REPORT_PATH
FETCH NEXT FROM DataSets INTO @REPORT_PATH, @DataSetName
END
CLOSE DataSets
DEALLOCATE DataSets
WITH XMLNAMESPACES('http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' AS URI1,
'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition' AS URI2,DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition')
SELECT DISTINCT *FROM(
SELECT
T2.DP.value('.', 'varchar(200)') AS Description,
T4.CT.value('.', 'varchar(200)') AS CommandText,
T5.PN.value('.', 'varchar(200)') AS Dataset_Parameter,
NULL STORED_PROCEDURE
FROM ReportServer$GLBSMCSS01.dbo.Catalog c
CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(max), c.content)) xmlCriteria) a
CROSS APPLY A.xmlCriteria.nodes('//Description')AS T2(DP)
CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet')AS T3(QP)
CROSS APPLY T3.QP.nodes('Query/CommandText')AS T4(CT)
CROSS APPLY T3.QP.nodes('Query/QueryParameters/QueryParameter/@Name') AS T5(PN)
--CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet[@Name=''GetCompanyDetails'']/Query/QueryParameters/QueryParameter/@Name')AS T3(QP)
--CROSS APPLY A.xmlCriteria.nodes('//DataSets/DataSet[@Name=''GetCompanyDetails'']/Query/CommandText')AS T4(CT)
UNION ALL
SELECT
T2.DP.value('.', 'varchar(200)') AS Description,
T4.CT.value('.', 'varchar(200)') AS CommandText,
T3.QP.value('.', 'varchar(200)') AS Dataset_Parameter,
NULL STORED_PROCEDURE
FROM ReportServer$GLBSMCSS01.dbo.Catalog c
CROSS APPLY (SELECT CONVERT(XML, CONVERT(VARBINARY(max), c.content)) xmlCriteria) a
CROSS APPLY A.xmlCriteria.nodes('//URI2:Description')AS T2(DP)
CROSS APPLY A.xmlCriteria.nodes('//URI2:DataSets/URI2:DataSet')AS T3(QP)
CROSS APPLY T3.QP.nodes('URI2:Query/URI2:CommandText')AS T4(CT)
CROSS APPLY T3.QP.nodes('URI2:Query/URI2:QueryParameters/URI2:QueryParameter/@Name') AS T5(PN)
--CROSS APPLY A.xmlCriteria.nodes('//URI2:DataSets/URI2:DataSet[@Name=''GetCompanyDetails'']/URI2:Query/URI2:QueryParameters/URI2:QueryParameter/@Name')AS T3(QP)
--CROSS APPLY A.xmlCriteria.nodes('//URI2:DataSets/URI2:DataSet[@Name=''GetCompanyDetails'']/URI2:Query/URI2:CommandText')AS T4(CT)
) A
WHERE 1=1 ORDER BY 1,2,3
--reference http://beyondrelational.com/modules/2/blogs/28/posts/10281/xquery-lab-2-an-example-using-outer-apply.aspx