'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 |
|---|
