使用STUFF在SQL中返回多个XML值

Question

我在表的一列中有以下XML值。

<wizard id="CF510D2B-BF9C-485B-9D33-0056D1DDFDF2" step="4" sbm="1C766093-633D-E611-80C2-40F2E9DD0D2A"> 
    <reviews> 
    <crd id="AE7F8E74-643D-E611-80C2-40F2E9DD0D2A" /> 
    <rvw id="AF7F8E74-643D-E611-80C2-40F2E9DD0D2A" /> 
    <rvw id="B07F8E74-643D-E611-80C2-40F2E9DD0D2A" /> 
</reviews> 
</wizard> 

使用下面的查询

SELECT abc = STUFF((
     SELECT ',' +CAST(w.[state].value('(/wizard/reviews/rvw/@id)[1]', 'uniqueidentifier') AS varchar(max)) 
     FROM dbo.Wizard w where code='1C766093-633D-E611-80C2-40F2E9DD0D2A' 
     FOR XML PATH('') 
     ), 1, 1, '') 
FROM dbo.Wizard w 

输出：

AF7F8E74-643D-E611-80C2-40F2E9DD0D2A 

所需的输出：

AF7F8E74-643D-E611-80C2-40F2E9DD0D2A,B07F8E74-643D-E611-80C2-40F2E9DD0D2A 

Answer 1

问题是[1]只因为你使用获得的第一个比赛value功能。您可以使用nodes函数与value将这些值与您启动的技术连接起来。

SELECT abc = STUFF((
    SELECT ',' + CONVERT(VARCHAR(50), Review.value('@id', 'uniqueidentifier')) 
    FROM dbo.Wizard w 
     CROSS APPLY w.[State].nodes('/wizard/reviews/rvw') Reviews (Review) 
    WHERE Code = '1C766093-633D-E611-80C2-40F2E9DD0D2A' 
    FOR XML PATH('')), 1, 1, '') 

这里是一个全功能的例子，如果有帮助，因为我假定表格和期望的输出在上面的代码：

DECLARE @Wizard TABLE (Code UNIQUEIDENTIFIER, [State] XML) 
INSERT @Wizard VALUES ('1C766093-633D-E611-80C2-40F2E9DD0D2A', 
    '<wizard id="CF510D2B-BF9C-485B-9D33-0056D1DDFDF2" step="4" sbm="1C766093-633D-E611-80C2-40F2E9DD0D2A"> 
     <reviews> 
     <crd id="AE7F8E74-643D-E611-80C2-40F2E9DD0D2A" /> 
     <rvw id="AF7F8E74-643D-E611-80C2-40F2E9DD0D2A" /> 
     <rvw id="B07F8E74-643D-E611-80C2-40F2E9DD0D2A" /> 
    </reviews> 
    </wizard>') 

DECLARE @Text VARCHAR(MAX) = STUFF((
    SELECT ',' + CONVERT(VARCHAR(50), Review.value('@id', 'uniqueidentifier')) 
    FROM @Wizard w 
     CROSS APPLY w.[State].nodes('/wizard/reviews/rvw') Reviews (Review) 
    WHERE Code = '1C766093-633D-E611-80C2-40F2E9DD0D2A' 
    FOR XML PATH('')), 1, 1, '') 
SELECT @Text