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 !

score 5 · Answer 1 · answered Feb 03 '12 at 12:05

5

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

answered Feb 03 '12 at 12:05

Martin Smith

438,706
87
741
845

score 2 · Answer 2 · edited May 23 '17 at 11:48

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

score 1 · Answer 3 · answered Feb 03 '12 at 12:04

Use:

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

select @x.value('(/*/Question1)[1]', 'nvarchar(max)')
    , @x.value('(/*/Question2)[1]', 'nvarchar(max)')
    , @x.value('(/*/Question3)[1]', 'nvarchar(max)')

Output:

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

score 0 · Answer 4 · answered Feb 03 '12 at 12:31

0

select stuff(
(
    select ',' + x.value('.', 'varchar(10)') [text()]
    from @x.nodes('/*/*') x(x)
    for xml path(''))
, 1, 1, '')

Output:

A,B,C

answered Feb 03 '12 at 12:31

Kirill Polishchuk

54,804
11
122
125

This is great, except that I am using a table and this code returns ALL records data on 1 row. I need each XML files data on 1 row. – Adrian Turner Feb 03 '12 at 14:57
@AdrianTurner, Provide some sample. – Kirill Polishchuk Feb 03 '12 at 17:53

Sql Server 2008 Shredding XML

4 Answers4