问题解决了。 重用cross apply 后的“节点”。这样就不用在使用cursor了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
'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