sql-server - 从 XML 中获取串联的值字符串

Question

我有 XML 数据，我只想从中将每个键值连接在一起的值。下面的代码接近但不起作用，请指教。我在下面尝试的是首先将值从 XML 中“拆分”出来，然后将它们重新连接在一起，我希望有更好的方法，否则只需更正我的代码即可。

请注意:我很难理解 XML 在 SQL 中是如何实现的，所以答案可能很明显

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

您不能使用sub-select 别名来引用数据。您需要再次重用 sub-select，但可以使用 CTE 来实现。 CTE 可以被引用N次

;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