title: SQL Select XML Node based on sibling node attribute value
body: <pre><code>&lt;Findings&gt;
     &lt;Finding EcinRecordID="1042893"&gt;
        &lt;Name&gt;Goal Length of Stay for the ORG&lt;/Name&gt;
        &lt;Selected Value="0" DisplayValue="No"/&gt;
     &lt;/Finding&gt;
     &lt;Finding EcinRecordID="1042894"&gt;
        &lt;Name&gt;Goal Length of Stay for the GRG&lt;/Name&gt;
        &lt;Selected Value="1" DisplayValue="Yes"/&gt;
        &lt;NoteText&gt;3 days&lt;/NoteText&gt;
     &lt;/Finding&gt;
  &lt;/Findings&gt;
  </code></pre>
  
  <p>2 challenges:</p>
  
  <ol>
  <li>Select node value of Findings/Finding/Name where Findings/Finding/Selected Value = "1"</li>
  <li>Select node value of Findings/Finding/NoteText where Findings/Finding/Selected Value = "1"</li>
  </ol>
  
  <p>Putting this into a stored procedure.  I've tried at least 3 dozen versions using query, exists and value.  I can get the whether the Selected Value = '1', but can't seem to assign the corresponding Name value in the Select statement.</p>
  
  <pre><code>SELECT
     p.value('(Payments[1]/Payment[1]/PreAuthCertNumber)[1]', 'varchar(20)') AS PriorAuthNumber
     ,qa.value('(Name[1])','varchar(255)') AS Question
     ,qa.value('(Findings/Finding/Name)[1]','varchar(255)') AS Answer
      FROM #ValueExample
      CROSS APPLY XMLDocument.nodes('/OutboundDataFeed/Patient/PatientAdmission') as t(p)
      CROSS APPLY XMLDocument.nodes('/OutboundDataFeed/Patient/PatientAdmission/CMAssessments/CMAssessment/Sections/Section/Questions/Question') as u(qa)
  </code></pre>
  
  <p>Thanks!</p>