Xquery Get Consecutive Distinct Values

user_9424434 0
Viewed 203

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.

xquery-sql xquery xml sql-server sql
3 Answers

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 |
+--------------+
user_19323110

… fiddle

select
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]
where not($v/text() = $n/text())
return (string($v/text()[1]))
')

fiddle

select stuff(
cast('<x>AB</x>
<x>AB</x>
<x>AB</x>
<x>AC</x>
<x>AB</x>' as xml).query('
for $v in x/text()
let $n := x[. >> $v][1]/text()
where not($v = $n)
return text{concat(" | ", $v)}
').value('.', 'nvarchar(max)'), 1, 3, '')


select stuff(
cast('<x>AB</x>
<x>AB</x>
<x>AB</x>
<x></x>
<x></x>
<x></x>
<x></x>
<x></x>
<x></x>
<x>AC</x>
<x>AB</x>' as xml).query('
for $v in x
let $n := x[. >> $v][1]
where not($v/text()= $n/text() or (empty($v/text()) and empty($n/text())))
return text{concat(" | ", $v/text()[1])}
').value('.', 'nvarchar(max)'), 1, 3, '')

a faster approach with the same idea as in the xquery: find the last value of a sequence. fiddle

select s.hierarchy
from t 
cross apply
(
select string_agg(isnull(x, ''), ' | ') within group(order by rn) as hierarchy
from
(
    select rn, x, lead(x) over(order by rn) as n
    from
    (
    select row_number() over(order by n.x) as rn, n.x.value('text()[1]', 'varchar(20)') as x
    from t.TheParts.nodes('x') as n(x)
    ) as d
) as l
where isnull(x, char(1)) <> isnull(n, char(1))
) as s
user_124036120

Please try the following solution. It is not XQuery-based.

Unfortunately, MS SQL Server's XQuery supports just a subset of the XQuery 1.0 standard.

Tumbling window and window variables functionality is a part of the XQuery 3.0/3.1 standard.

Check it out here: window clause

Please up-vote my suggestion: SQL Server vNext (post 2019) and NoSQL functionality

SQL

DECLARE @xml XML = 
N'<root>
    <x>AB</x>
    <x>AB</x>
    <x>AC</x>
    <x>AC</x>
    <x>AB</x>
</root>';

WITH rs AS
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS seq
        , c.value('text()[1]','CHAR(2)') AS R
    FROM @xml.nodes('/root/x') AS t(c)
), cte AS
(
 SELECT *, SUM(IIF(R <> ns, 1, 0)) OVER (ORDER BY seq) AS series
    FROM (
        SELECT series.*,
               LAG(R) OVER (ORDER BY seq) AS ns
        FROM rs AS series
    ) q
)
--SELECT * FROM cte ORDER BY seq;
SELECT MIN(R) AS R, series, MIN(cte.seq) AS seq_min, MAX(cte.seq) AS seq_max
FROM cte
GROUP BY cte.series
ORDER BY MAX(cte.seq) ASC;

Output

+----+--------+---------+---------+
| R  | series | seq_min | seq_max |
+----+--------+---------+---------+
| AB |      0 |       1 |       2 |
| AC |      1 |       3 |       4 |
| AB |      2 |       5 |       5 |
+----+--------+---------+---------+
user_19323110
Related