tsql for XML format the values as elements

Question

I am trying to get the values in a column as XML elements. Is it possible to do this using For XML in sql server?

declare @XMLTest table( [Name] [nvarchar](50) NOT NULL )

INSERT @XMLTest ([Name]) VALUES (N'One¬d¦Uº')
INSERT @XMLTest ([Name]) VALUES (N'Two')
INSERT @XMLTest([Name]) VALUES (N'Three')

I would like to get the following on separate rows from the select query.

This would help me escape the Invalid characters in the values, so they can then be serialized to XML properly.

<One_x00AC_d_x00A6_U_x00BA_/>
<Two/>
<Three/>

Is it possible to get this return from the FOR XML query?

Answer 1

Start here:

declare @XMLTest table( [Name] [nvarchar](50) NOT NULL )

INSERT @XMLTest ([Name]) VALUES (N'One')
INSERT @XMLTest ([Name]) VALUES (N'Two')
INSERT @XMLTest([Name]) VALUES (N'Three')

select * from @xmltest for xml auto

Returns in XML format.

Or just some lame concatenation.

declare @XMLTest table( [Name] [nvarchar](50) NOT NULL )

INSERT @XMLTest ([Name]) VALUES (N'One')
INSERT @XMLTest ([Name]) VALUES (N'Two')
INSERT @XMLTest([Name]) VALUES (N'Three')

select '<' + Name + '/>' from @xmltest

It would help if your objective were more clear.

This query provides more guidance on how to custom format the XML stuff.

create table #xmltest (nID int primary key identity, [Name] [nvarchar](50) NOT NULL )
create table #xmldemo (Tag varchar, Parent Varchar, Other Varchar);
declare @i as int = 1;

INSERT #XMLTest ([Name]) VALUES (N'One')
INSERT #XMLTest ([Name]) VALUES (N'Two')
INSERT #XMLTest([Name]) VALUES (N'Three')

while (@i < 3)
begin
declare @tag as varchar(100) = '[Test!1!' + (select name from #XMLTest where nID = @i) + '!ELEMENT]';
declare @dsql as varchar(max) = 'select 1 as Tag, null as Parent, ''this'' as ' + @tag + ' from #xmltest for xml explicit';
exec(@dsql);
set @i += 1;
end

select * from #xmldemo;

drop table #xmldemo;
drop table #xmltest;

There's a little bit of extra stuff in there that's not pertinent, but it demonstrates something like what you're attempting.