CODEWITHSUNDEEP
sqlxmlt-sqlxpathxquery
Adrian Turner
Adrian Turner

Reputation: 31

Sql Server 2008 Shredding XML

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 !

Upvotes: 3

Views: 1050

Answers (4)

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56162

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

Output:

A,B,C

Upvotes: 0

dillenmeister
dillenmeister

Reputation: 1647

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

Upvotes: 2

Martin Smith
Martin Smith

Reputation: 453212

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

Upvotes: 5

Kirill Polishchuk
Kirill Polishchuk

Reputation: 56162

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

Upvotes: 1

Related Questions