[SOLVED] How can I extract subscription priority from ReportServerXML?

How can I extract subscription priority from ReportServerXML?

I'm working in SQL Server 2014 and with Microsofts out of the box ReportServer database. I've been tasks with creating a script that will pull the subscription priority because of this bug.

I've written the following query using: SQL, XQuery, and XPath.

;WITH XMLNAMESPACES (DEFAULT 'http://schemas.microsoft.com/sqlserver/reporting/2010/01/reportdefinition','http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd )
,s
as (SELECT  
      s.Report_OID
     ,s.SubscriptionID
     ,s.OwnerID
     ,s.Description 
     ,s.LastStatus
     ,s.EventType
     ,CONVERT(XML,CONVERT(VARCHAR(MAX),s.ExtensionSettings)) AS reportXML
    FROM ReportServer.dbo.Subscriptions as s
    WHERE s.ExtensionSettings is not null
   )
--SELECT s.reportxml.value('/ParameterValues[1]','varchar(150)') FROM s

SELECT 
 con.Report_OID
,con.test
FROM (SELECT 
       s.Report_OID
      ,dsn.value('Name[1]','varchar(150)') as test
      FROM s
      cross apply reportxml.nodes('/ParameterValues/ParameterValue')  as r(dsn)
     ) con

I also have 2 example xml documents that are pasted below.

   <ParameterValues>
      <ParameterValue>
        <Name>FILENAME</Name>
        <Field>subscriber_domestic_id</Field>
      </ParameterValue>
      <ParameterValue>
        <Name>PATH</Name>
        <Field>folder</Field>
      </ParameterValue>
      <ParameterValue>
        <Name>RENDER_FORMAT</Name>
        <Value>PDF</Value>
      </ParameterValue>
      <ParameterValue>
        <Name>WRITEMODE</Name>
        <Value>OverWrite</Value>
      </ParameterValue>
      <ParameterValue>
        <Name>FILEEXTN</Name>
        <Value>True</Value>
      </ParameterValue>
      <ParameterValue>
        <Name>USERNAME</Name>
        <Field>login</Field>
      </ParameterValue>
      <ParameterValue>
        <Name>PASSWORD</Name>
        <Field>mypasswordstackOF</Field>
      </ParameterValue>
    </ParameterValues>

The second document here.

<ParameterValues>
  <ParameterValue>
    <Name>TO</Name>
    <Value>jonjones@email.com;billiejean@email.com;prettyfloyd@email.com</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>CC</Name>
    <Value>a@email.com;g@email.com.com;</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>IncludeReport</Name>
    <Value>True</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>RenderFormat</Name>
    <Value>PDF</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Subject</Name>
    <Value>@ReportName was executed at @ExecutionTime</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>IncludeLink</Name>
    <Value>True</Value>
  </ParameterValue>
  <ParameterValue>
    <Name>Priority</Name>
    <Value>NORMAL</Value>
  </ParameterValue>
</ParameterValues>

From this you can see that not all documents have a priority. My current query doesn't error out but it does not return anything. I believe i'm using the nodes() function correctly. Any ideas on how to correctly approach this?

Solution

For future purposes and if someone is looking to accomplish the same type of extraction, I'm pasting my code below:

;WITH
s
as (SELECT  
      s.Report_OID
     ,s.SubscriptionID
     ,s.OwnerID
     ,s.Description 
     ,s.LastStatus
     ,s.EventType
     ,CONVERT(XML,CONVERT(VARCHAR(MAX),s.ExtensionSettings)) AS reportXML
    FROM ReportServer.dbo.Subscriptions as s (NOLOCK)
    WHERE s.ExtensionSettings is not null
   )

SELECT 
 con.Report_OID
,con.Node
,con.PriorityLevel
INTO #Temp
FROM (SELECT 
       s.Report_OID
      ,dsn.value('Name[1]','varchar(150)') as Node
      ,dsn.value('Value[1]','varchar(150)') as PriorityLevel
      FROM s
      cross apply reportxml.nodes('/ParameterValues/ParameterValue')  as r(dsn)
     ) con

SELECT 
 t.Report_OID
,c.Name as 'ReportName'
,t.Node
,t.PriorityLevel
,c.Path
FROM #Temp t
    join ReportServer.dbo.Catalog c (NOLOCK)
        on t.Report_OID = c.ItemID 
WHERE 1=1 
and Node = 'Priority'

Enjoy!