Here is an example, assuming the data is in a variable - however, you will notice that it shows the values of s and a twice - because there are two nodes with those values. Which of those did you want to pick up? Change the XPATH from '/v/*' to '/v/InterpreterPresent' if you want to pick up from InterpreterPresent node.DECLARE @x XML = '<v n="51500">
<InterpreterPresent s="e" a="sterlins" n="51500" />
<NameOfInterpreter s="e" a="sterlins" n="51500" />
</v>
<v n="52500">
<InterpreterPresent s="r" a="sterlins" n="52500" />
<NameOfInterpreter s="r" a="sterlins" n="52500" />
</v>';
SELECT
c.value('local-name(.)','varchar(32)') AS NodeName,
c.value('@s[1]','varchar(32)') AS S,
c.value('@a[1]','varchar(32)') AS A
FROM
@x.nodes('/v/*')T(c);
If the data is in a table, query is similar, except, change the from clause to....
FROM
tbl
CROSS APPLY colName.nodes('/v/*')T(c);
________________________________________
-- Yes, I am indeed a fictional character.
XML data from a column
Posted - 11/28/2012 : 15:52:18
