Return Multiple XML values in SQL using STUFF

Question

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

Jason W · Accepted Answer · 2017-04-10 13:20:03Z

1

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

answered Apr 10, 2017 at 13:20

Jason W

13.2k3 gold badges35 silver badges62 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

rach Over a year ago

Yes, [1] only gets the first one. I have always used [1] in the query. when I tried [2] it got second match.But I wasn't aware of how to get multiple values. Select query is exactly what I am looking for ! Thank you for your help. :)

Collectives™ on Stack Overflow

Return Multiple XML values in SQL using STUFF

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related