title: How to properly parse this xml response
body: <p>We are using a currency service for currency exchange.  There xml web response is not in the greatest format.  It looks like such</p>
  
  <pre><code>&lt;RESPONSE&gt;
     &lt;EXPR&gt;USD&lt;/EXPR&gt; 
     &lt;EXCH&gt;CAD&lt;/EXCH&gt; 
     &lt;AMOUNT&gt;1&lt;/AMOUNT&gt; 
     &lt;NPRICES&gt;1&lt;/NPRICES&gt; 
     &lt;CONVERSION&gt;
        &lt;DATE&gt;Thu, 07 Apr 2011 21:00:00 GMT&lt;/DATE&gt; 
        &lt;ASK&gt;1.0418&lt;/ASK&gt; 
        &lt;BID&gt;1.0415&lt;/BID&gt; 
     &lt;/CONVERSION&gt;
     &lt;EXPR&gt;USD&lt;/EXPR&gt; 
     &lt;EXCH&gt;AUD&lt;/EXCH&gt; 
     &lt;AMOUNT&gt;1&lt;/AMOUNT&gt; 
     &lt;NPRICES&gt;1&lt;/NPRICES&gt; 
     &lt;CONVERSION&gt;
        &lt;DATE&gt;Thu, 07 Apr 2011 21:00:00 GMT&lt;/DATE&gt; 
        &lt;ASK&gt;1.0461&lt;/ASK&gt; 
        &lt;BID&gt;1.0459&lt;/BID&gt; 
     &lt;/CONVERSION&gt;
  &lt;/RESPONSE&gt;
  </code></pre>
  
  <p>So I tried to write the following SQL Statement</p>
  
  <pre><code>SELECT 
      T.c.value('../EXPR[1]', 'VARCHAR(3)')
  ,   T.c.value('../EXCH[1]', 'VARCHAR(3)')
  ,   T.c.value('ASK[1]', 'MONEY')
  FROM @xml.nodes('/RESPONSE/CONVERSION') T(c)
  </code></pre>
  
  <p>But since both Headers (EXPR and EXCH) are on the same level, is there anyway I can pick the Expr that is above the conversion.  <b>And without reading as a text file in C#.  </b>
  I understand how I would accomplish that.</p>