Take this for example:
DECLARE @xml XML = N'
<members>
<member>
<name>John</name>
<age>32</age>
</member>
<member>
<name>ELisa</name>
<age>28</age>
</member>
<member>
<name>Alex</name>
<age>26</age>
</member>
</members>';
SELECT @xml.query('/members/member/age[text() > 26 ]');
will ouput the following which is correct by design, but isn't informative enough
<age>32</age>
<age>28</age>
I am expecting:
<members>
<member>
<name>John</name>
<age>32</age>
</member>
<member>
<name>ELisa</name>
<age>28</age>
</member>
</members>
Or:
<members>
<member>
<name>John</name>
</member>
<member>
<name>ELisa</name>
</member>
</members>
Is it achievable in T-SQL/XQuery?
You get that result because you scrolled down to the <age> tag for the filter. But you can easily go back up, once you've made your selection, as follows:
SELECT @xml.query('/members/member/age[text() > 26 ]/..');
with result:
<member>
<name>John</name>
<age>32</age>
</member>
<member>
<name>ELisa</name>
<age>28</age>
</member>
Tested with SQL server online