Convert XML Data to Rows

Question

Convert XML Data to Rows

ganeshmuthuvelu

SSCrazy

Points: 2177
More actions
May 19, 2008 at 10:50 am

#186777

Hello,
I have a XML like this:
<ROWS>
<COL1>1</COL1>
<COL2>2</COL2>
<COL3>3</COL3>
</ROWS>
and I need to convert this data into a table as below:
C1 C2
----- ---------
COL1 1
COL2 2
COL3 3
How would I do this in SQL Server 2005?.
Thanks,
Ganesh

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

GSquared SSC Guru Points: 260824 More actions · Answer 1

Take a look at the Node function in SQL in Books Online. That ought to give you what you need.

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 2

The nodes function appears to return the data only as a single row with Col1, Col2 as column names as below.

For example,

declare @doc2 xml

SET @doc2 = '

<ROWS>

</ROWS>'

SELECT

tab.col.value('COL1[1]','VARCHAR(20)')AS Col1,

tab.col.value('COL2[1]','VARCHAR(20)')AS Col2,

tab.col.value('COL3[1]','VARCHAR(20)')AS Col3

FROM @doc2.nodes('//ROWS') tab(col)

returns

Col1,Col2,Col3

1,2,3

but that's not what I want.. I want like this:

C1 C2

----- ---------

COL1 1

COL2 2

COL3 3

ganeshmuthuvelu SSCrazy Points: 2177 More actions · Answer 3

Found it, here you go:

Select r.value('local-name(.)','varchar(20)') as Name,

r.value('(text())[1]','varchar(100)') as Value

From @doc2.nodes('//*') as x(r)

Thanks,

Ganesh

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 4

Here's one way....

drop table #t

create table #t(rid int identity(1,1), x xml)

insert #t(x)

select @doc2

select #t.rid, t.col.value('.','varchar(50)') c,

t.col.query('local-name(.)') j

from #t cross apply #t.x.nodes('//ROWS/*') t(col)

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?