CODEWITHSUNDEEP
sql-serverxml
rach
rach

Reputation: 679

Return Multiple XML values in SQL using STUFF

I have following XML value in one of the column of table. 

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

Using following query

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

Output:

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

Desired output: 

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

Upvotes: 2

Views: 315

Answers (1)

Jason W
Jason W

Reputation: 13179

The issue is the [1] only gets the first match since you are using the value function. You can use the nodes function with value to concatenate the values with the technique you started.

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, '')

Here is a fully functional example if it helps since I'm assuming your tables and desired output in above code:

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

Upvotes: 1

Related Questions