Concatenating xml values when selecting by XQuery in T-SQL
you can use xuery if you want concatenated strings:
SELECT Tbl.Col.query('for $i in value return concat($i/text()[1], ";")').value('.', 'nvarchar(max)'), Tbl.Col.query('for $i in value return concat($i/@code, ";")').value('.', 'nvarchar(max)')FROM @xml.nodes('root/element/subelement') Tbl(Col);if you want your values into rows:
SELECT Tbl.Col.value('.', 'nvarchar(max)'), Tbl.Col.value('@code', 'nvarchar(max)')FROM @xml.nodes('root/element/subelement/value') Tbl(Col);