我正在使用SQL SERVER 2008 R2 . 我想从XML格式的RDL数据中提取数据集及其参数 . 我们可以在SSRS数据库中找到xml数据 . 表名是dbo.Catalog .

基于以下查询，我获得了报告的所有数据集 .

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

现在我想拥有每个数据集的所有参数，映射关系应该是report - dataset - parameter . 因此，一个报告可能具有多个数据集，并且数据集可能具有多个参数 .

我使用光标逐个浏览数据集以获得结果，但我相信有一种简单的方法可以获得它 .

我的查询如下

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

任何意见 ？

--- 2017-03-14 18:05:29 ---

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 C1.path                      AS Report_Path,
             --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   dbo.Catalog c1
			 INNER JOIN dbo.Catalog c on c1.LinkSourceID=C.ItemID
             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)/Operational Reporting/Finance/
      WHERE  C1.path like '/Regional Reporting/%/Finance/%'
      UNION ALL
      SELECT C1.path                      AS Report_Path,
             --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   dbo.Catalog c1
			 INNER JOIN dbo.Catalog c on c1.LinkSourceID=C.ItemID
             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)
      WHERE  C1.path like '/Regional Reporting/%/Finance/%' ) A
      WHERE 1=1 and commandtext like 'global.gp_sec_pINSTANCE_COMPANIES'ORDER BY 1,2,3
      
      
      --reference http://beyondrelational.com/modules/2/blogs/28/posts/10281/xquery-lab-2-an-example-using-outer-apply.aspx
sql xml reporting-services xquery