How to query XML column?

Question

Hi I am new to XML Datatype. Can someone help me to pick "Yes I am right" from the following XML. Tried many existing sources but of no use.

<Names>
  <Name Id="1" Code="NM" Default="true">Yes I am right</Name>
</Names>

select column.value('(/languages/language/@code)[1]','varchar')
from table t with(nolock)

I tried the above code after searching many sources but I am getting an error

Msg 9341, Level 16, State 1, Line 1
XQuery table.column.value()]: Syntax error near '[', expected a step expression.

pln · Accepted Answer · 2014-09-18 12:00:59Z

1

The path for your example would be /Names/Name and the value methods requires that you specify that it is a single value being returned so the complete query would be

select column.value('(/Names/Name)[1]','varchar(max)') from t

Notice the varchar(max) since otherwise you would just return a single character by specifying varchar without a size.

answered Sep 18, 2014 at 12:00

pln

1,20810 silver badges13 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Azar Over a year ago

Thank you very much for your handy help.

Collectives™ on Stack Overflow

How to query XML column?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related