Concatenating Values Within SQL XML field -

i have table in sql server 2012 has xml field. field contains arrays (the number of elements not constant) in following format:

<values>     <value>a</value>     <value>b</value>     <value>c</value>     <value>d</value> </values> 

and turn varchar this:

'a;b;c;d' 

i have tried:

select myfield.value('.', 'nvarchar(50)')   mytable 

which creates 'abcd' don't know how delimit (in real case not single character values).

try this

declare @mytable table (id int,myfield xml)  insert @mytable(id,myfield) values(1,'<values>     <value>a</value>     <value>b</value>     <value>c</value>     <value>d</value> </values>')   ;with xmltable (     select id, myfield.v.value('.', 'varchar(200)') myfield     @mytable      cross apply myfield.nodes('/values/value') myfield(v) )  select stuff((select ';' + myfield                    xmltable t2                    t2.id = t1.id                     xml path('')),1,1,'') myfield xmltable t1 group id