Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 862
  • Last Modified:

build string from select statement

I am trying to build a select statement that I will pass to a tored proc as xml.  basically, I want to wrap the ids that are returned in xml tags, like this:
<id>123</id><id>234</id>
...the query statement I am trying to run looks like:
select distinct @myxml = '<id>' + convert(varchar(25), id) + '</id>'
from tbl_answers
where coalesce(IsCompleted, 0) = 0

This only returns the first value it hits (i.e. - <id>123</id>).  I also tried inserting the results into a table variable and building the string by selecting one row at a time, that didnt work either, it returns a null.

Any thoughts?
0
PsychoDazey
Asked:
PsychoDazey
  • 3
  • 2
1 Solution
 
BinuthCommented:
try this
declare @myxml as varchar(max)
set @myxml = ''
select @myxml = @myxml + '<id>' + convert(varchar(25), id) + '</id>'
from tbl_answers
where coalesce(IsCompleted, 0) = 0

Open in new window

0
 
PsychoDazeyAuthor Commented:
That works until I put the distinct clause in.  For some reason as soon as I do that it only returns the first record....any ideas?
0
 
BinuthCommented:

declare @myxml as varchar(max)
set @myxml = ''
select 
	@myxml = @myxml + '<id>' + convert(varchar(25), id) + '</id>'
from 
	(
		select Distinct id 
		from tbl_answers 
		where coalesce(IsCompleted, 0) = 0
	) Dist

Open in new window

0
 
PsychoDazeyAuthor Commented:
Perfect, thanks!
0
 
PsychoDazeyAuthor Commented:
sorry, forgot to accept!
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now