[SOLVED] XQuery Node By Value Then Its Sibling

XQuery Node By Value Then Its Sibling

<a>
    <b>111</b>
    <c>AAA</c>
    <b>222</b>
    <c>BBB</c>
    <b>333</b>
    <c>CCC</c>
</a>

The above value is found in the an XML typed column in SQL Server.

I want to find the value for node located after node with the value of "111". Can this be done using XQuery?

So far I have:

SELECT X.Y.value('('b[.="111"])[1]', 'varchar(10)') AS 'MyColumn'
FROM DBTable
CROSS APPLY DocXml.nodes('/a') AS X(Y);

This gets me the first node but I haven't been able to get the sibling.

Solution

Unfortunately SQL Server does not support sibling axes, which would have made this simpler.

Instead, you need to do the following

Save the b node into a variable using let
Take all nodes of the root by using *, filter by checking each one to see if it follows b, return the first one.
Note that you should use text() to get the inner text of a node, rather than relying on implicit conversion.

SELECT
  x1.a.value('let $b := b[text() = "111"][1] return (*[. >> $b]/text())[1]','varchar(100)')
FROM DBTable t
CROSS APPLY t.DocXml.nodes('/a') x1(a);

db<>fiddle