title: XQuery SQL Extract text value from a child element of a specific node
body: <p>I'm struggling with the following situation:  I need to extract that '10000' after  </p>
  
  <pre><code>...name="stateid"&gt; &lt;from&gt;10000&lt;/from&gt;
  </code></pre>
  
  <p>I've managed to extract some that I need from the first node audittrail </p>
  
  <pre><code>SELECT ref.value ('@datetime', 'nvarchar(364)') as [LastTimeActioned],
  ref.value ('@changedby', 'nvarchar(364)') as [Actioned_By]
  FROM Audit 
  CROSS APPLY audit.Xml.nodes ('/audittrail') R(ref)
  
  
  &lt;audittrail id="137943" datetime="29-Feb-2016 15:42:06" changedby="quality" type="update"&gt;
     &lt;fields&gt;
       &lt;field id="10022" name="Content Control"&gt;
        &lt;mergedValue&gt;dsad&lt;/mergedValue&gt;
        &lt;from /&gt;
        &lt;to&gt;dsad&lt;/to&gt;
       &lt;/field&gt;
       &lt;field id="10027" name="Document Controller"&gt;
        &lt;mergedValue&gt;quality&lt;/mergedValue&gt;
        &lt;from /&gt;
        &lt;to&gt;quality&lt;/to&gt;
      &lt;/field&gt;
      &lt;field id="10028" name="Document Owner"&gt;
        &lt;mergedValue&gt;quality&lt;/mergedValue&gt;
        &lt;from /&gt;
        &lt;to&gt;quality&lt;/to&gt;
      &lt;/field&gt;
      &lt;field id="10029" name="Document Type"&gt;
        &lt;mergedValue&gt;Contract/Agreement&lt;/mergedValue&gt;
        &lt;from /&gt;
        &lt;to&gt;Contract/Agreement&lt;/to&gt;
      &lt;/field&gt;
      &lt;field id="10067" name="StateId"&gt;
        &lt;from&gt;10000&lt;/from&gt;
        &lt;to&gt;10000&lt;/to&gt;
      &lt;/field&gt;
      ....
    &lt;/fields&gt;
  &lt;/audittrail&gt;
  </code></pre>