September 29, 2010 at 2:35 pm
Sorry for the not very descriptive title, but I have never work with XML on SQL.
Say I have the following XML stored in XML column in SQL 2005
<Answers>
<AnswerSet>
<Answer questionId="q1">4</Answer>
<Answer questionId="q2" />
<Answer questionId="q3">3</Answer>
</AnswerSet>
</Answers>
How to write a query that will return this:
ID q1 q2 q3
1 '4' '' '3'
2 '3' '1' '4'
I supposed I can do something like:
SELECT
ID,
answerXML.value('(/Answers/AnswerSet/Answer)[1]', 'varchar(50)') AS a1,
answerXML.value('(/Answers/AnswerSet/Answer)[2]', 'varchar(50)') AS a2,
answerXML.value('(/Answers/AnswerSet/Answer)[3]', 'varchar(50)') AS a3
FROM
answerfiles
However, I am wondering if there is a way to write a generic query that do kind of a "for loop" to loop through all Answer nodes without hard coding the number of them like I did above.
October 4, 2010 at 3:45 pm
Try using the pivot operator. You will have a fix number of columns.
declare @xml xml;
set @xml = '<Answers>
<AnswerSet>
<Answer questionId="q1">4</Answer>
<Answer questionId="q2" />
<Answer questionId="q3">3</Answer>
</AnswerSet>
</Answers>'
declare @t table (num int);
declare @count int;
declare @lastCol int;
set @count = 1;
set @lastCol = 4;
while (@count <= @lastCol)
begin
insert into @t values (@count)
set @count = @count + 1
end
select [1] as q1, [2] as q2, [3] as q4
from
(select
t.*, x.col.value('(text())[1]', 'varchar(10)') as score from @t t
cross apply @xml.nodes ('/Answers/AnswerSet/Answer[fn:position() = sql:column("num")]') x(col)
) src
pivot (
max(score) for num in ([1], [2], [3])
) as pvt
Russel Loski, MCSE Business Intelligence, Data Platform
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply