sql-serverxmlt-sqlxqueryxquery-sql

Keep ancestors/siblings structure in XQuery query() method


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?


Solution

  • 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