SQL Server XML field - using TSQL variable as XML node sequence indicator

64 views Asked by At

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>
1

There are 1 answers

0
Shnugo On BEST ANSWER

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:

DECLARE @i int;
SET @i = 2;

SELECT
  XDocument
    .value('(/Book/Chapter[@verse="allo"])[sql:variable("@i")][1]', --<--Here
      nvarchar(max)')
FROM XBiblos
WHERE Version = 666