Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

build string from select statement

Posted on 2008-10-18
5
Medium Priority
?
858 Views
Last Modified: 2012-06-27
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
Comment
Question by:PsychoDazey
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 14

Expert Comment

by:Binuth
ID: 22751127
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
 
LVL 6

Author Comment

by:PsychoDazey
ID: 22752642
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
 
LVL 14

Accepted Solution

by:
Binuth earned 2000 total points
ID: 22755043

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
 
LVL 6

Author Comment

by:PsychoDazey
ID: 22757962
Perfect, thanks!
0
 
LVL 6

Author Closing Comment

by:PsychoDazey
ID: 31507523
sorry, forgot to accept!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question