CODEWITHSUNDEEP
sql-serverxmlt-sqlvariablesxmltype
Blessed Geek
Blessed Geek

Reputation: 21664

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

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>

Upvotes: 1

Views: 51

Answers (1)

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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

Upvotes: 1

Related Questions