Why does this work?
SELECT
XDocument
.value('(/Book/Chapter[@verse="allo"])[2]',
nvarchar(max)')
FROM XBiblos
WHERE Version = 666
But WHY does this not work? Where the only diff is that [2]
is replaced by [sql:variable("@i")]
Which results in error msg
'value()' requires a singleton (or empty sequence), found operand of type 'xdt:untypedAtomic *'
DECLARE @i int;
SET @i = 2;
SELECT
XDocument
.value('(/Book/Chapter[@verse="allo"])[sql:variable("@i")]',
nvarchar(max)')
FROM XBiblos
WHERE Version = 666
Doc structure example ...
<XDocument name="DraXQueLah">
<Book name="Worldymort">
<Chapter verse="Forgot">Forgot so loved the world</Chapter >
<Chapter verse="Icecream">That we were eternally creamed</Chapter >
<Chapter verse="blah">blah blah</Chapter >
<Chapter verse="blah">blah blah</Chapter >
<Chapter verse="blah">blah blah</Chapter >
<Chapter verse="blah blah">blah blah blah</Chapter >
</Book>
</XDocument>
Just add
[1]
to enforce a singleton. The engine knows, that[2]
(which is the position), will - for sure! - have only one (or none) result. But the engine is not able to predict, that your expression will result in one single result: