title: Select values from XML in SQL
body: <p>I need to select values from an XML document. These values are stored as childnodes as follows:  </p>
  
  <pre><code>   &lt;customers&gt;
          &lt;customer&gt;
              &lt;kunnr&gt;1&lt;/kunnr&gt;
              &lt;kdgrp&gt;2&lt;/kdgrp&gt;
          &lt;/customer&gt;
          &lt;customer&gt;
              &lt;kunnr&gt;2&lt;/kunnr&gt;
              &lt;kdgrp&gt;2&lt;/kdgrp&gt;
          &lt;/customer&gt;
      &lt;/customers&gt;
  </code></pre>
  
  <p>I need to select the values of kunnr and kdgrp for every customer node. I expect a result like this:</p>
  
  <pre><code>kunnr       kdgrp
  1           2
  2           2
  </code></pre>
  
  <p>What I tried so far:</p>
  
  <pre><code>SELECT  @xml.query('/customers/customer/kunnr') AS KUNNR,
          @xml.query('/customers/customer/kdgrp') AS KDGRP
  </code></pre>
  
  <p>This results in one row with two colums containing XML:</p>
  
  <pre><code>KUNNR                                     KDGRP
  &lt;kunnr&gt;1&lt;/kunnr&gt;&lt;kunnr&gt;2&lt;/kunnr&gt;          &lt;kdgrp&gt;2&lt;/kdgrp&gt;&lt;kdgrp&gt;2&lt;/kdgrp&gt;
  </code></pre>
  
  <p>Another try:</p>
  
  <pre><code>SELECT  C.value('/kunnr/text()','nvarchar(10)') as KUNNR,
          C.value('/kdgrp/text()','nvarchar(10)') as KDGRP
  from @xml.nodes('/customers/customer') AS T(C);
  </code></pre>
  
  <p>This resulted in the following error message:</p>
  
  <pre><code>XQuery [value()]: 'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
  </code></pre>