title: Query on SQL Server Reporting Services Database, ReportServer.Catalog table to analyze report parameters
body: <p>I am trying to write a query that pulls out the names of all of the parameters in my report server. The parameters data is stored as XML in an ntext field so I don't think I can use xquery on it. </p>
  
  <p>What I initially started doing was making case statements for each parameter name I expect to find but, this is going to be tedious and I don't think the report developers were totally consistent with parameter names (and in fact that is one of my reasons for wanting the query). </p>
  
  <p>Here is what i have so far, which is pretty simplistic:</p>
  
  <pre><code>SELECT [Path], [Name], CreationDate, ModifiedDate, Parameter, 
  CASE WHEN Parameter LIKE '%&lt;Name&gt;UserId&lt;/Name&gt;%' THEN 'Yes' ELSE 'No' END AS  'UserId', 
  CASE WHEN Parameter LIKE '%&lt;Name&gt;Country&lt;/Name&gt;%' THEN 'Yes' ELSE 'No' END AS 'Country',
  CASE WHEN Parameter LIKE '%&lt;Name&gt;Office&lt;/Name&gt;%' THEN 'Yes' ELSE 'No' END AS 'Office'
  FROM dbo.Catalog
  WHERE type IN (2, 4)
  AND [path] NOT LIKE '/Enterprise Reports%' 
  AND [Path] NOT LIKE '/Email Subscription%'
  ORDER BY [Path]
  </code></pre>
  
  <p>What I think I want is something more like this: </p>
  
  <pre><code>SELECT [Path], [Name], CreationDate, ModifiedDate, Parameter.query(/Parameters/Parameter/Name)
  FROM dbo.Catalog
  WHERE type IN (2, 4)
  AND [path] NOT LIKE '/Enterprise Reports%' 
  AND [Path] NOT LIKE '/Email Subscription%'
  ORDER BY [Path]
  </code></pre>
  
  <p>The problem is, it's ntext, not xml. Can I just use convert on that? </p>
  
  <p>The other problem is, I don't know the xquery syntax to pull out the name field of the many parameters within the XML. </p>
  
  <p>The document's schema is very simple: </p>
  
  <pre><code>&lt;Parameters&gt;
     &lt;Parameter&gt;
        &lt;Name&gt;Some name&lt;/Name&gt;
        ...some more fields i don't care about...
     &lt;/Parameter&gt;
     &lt;Parameter&gt;
        &lt;Name&gt;Another name&lt;/Name&gt;
     &lt;/Parameter&gt;
     ...more parameters, etc.
  &lt;/Parameters&gt;
  </code></pre>
  
  <p>I just want a list of what is in the names. </p>
  
  <p>There can be many  nodes and each one has a single  node as a child. </p>
  
  <p>UPDATE: </p>
  
  <p>It seems like Nodes() should help me but I am struggling to take the examples on Books Online and a Simple Talk article and apply it to my situation. Here is what I have so far: </p>
  
  <pre><code>SELECT [Path], [Name], CreationDate, ModifiedDate, Parameter, 
  CASE WHEN Parameter LIKE '%&lt;Name&gt;UserId&lt;/Name&gt;%' THEN 'Yes' ELSE 'No' END AS 'UserId', 
  CASE WHEN Parameter LIKE '%&lt;Name&gt;Country&lt;/Name&gt;%' THEN 'Yes' ELSE 'No' END AS 'Country',
  CASE WHEN Parameter LIKE '%&lt;Name&gt;Office&lt;/Name&gt;%' THEN 'Yes' ELSE 'No' END AS 'Office'
  , CAST(Parameter AS XML).nodes('/Parameters/Parameter/Name')
  FROM dbo.Catalog
  WHERE type IN (2, 4)
  AND [path] NOT LIKE '/Enterprise Reports%' 
  AND [Path] NOT LIKE '/Email Subscription%'
  ORDER BY [Path]
  </code></pre>