Show multiple "parameter" xml nodes values into one row from Report Server Catalog table-CodePudding

I used the following SQL query to get the parameters from the ReportServer's Catalog table. In this query, I tried to use the following query to bring up some values, but it shows one value instead of the original number of parameters:

USE ReportServer
GO

SELECT Name,
CAST(Parameter as xml) [Parameter_XML],
CONCAT(Convert(XML,Parameter).value('(//Parameters/Parameter/Name)[1]','nvarchar(MAX)'), ', [', Convert(XML,Parameter).value('(//Parameters/Parameter/Type)[1]','nvarchar(MAX)'), ']') as [Parameter_List]
FROM Catalog

My expected end result would be like this:

Report Name	Parameter_XML	Parameter_List
My New Report with Parameters	XML goes here	Report_Name [DateTime], ReportDate [DateTime], etc...

How can I bring multiple parameter XML node values into one row?

CodePudding user response：

There is a way to do this:

You simply read the nodes of each report, then use stuff for XML to create a comma seperated list.

USE ReportServer
GO
; with mycte as (
SELECT Name,
CAST(Parameter as xml) [Parameter_XML]

FROM Catalog

where Parameter is not null
 

)
, mycte2 as (
SELECT Name, T2.x.value('(Name)[1]', 'varchar(100)') as parameter_list 
FROM   mycte
CROSS APPLY [Parameter_XML].nodes('//Parameters/Parameter') as T2(x)
)

SELECT DISTINCT 
       Name as report_name, 
       STUFF(
(
    SELECT ', '   parameter_list
    FROM mycte2 A1
    WHERE A1.Name = A2.Name FOR XML PATH('')
), 1, 1, '') AS report_parameters
FROM mycte2 A2;

You can expand on this to get the types as well. But this should be a good starting point if not the whole solution!