title: SQL XML query for name value pair?
body: <p>First off i'll start by noting that its not my choice to use nvp's within XML and its in the process of being modified. That being said, I have a table that contains an XML column that has the following xml:</p>
  
  <pre><code>&lt;root&gt;
    &lt;results&gt;
      &lt;result name='First Result'&gt;
        &lt;property name='Property1' value='Value1' /&gt;
        &lt;property name='Property2' value='Value2' /&gt;
      &lt;/result&gt;
    &lt;/results&gt;
  &lt;/root&gt;
  </code></pre>
  
  <p>I have the following query that gets me the result name:</p>
  
  <pre><code>SELECT
    T.N.value('@name', 'nvarchar(256)') AS resultName
  FROM
    results CROSS APPLY xmlField.nodes('/root/results/result') AS T(N)
  </code></pre>
  
  <p>But I'm not positive how I could get the property to display. I tried the following:</p>
  
  <pre><code>SELECT
    T.N.value('@name', 'nvarchar(256)') AS resultName,
    T.N.value('/property[@name="Property1"]/@value', 'nvarchar(256)') AS Property
  FROM
    results CROSS APPLY xmlField.nodes('/root/results/result') AS T(N)
  </code></pre>
  
  <p>But I got the following error:</p>
  
  <p>XQuery [results .xmlField.value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'</p>
  
  <p>I know I'm along the right path, but I'm not quite positive what exactly I should be doing. Would anyone be able to point me in the right direction?</p>