<div>
try this<br />

<pre><code id="code-174331">declare @myxml as varchar(max)
set @myxml = ''
select @myxml = @myxml + '&lt;id&gt;' + convert(varchar(25), id) + '&lt;/id&gt;'
from tbl_answers
where coalesce(IsCompleted, 0) = 0
</code></pre>
</div>


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


<div>
That works until I put the distinct clause in. &nbsp;For some reason as soon as I do that it only returns the first record....any ideas?
</div>


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?

<div>
<div class="content wysiwyg-content">
I am trying to build a select statement that I will pass to a tored proc as xml. &nbsp;basically, I want to wrap the ids that are returned in xml tags, like this:<br />
&lt;id&gt;123&lt;/id&gt;&lt;id&gt;234&lt;/id&gt;<br />
...the query statement I am trying to run looks like:<br />
select distinct @myxml = '&lt;id&gt;' + convert(varchar(25), id) + '&lt;/id&gt;'<br />
from tbl_answers <br />
where coalesce(IsCompleted, 0) = 0<br />
<br />
This only returns the first value it hits (i.e. - &lt;id&gt;123&lt;/id&gt;). &nbsp;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.<br />
<br />
Any thoughts?
</div>
</div>


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?

build string from select statement

Microsoft SQL Server 2005 is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning. It includes support for managing XML data and allows a database server to be exposed over web services using Tabular Data Stream (TDS) packets encapsulated within SOAP (protocol) requests.

Microsoft SQL Server 2005

Microsoft SQL Server is a suite of relational database management system (RDBMS) products providing multi-user database access functionality.SQL Server is available in multiple versions, typically identified by release year, and versions are subdivided into editions to distinguish between product functionality. Component services include integration (SSIS), reporting (SSRS), analysis (SSAS), data quality, master data, T-SQL and performance tuning.