Hi,
Is there a way to join nodes using for loop. for example i have

<name>
<alt>test1</alt>
<alt>test2</alt>
<alt>test3</alt>
</name>

here i want the result as test1;test2;test3

is there a posibility to achieve this.

thanks

----------
dnandhak

Another trick is to use FOR XML:

SELECT STUFF( ( SELECT ';' + x.y.value( '.','VARCHAR(10)') FROM
@x.nodes('name/alt') x(y) FOR XML PATH('') ), 1, 1, '' )

According to the execution plans for this example, and Martins, it's 98% /
2% to me, proving how expensive FLOWR statements can be.

HTH
wBob

"Martin Honnen" wrote:

> dnandhak wrote:
>
> > Is there a way to join nodes using for loop. for example i have
> >
> > <name>
> > <alt>test1</alt>
> > <alt>test2</alt>
> > <alt>test3</alt>
> > </name>
> >
> > here i want the result as test1;test2;test3
>
> With XQuery 1.0 you can simply do
> string-join(/name/alt, ';')
>
> Unfortunately MS SQL Server does not provide the string-join function so
> it is getting ugly:
>
> DECLARE @x xml;
> SET @x = N'<name>
> <alt>test1</alt>
> <alt>test2</alt>
> <alt>test3</alt>
> </name>';
>
> SELECT @x.query('(for $a in name/alt[position() != last()] return
> concat($a, ";"), data(name/alt[position() = last()]))');
>
>
> --
>
> Martin Honnen --- MVP XML
> http://JavaScript.FAQTs.com/
>