Help getting a column into Comma Separated data

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help getting a column into Comma Separated data

Author

Topic

yaf23
Starting Member

9 Posts

Posted - 2009-11-12 : 16:45:11

hey guys I have data in a column that looks like this:

<root><Speakers>200</Speakers><Speakers>300</Speakers><Speakers>400</Speakers></root>

So far I have a query like this and it gets me 200 (i.e. the first occurrence of <Speakers>)

Select (SUBSTRING(content_html, charindex('<Speakers>', content_html) + LEN('<Speakers>'), (charindex('</Speakers>', content_html) - charindex('<Speakers>', content_html) - LEN('<Speakers>')))) as SpeakerID
FROM content WHERE folder_id = 242

but I want the resulting data to be like 200,300,400.

is this possible and I don't have access to write a Stored proc.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-13 : 02:06:03

Like this


declare @string varchar(8000) ,@data varchar(8000)

select @string = '<root><Speakers>200</Speakers><Speakers>300</Speakers><Speakers>400</Speakers></root>'


select left(data,patindex('%[^0-9]%',data)-1) from 
(
select 
        substring(@string, n, charindex('>', @string + '>', n ) - n) as data
from 
( 
        select number as n from master..spt_values where type='p' 
) numbers 
where 
        substring( '~' + @string, n, 1 ) = '>' 
) as t
where data like '[0-9]%'

Madhivanan

Failing to plan is Planning to fail

Sachin.Nand

2937 Posts

Posted - 2009-11-13 : 06:23:26

If u need result in column wise & u are using SQL 2005 then use this one

declare @x as xml
set @x='<root><Speakers>200</Speakers><Speakers>300</Speakers><Speakers>400</Speakers></root>'
select @x
select x.i.value('.','varchar(10)') from @x.nodes('//root/Speakers')x(i)

PBUH

yaf23
Starting Member

9 Posts

Posted - 2009-11-13 : 09:49:11

Thanks this seems like it will be a quick way to do this thanks. Also madhivanan solutions also worked too.

quote:
Originally posted by Idera

If u need result in column wise & u are using SQL 2005 then use this one

declare @x as xml
set @x='<root><Speakers>200</Speakers><Speakers>300</Speakers><Speakers>400</Speakers></root>'
select @x
select x.i.value('.','varchar(10)') from @x.nodes('//root/Speakers')x(i)

PBUH

yaf23
Starting Member

9 Posts

Posted - 2009-11-13 : 11:35:45

Now I managed to get the results I want but what I get is the result in a row by row like I get

200
300
400

Which is technically fine but is there way to get them into a comma separated list in one row like 200,300,400

here is what I have now in the SQL:

DECLARE @sql nvarchar(4000)
SELECT @sql = content_html
FROM content
WHERE content_id = 244
declare @x as xml
set @x= @sql
select @x
select x.i.value('.','varchar(4000)') from @x.nodes('//root/Speakers')x(i)

quote:
Originally posted by Idera

If u need result in column wise & u are using SQL 2005 then use this one

declare @x as xml
set @x='<root><Speakers>200</Speakers><Speakers>300</Speakers><Speakers>400</Speakers></root>'
select @x
select x.i.value('.','varchar(10)') from @x.nodes('//root/Speakers')x(i)

PBUH

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-11-16 : 01:37:34

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=81254

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources