Do I need /text() when selecting an attribute value from an XML column?

Question

I've seen examples doing it like this, with /text():

SELECT someColumn.value('(/someElement[@someAttribute="some value"]/text())[1]', 'nvarchar(100)')
FROM   SomeTable

and I've seen some (including MSDN) leaving off the /text() step:

SELECT someColumn.value('(/someElement[@someAttribute="some value"])[1]', 'nvarchar(100)')
-- No /text() here ------------------------------------------------^
FROM   SomeTable

Both forms work with my data. Does it matter from any perspective other than style? Such as performance, or assumptions, ...

Update: I've run both queries in the same batch with "actual execution plan" turned on, using a simple synthetic example with ~1 million rows (limiting to 10 rows by PK), and the actual execution plans are different, but I'm no expert at reading execution plans. It also consistently claims that the version with /text() is 54% of the total and the version without is 46%. This suggests there's a difference of some kind.

Answer 1

There is a difference, but apparently you don't encounter it in your data. Here is an example:

declare @x xml;

set @x = '
<Item Id="1">Just a text</Item>
<Item Id="2">
    <SubItem Id="247">Subitem text.</SubItem>
Text and some extra</Item>
';

select @x.value('/Item[@Id="1"][1]', 'varchar(max)') as [No text works],
    @x.value('/Item[@Id="2"][1]', 'varchar(max)') as [More complex case],
    @x.value('(/Item[@Id="2"]/text())[1]', 'varchar(max)') as [Text fixes it];