CODEWITHSUNDEEP
sqlsql-servert-sql
Yargicx
Yargicx

Reputation: 1734

Get Value FOR XML PATH WITH CTE

I try to get data to @DataSrcCatIds variable. But SQL Server throws an error

The FOR XML clause is not allowed in a ASSIGNMENT statement

I've Googled and I've found a solution but the solution doesn't use CTE. I have to DataSrcCatIds it with common table expression.

Is there any solution with CTE?

Thanks

DECLARE @DataSrcCatIds AS NVARCHAR(MAX)

;WITH CatTree (CategoryId ) AS
(
    SELECT CategoryId FROM Categories
    WHERE SefLink='my-link'
    UNION ALL
    SELECT ct.CategoryId
    FROM Categories ct
    INNER JOIN CatTree AS parent ON parent.CategoryId = ct.ParentCatId
)
SELECT @DataSrcCatIds = DataSrcCatIds + ',' 
FROM Categories c
JOIN CatTree ct ON ct.CategoryId = c.CategoryId
FOR XML PATH ('')

SET @DataSrcCatIds = LEFT(@DataSrcCatIds, LEN(@DataSrcCatIds) - 1)

Upvotes: 0

Views: 1556

Answers (1)

Thom A
Thom A

Reputation: 95554

A bit of a stab in the dark, but I assume you're after:

DECLARE @DataSrcCatIds AS NVARCHAR(MAX);

WITH CatTree (CategoryId ) AS
(
    SELECT CategoryId FROM Categories
    WHERE SefLink='my-link'
    UNION ALL
    SELECT ct.CategoryId
        FROM Categories ct
    INNER JOIN CatTree as parent ON parent.CategoryId = ct.ParentCatId
)
SELECT @DataSrcCatIds = STUFF((SELECT ',' + DataSrcCatIds
                               FROM Categories c
                                    JOIN CatTree ct ON ct.CategoryId =c.CategoryId
                               FOR XML PATH ('')),1,1,'');

Upvotes: 5

Related Questions