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 N开发者_StackOverflow社区ame,
Node.value('@abbreviation', 'varchar(2)') AS Abbreviation,
Node.value('@capital', 'varchar(100)') AS Capital
FROM @xml.nodes('/capitals/state') TempXML (Node);
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);
I guess I am silly:
Node.value('.','varchar(100)') AS PoliticalDisposition
精彩评论