0

I'm trying to extract xml metadata from from SQL Server's Report Server Database. My current query is listed below: 

SELECT 
 a.Name as 'ReportName'
,a.ReportXML
,Parameter = x.value('(Parameter/Name)[1]','VARCHAR(250)')
FROM (SELECT C.Name,c.itemID,CONVERT(XML,CONVERT(VARCHAR(MAX),C.Parameter)) AS reportXML
      FROM ReportServer.dbo.Catalog C
      WHERE C.Content is not null
      and c.Type = 2 -- Report only
      ) a

cross apply reportXML.nodes('/Parameters') r (x)
WHERE 1=1 
and name ='ReportName'

My objective is to return all parameters associated with a report. The x.value method will only return 1 value at most. (It currently returns the first parameter of the report because 1 is hard coded in the string literal.) I know there are 5 parameters for the report I'm looking at.

Is there another function with similar syntax that will allow me to return all the values? Or is there a wildcard that I can use in place of the number? I've tried multiple functions on msdn with no luck.

Shnugo
  • 66,100
  • 9
  • 53
  • 114
SQLUser44
  • 51
  • 2
  • 9

1 Answers1

1

Your query is using nodes('/Parameters'). This will return a resultset with one row for each Parameters element - but there is only one probably.

With Parameter = x.value('(Parameter/Name)[1]','VARCHAR(250)') you are reading the first name of the first Parameter. If there are more Parameter elements nested within Parameters you will read only the first...

Without an example of your actual XML it is not easy to answer but:

Try to add one nesting level by adding this below your cross apply

outer apply r.x.nodes('Parameter') AS p (y)

Then change the column to

,Parameter = y.value('Name[1]','VARCHAR(250)')

This should read the first Name element of each Parameter element

If you need further help, please poste an example of your XML.

Btw: I do not understand this:

CONVERT(XML,CONVERT(VARCHAR(MAX),C.Parameter)

What is the initial type of C.Parameter, that you have to cast it to VARCHAR(MAX) and then to XML? If you are still using the deprecated TEXT, NTEXT or IMAGE you should consider to change this!

Shnugo
  • 66,100
  • 9
  • 53
  • 114
  • Thank you! Your solution worked and you are also correct in inferring that C.Parameter is an NTEXT. It is because of this that I converted to varchar(max) and then to XML. Do you mind providing me the reason for changing this from NTEXT or pointing me to some documentation. – SQLUser44 Aug 22 '16 at 20:23
  • 1
    @SQLUser44, [Read this](https://msdn.microsoft.com/en-us/library/ms187993.aspx). NTEXT, TEXT and IMAGE where set to *deprecated* with SQL Server 2000 (!) already. Microsoft will end the support in one of the next versions. Today we use `NVARCHAR(MAX)`, `VARCHAR(MAX)` and `VARBINARY(MAX)`. In your specific case you should use the datatype `XML`. This will 1) guarantee valid content and 2) speed up your queries, as you don't have to perform expensive casts of large types. Btw: Thx for the acceptance. It would be nice to vote me up also, thx! – Shnugo Aug 22 '16 at 20:32