SQL Server 2008 shredding XML data - cant get element text in rowset!

Question

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

DECLARE @xml XML;

SET @xml = '<capitals>
  <state name="Alabama" 
    abbreviation="AL" 
    capital="Montgomery" >red</state>
  <state name="Alaska" 
    abbreviation="AK" 
    capital="Juneau" >blue</state>
  <state name="Arizona" 
    abbreviation="AZ" 
    capital="Phoenix" >green</state>
</capitals>';

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);

Answer 1

You just have to use the . to get the inner text of the element. You can also use text()[1] There is a really good tutorial and examples on xPath in here.

DECLARE @xml XML; 

SET @xml = '<capitals> 
  <state name="Alabama"  
    abbreviation="AL"  
    capital="Montgomery" >red</state> 
  <state name="Alaska"  
    abbreviation="AK"  
    capital="Juneau" >blue</state> 
  <state name="Arizona"  
    abbreviation="AZ"  
    capital="Phoenix" >green</state> 
</capitals>'; 

SELECT Node.value('@name', 'varchar(100)') AS Name, 
  Node.value('@abbreviation', 'varchar(2)') AS Abbreviation, 
  Node.value('@capital', 'varchar(100)') AS Capital,
  Node.value('.', 'varchar(100)') AS Color 
FROM @xml.nodes('/capitals/state') TempXML (Node); 

Answer 2

I guess I am silly:

 Node.value('.','varchar(100)') AS PoliticalDisposition