title: T-SQL Convert XML field to multi-column dataset
body: <p>I want to 'split' XML field to the multiple columns dataset. XML looking like:</p>
  
  <pre><code>&lt;simple&gt;
      &lt;propertyid&gt;
          &lt;value&gt;p1&lt;/value&gt;
          &lt;value&gt;p2&lt;/value&gt;
          &lt;value&gt;p3&lt;/value&gt;
          &lt;value&gt;p4&lt;/value&gt;
      &lt;/propertyid&gt;
      &lt;complexid&gt;
          &lt;value&gt;c1&lt;/value&gt;
          &lt;value&gt;c2&lt;/value&gt;
          &lt;value&gt;c3&lt;/value&gt;
      &lt;/complexid&gt;
  &lt;/simple&gt;
  </code></pre>
  
  <p>I tried to do something like that (each time I working with XML in TSQL I feel butthurt, so my code is wrong):</p>
  
  <pre><code>;WITH source AS (
      SELECT CAST(@xmlstring AS XML) AS x
  )
  SELECT 
      items.item.query('.')
  FROM source
  CROSS APPLY x.nodes('/simple/*/value') AS items(item)
  </code></pre>
  
  <p>Expected dataset:</p>
  
  <pre><code>ColumnName      Value
  ------------------------
  propertyid      p1
  propertyid      p2
  propertyid      p3
  propertyid      p4
  complexid       c1
  complexid       c2
  complexid       c3
  </code></pre>
  
  <p>How can I get required result?
  Maybe you can recommend good resource with T-SQL + XML + XQuery explanations and samples?</p>