XQuery Node By Value Then Its Sibling-CodePudding

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

CodePudding user response：

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