title: tsql xml get value from same level as selected attribute
body: <p>I am using sql server 2014 and need to query a column (extensionsettings) from the ssrs subscriptions  table and need to pull out Render Formats for each subscription but don't know how to go about it. Here is the query as is:</p>
  
  <pre><code>SELECT  Sch.ScheduleID,
          CAT.[Path],
          cat.Name,
          CAST(s.ExtensionSettings AS XML)
  FROM    [ReportServer$REPORTS].dbo.Subscriptions S
          JOIN [ReportServer$REPORTS].dbo.ReportSchedule Sch ON S.SubscriptionID = Sch.SubscriptionID
          JOIN [ReportServer$REPORTS].dbo.[Catalog] CAT on S.[Report_OID] = CAT.[ItemID]
  </code></pre>
  
  <p>Here is an example of what the XML looks like.</p>
  
  <pre><code>&lt;ParameterValues&gt;
    &lt;ParameterValue&gt;
      &lt;Name&gt;TO&lt;/Name&gt;
      &lt;Value&gt;example@test.com&lt;/Value&gt;
    &lt;/ParameterValue&gt;
    &lt;ParameterValue&gt;
      &lt;Name&gt;IncludeReport&lt;/Name&gt;
      &lt;Value&gt;True&lt;/Value&gt;
    &lt;/ParameterValue&gt;
    &lt;ParameterValue&gt;
      &lt;Name&gt;RenderFormat&lt;/Name&gt;
      &lt;Value&gt;EXCEL&lt;/Value&gt;
    &lt;/ParameterValue&gt;
    &lt;ParameterValue&gt;
      &lt;Name&gt;Subject&lt;/Name&gt;
      &lt;Value&gt;@ReportName&lt;/Value&gt;
    &lt;/ParameterValue&gt;
    &lt;ParameterValue&gt;
      &lt;Name&gt;IncludeLink&lt;/Name&gt;
      &lt;Value&gt;False&lt;/Value&gt;
    &lt;/ParameterValue&gt;
    &lt;ParameterValue&gt;
      &lt;Name&gt;Priority&lt;/Name&gt;
      &lt;Value&gt;NORMAL&lt;/Value&gt;
    &lt;/ParameterValue&gt;
  &lt;/ParameterValues&gt;
  </code></pre>
  
  <p>My trouble googling this is that i'm not sure what each part of the XML is called but basically I want for where the Name part of Paramter Value =  'RenderFormat' I want to return the corresponding Value value.</p>
  
  <p>So for a given list of subscriptions return:</p>
  
  <p>SchedID, Reports/ReportA, ReportA, TRUE</p>
  
  <p>SchedID, Reports/ReportB, ReportB, FALSE</p>
  
  <p>etc. </p>