Do I need / text () when fetching an attribute value from an XML column?
I've seen examples doing it like this /text()
::
SELECT someColumn.value('(/someElement[@someAttribute="some value"]/text())[1]', 'nvarchar(100)')
FROM SomeTable
and I've seen some (including MSDN ) leaving a step /text()
:
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 point of view other than style? Such as performance or assumptions, ...
Update: I ran both queries in the same batch with the "actual execution plan" enabled, using a simple synthetic example with ~ 1 million lines (limited to 10 lines by PK), and the actual execution plans are different, but I'm not an expert on reading execution plans. He also consistently claims that version c /text()
makes up 54% of the total, and version sans 46%. This suggests that there is some difference.
source to share
There is a difference, but apparently you don't see it in your data. Here's 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];
source to share