title: SQL XQuery Selector for Key/Value Pairs
body: <p>I have some xml in a column like this.</p>
  
  <pre><code>&lt;ScopedWorkflowConfiguration xmlns:i="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://schemas.microsoft.com/workflow/2012/xaml/activities"&gt;
    &lt;appSettings&gt;
      &lt;AppSetting&gt;
        &lt;Key&gt;CurrentWebUri&lt;/Key&gt;
        &lt;Value&gt;http://myurl.org&lt;/Value&gt;
      &lt;/AppSetting&gt;
      &lt;AppSetting&gt;
        &lt;Key&gt;SomethingElse&lt;/Key&gt;
        &lt;Value&gt;1&lt;/Value&gt;
      &lt;/AppSetting&gt;
      &lt;AppSetting&gt;
        &lt;Key&gt;AnotherThing&lt;/Key&gt;
        &lt;Value&gt;420&lt;/Value&gt;
      &lt;/AppSetting&gt;
    &lt;/appSettings&gt;
  &lt;/ScopedWorkflowConfiguration&gt;
  </code></pre>
  
  <p>Trying to select the value of the Value node.</p>
  
  <pre><code>;WITH Casted AS (SELECT t.Id, t.[Configuration] As XmlData FROM dbo.MyTable AS t)
  Select Distinct
       Id
      ,pv.value('//*:Key="CurrentWebUri"/Value', 'nvarchar(max)') As CurrentWebUri
  From
      Casted
      Cross Apply Casted.XmlData.nodes(N'//*:ScopedWorkflowConfiguration//*:appSettings//*:AppSetting') AS A(pv)
  </code></pre>
  
  <p>I know I am close -- but I have having trouble with the syntax here: <strong><em>//</em>:Key="CurrentWebUri"/Value*</strong></p>
  
  <p>The Query above results in this error:</p>
  
  <blockquote>
    <p>A node or set of nodes is required for /</p>
  </blockquote>
  
  <p>Can you help?</p>
  
  <p>Adding a result set for reference to further clarify what I am after:</p>
  
  <pre><code>+----+------------------+------------------+------------------+
  | ID | CurrentWebUri    | SomethingElse    |  AnotherThing    |
  +----+------------------+------------------+------------------+
  |  1 | example.org      |        1         |       420        |
  +----+------------------+------------------+------------------+
  |  2 | example.com      |        6         |       29         |
  +----+------------------+------------------+------------------+
  </code></pre>