Sql Server 2008 Shredding XML

Question

Hi I have some XML like this...

<Questions>
  <Question1>A</Question1>
  <Question2>B</Question2>
  <Question3>C</Question3>
</Questions>

I would like to return A,B,C. I have seen some similar questions but those had the same repeating node name. Unfortunately at this stage I cant change it. I have played with the SQL Xpath syntax for AGES, with no luck. I can get the whole Questions Node, but Ideally I would like the Actual Data. Multiple returned rows would also be OK.

Any help would be greatly appreciated.

Update - Kirill's answer is very close, except that I have more than 1 record in the table and it is returning all records data within 1 row. If I could achieve a row/xml file that would be perfect !!! Either by outputing another field from that record e.g. rownum or outputting another piece of data from the file e.g. ProfileName..

thanks,

Adrian !

Answer 1

declare @x xml = N'<Questions>
  <Question1>A</Question1>
  <Question2>B</Question2>
  <Question3>C</Question3>
</Questions>
'

SELECT x.value('.','varchar(10)')
FROM @x.nodes('/Questions/*') x(x)

Output

----------
A
B
C

Answer 2

Continuing on Martin's solution and with some inspiration from this:

declare @x xml = N'<Questions>
  <Question1>A</Question1>
  <Question2>B</Question2>
  <Question3>C</Question3>
</Questions>
'

DECLARE @Questions VARCHAR(MAX)

SELECT @Questions = COALESCE(@Questions + ', ', '') + Question
FROM (
    SELECT x.value('.','varchar(10)') as Question
    FROM @x.nodes('/Questions/*') x(x)
) as y

SELECT @Questions

Output:

A, B, C