title: SQL Server 2008 shredding XML data - cant get element text in rowset!
body: <p>Please consider this simple example.  I can't get the text of the state element 'red' or 'blue'  Please help!!!!! this is driving me batty</p>
  
  <pre><code>DECLARE @xml XML;
  
  SET @xml = '&lt;capitals&gt;
    &lt;state name="Alabama" 
      abbreviation="AL" 
      capital="Montgomery" &gt;red&lt;/state&gt;
    &lt;state name="Alaska" 
      abbreviation="AK" 
      capital="Juneau" &gt;blue&lt;/state&gt;
    &lt;state name="Arizona" 
      abbreviation="AZ" 
      capital="Phoenix" &gt;green&lt;/state&gt;
  &lt;/capitals&gt;';
  
  SELECT Node.value('@name', 'varchar(100)') AS Name,
    Node.value('@abbreviation', 'varchar(2)') AS Abbreviation,
    Node.value('@capital', 'varchar(100)') AS Capital
  FROM @xml.nodes('/capitals/state') TempXML (Node);
  </code></pre>