Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

tsql: convert columns into a xml column, one xml per row

Example of what I have:

table: (col1 int, col2 int)
sample data (the real data will not be known at runtime):

1,1
2,2
3,3
4,4

Expected result: one column only (xml)
and 4 rows

row1: <cols><col1>1</col1><col2>1</col2></cols>
row2: <cols><col1>2</col1><col2>2</col2></cols>
row3: <cols><col1>3</col1><col2>3</col2></cols>
row4: <cols><col1>4</col1><col2>4</col2></cols>

Sorry guys the moderator don't want the "noise answers" to be deleted. I hope he can understand I'm helping these poor guys so no one can see they completely missed the point. I was protecting their reputations ...

Here is a way to achieve what i want. But it is not a good way because the xml is built by hand and not properly encoded.

declare @colsList nvarchar(max)
set @colsList = ''
select @colsList = @colsList + '+ ''<' + COLUMN_NAME + '>'' + Isnull(cast( [' + COLUMN_NAME + '] as nvarchar(max)),'''') + ''</' + COLUMN_NAME + '>'' ' 
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = 'SampleTable';

select @colsList = stuff(@colsList,1,1,'');
exec('select colsValues=cast(' + @colsList + ' as xml) from SampleTable');
like image 799
Softlion Avatar asked Nov 27 '22 10:11

Softlion


1 Answers

Well the simpler the better the faster.

select d=(select a.* for xml path('r'),type,elements absent)
from MyTable a
like image 84
Softlion Avatar answered Dec 18 '22 19:12

Softlion