SELECT only specific value from XML column in SQL

Question

Running the following TSQL

SELECT Name FROM Category

returns these results as the column in SQL is of xml data type.

1 <locale en-US="Abstract" />
2 <locale en-US="African" />
3 <locale en-US="Americana" />

I'd like to get a result set like:

1 Abstract
2 African
3 Americana

How can I do this?

Answer 1

You can try to use the .value function

SELECT Name.value('(/locale/@en-US)[1]','varchar(20)')
from  Category

sqffiddle