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.

CodePudding user response：

Thanks to @lptrt we have a pure XQuery solution.

All credit goes to @lptr. I just slightly modified its FLWOR expression return clause.

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

CodePudding user response：

… 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
let $n := x[. >> $v][1]
where not($v/text() = $n/text())
return text{concat(" | ", string($v/text()[1]))}
').value('.', 'nvarchar(max)'), 1, 3, '')

CodePudding user response：

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