Get concatenated string of values from XML

Question

I have XML data from which I would like only the values concatenated together per key value. The below code comes close but does not work, please advise. What I tried below is to first "split" the values out of the XML, then reconcatenate them together, I am hoping there is a better way, else just a correction of my code will be appreciated.

Just a note: I struggle to wrap my head around how XML is implemented in SQL so the answer may be obvious

SELECT RoleId,
    /*This part does not work*/
    STUFF((SELECT ', ' + Condition
            FROM filters /*Invalid object name 'filters'.*/
            FOR XML PATH ('')), 1,1, '') vals
    /*This part does not work*/
FROM (
    /*This part works*/
    SELECT tbl.RoleId,
        p.value('@Condition', 'VARCHAR(8000)') AS Condition
    FROM (
        SELECT RoleId,
            r.RoleName,
            CAST(Data AS XML) Data
        FROM dbo.RoleFilters rf
        INNER JOIN dbo.Roles r
            ON r.Id = rf.RoleId
        ) tbl
    CROSS APPLY Data.nodes('/RoleFilters/Filters/ExpressionInfoGroup/Filters/Expression') t(p)
    ) filters

Answer 1

You cannot use the sub-select alias name to refer the data. You need to reuse the sub-select again but it can be achieved using CTE. CTE can be referred N number of times

;with filters as
(
 SELECT tbl.RoleId,
        p.value('@Condition', 'VARCHAR(8000)') AS Condition
    FROM (
        SELECT RoleId,
            r.RoleName,
            CAST(Data AS XML) Data
        FROM dbo.RoleFilters rf
        INNER JOIN dbo.Roles r
            ON r.Id = rf.RoleId
        ) tbl
    CROSS APPLY Data.nodes('/RoleFilters/Filters/ExpressionInfoGroup/Filters/Expression') t(p)
)
SELECT Distinct RoleId,
       STUFF((SELECT ', ' + Condition
            FROM filters 
            FOR XML PATH ('')), 1,1, '') vals
FROM filters