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

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?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source