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

Question

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');

Solution 1

Well the simpler the better the faster.

select d=(select a.* for xml path('r'),type,elements absent)
from MyTable a

Solution 2

The easiest way is to use this:

SELECT (SELECT T.* FOR XML RAW)
FROM MyTable T

Or if you want more control over the XML structure you should use:

SELECT (SELECT T.* FROM (SELECT T.*) T FOR XML AUTO)
FROM MyTable T

Solution 3

EDIT

To produce an XML column for each row, you can use the below

-- this is a poor cousin of a proper sanitize function. Add to it whatever you need
-- to cater for your column names that will be invalid XML tag names
-- example below only removes spaces (xml node names cannot contain spaces)
create function dbo.sanitize(@colname sysname) returns sysname
as begin
return replace(@colname,' ', '')
end
GO

-- test table
create table [test table](i int, s varchar(max), [d t] datetime)
insert [test table] select 1, 'abc', getdate()
insert [test table] select 2, 'def', getutcdate()
insert [test table] select 3, 'g', getdate()+10
insert [test table] select 4, 'hij', getutcdate()+20
GO

-- the dynamic SQL to return each row as an single XML column
declare @tablename sysname set @tablename = 'test table';
declare @colsList nvarchar(max)
select @colsList = isnull(@colsList+',','') +
    QuoteName(column_name) + ' as ' +
    dbo.sanitize(column_name)
from INFORMATION_SCHEMA.COLUMNS 
where TABLE_NAME = @tablename;

select @colsList = '
    select (select ' + @colslist + ' for xml path (''cols''))
    from ' + quotename(@tablename)
--print @colslist
exec(@colslist);

As a single XML document for the entire table

Just use FOR XML, and specify a path of "cols"

declare @tbl table (col1 int, col2 int)
insert @tbl values (1,1)
insert @tbl values (2,2)
insert @tbl values (3,3)
insert @tbl values (4,4)

-- you can use just the part below here
select col1, col2
from @tbl
for xml path('cols')

Output

<cols>
  <col1>1</col1>
  <col2>1</col2>
</cols>
<cols>
  <col1>2</col1>
  <col2>2</col2>
</cols>
<cols>
  <col1>3</col1>
  <col2>3</col2>
</cols>
<cols>
  <col1>4</col1>
  <col2>4</col2>
</cols>

Note that for XML, formatting and whitespace have no inherent meaning. So either you want a VARCHAR result with multiple rows (resembling XML), or you want XML - pick one.

Comments

[Stephen Canon]

Softion, please stop trying to delete people's answers because they "totally miss the point". Instead, clarify what you're actually asking, either in your question or in a comment to their answer.

[Softlion]

I have clarified the question before deleting. I delete so no other user could be disturbed by answers which are not for this particular question.

[Softlion]

-1 for putting -1 for a bad reason. Where do you think you are. At school ? It is my question and i manage it as i want.

[Martin Smith]

The OP wants an XML document per row I think.

[Softlion]

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

[Pascal Cuoq]

@Softion Welcome to StackOverflow. Editing is not intended for removing answers that you think miss the point, and actually it does not remove the answer, just make it even less relevant than it previously was.

[Softlion]

Nice. If it could be hidden from my view it would be better than juste making it less relevant. I would have not deleted the answers if they could add some value. But they dont. They just deserve their writer.

[Softlion]

Nice shot. select result = (select myColumn for xml path) from MyTable; is a strange construction as the inner select does not have a from clause. It does not work with * in the select list. Is it really supported by sql server or is it a hack ? Well it works. Thanks !

[RichardTheKiwi]

@softion I see you inferred that it works with *. Great stuff