title: Extracting data from XML in Sql Server column
body: <p>I've just discovered (by necessity) that TSQL has some functionality to extract data from columns that contain XML.  I've got a Sql Server column that contains XML data (though the column type isn't xml...it's varchar).  I'm supposed to extract a subset of the data from the column.  A simplified entry would look something like this:</p>
  
  <pre><code>declare @Table as table(id char(1), datacolumn xml)
  
  insert into @table
  select 'a', '&lt;root&gt;&lt;vnode v="5" /&gt;&lt;vnode v="8" /&gt;&lt;/root&gt;'
  
  insert into @table
  select 'b', '&lt;root&gt;&lt;vnode v="7" /&gt;&lt;/root&gt;'
  
  
  id   datacolumn
  ---  -----------------------------------------
  a    &lt;root&gt;&lt;vnode v="5" /&gt;&lt;node v="8" /&gt;&lt;/root&gt;
  b    &lt;root&gt;&lt;vnode v="7" /&gt;&lt;/root&gt;
  </code></pre>
  
  <p>So, I'd like to come up with a query that would return something along the lines of:</p>
  
  <pre><code>id   data
  ---  ----
  a    5
  a    8
  b    7
  </code></pre>
  
  <p>I've gotten to the point where I'm able to get some information...but I'm missing some concepts.</p>
  
  <pre><code>select id, T.c.value('node[1]', 'varchar(100)') mdata
  from @table
  cross apply datacolumn.nodes('/root') AS T(c)
  </code></pre>
  
  <p>Problems: </p>
  
  <ol>
  <li>This will get me the value inside the vnode (which is empty), but I need the value for the attribute marked "v". </li>
  <li>Also, because I've got [1] in there, I'm getting the first node...but I want all of them...but haven't groked the concept for that yet.</li>
  </ol>