SELECT SUBSTRING(@x.query(' for $t in (/Type) return [Type]{concat('','',$t/@Value)}[/Type] ').query('data(Type)').value('.','varchar(50)'),2,50) AS Val
/* Val -------------------------------------------------- White Fox ,Brown Fox */
Note: Blogger does not allow me to post XML fragments in comments and hence I replaced XML tags with []
Posts
What if the XML attribute value has spaces on its own?
ReplyDeleteFor example, Value="brown fox"
The REPLACE() function technique won't work in such cases.
Regards,
Yitzhak
Yes, in such a case, you might need a little more complicated XQuery.
ReplyDeleteDECLARE @x XML
SELECT @x = '
[Type Value="White Fox"/]
[Type Value="Brown Fox"/]'
SELECT SUBSTRING(@x.query('
for $t in (/Type)
return
[Type]{concat('','',$t/@Value)}[/Type]
').query('data(Type)').value('.','varchar(50)'),2,50) AS Val
/*
Val
--------------------------------------------------
White Fox ,Brown Fox
*/
Note: Blogger does not allow me to post XML fragments in comments and hence I replaced XML tags with []