sqlsql-serverxmlxqueryxquery-sql

Xquery Get Consecutive Distinct Values


Trying to get consecutive distinct from the following XML.

<x>
    AB
</x>
<x>
    AB
</x>
<x>
    AB
</x>
<x>
    AC
</x>
<x>
    AB
</x>

Expected Result :

AB AC AB

Current Result :

AB AC

Code :

SELECT * ,STUFF(( TheParts.query(' for $x in distinct-values(/x/text()) return <x>{concat(" | ", $x)}</x> ').value('.', 'varchar(max)') ), 1, 1, '') AS Hierarchy 
FROM Table

The distinct-values get distinct from the whole set, but I would like to get distinct values only if there are consecutive.


Solution

  • We have a pure XQuery solution.

    SQL

    SELECT REPLACE(CAST('<x>AB</x>
        <x>AB</x>
        <x>AB</x>
        <x>AC</x>
        <x>AB</x>' as xml)
    .query('
        for $v in x
        let $n := x[. >> $v][1]
        return if ($v/text()[1] = $n/text()[1]) then ()
                else data($v)
    ').value('.', 'VARCHAR(MAX)'), SPACE(1), ' | ') AS Result;
    

    Output

    +--------------+
    |    Result    |
    +--------------+
    | AB | AC | AB |
    +--------------+