Get the node following a selected node

October 6, 2022

I have the following (cut down) xml in a sql column

<Values>
  <Value>
    <N>Test</N>
    <V>FindMe</V>
  </Value>
  <Value>
    <N>Test</N>
    <V>NotMe</V>
  </Value>
  <Value>
    <N>Other Value</N>
    <V>NotMe</V>
  </Value>
</Values>

I am trying to get the value of V, following the first N with a value of Test

I have tried using following-sibling, but that apparently is not supported in whatever version of XPATH Sql Server 2008 uses.

I have arrived at the following query, based on Gunther’s answer here:

/Values/Value/V[. >> /Values/Value/N[. = 'Test'][1]][1]

Which when tried online, returns multiple rows, but when tried in the following SQL

SELECT
    A.x,
    A.x.query('/Values/Value/V[. >> /Values/Value/N[. = ''Test''][1]][1]')
FROM (SELECT CAST('<Values><Value><N>Test</N><V>FindMe</V></Value><Value><N>Other Value</N><V>NotMe</V></Value></Values>' AS XML) x) A

Has the following error

XQuery [A.x.query()]: '>>' requires a singleton (or empty sequence), found operand of type 'element(N,xdt:untyped) *'

I’ve tried the following variations on the query, but all have had the same error

/Values/Value/V[. >> /Values/Value/N[. = 'Test'][1]][1]
/Values/Value/V[. >> /Values/Value[N = 'Test'][1]][1]
/Values/Value/V[. >> /Values/Value[N = 'Test']/N[1]][1]