0

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?

Ishpal
  • 103
  • 1
  • 8

1 Answers1

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.

ZombiePiranha80
  • 26
  • 5
  • if you read my post I mentioned that "This would help me escape the Invalid characters in the values, so they can then be serialized to XML properly." If the text is generated using the XML engine, the SQL server will remove the invalid characters from the data, so the element name is valid XML. Your approach only concatenates the strings, which does not help remove invalid characters, so wont work for me. – Ishpal Oct 15 '13 at 20:21
  • If you had INSERT #XMLTest ([Name]) VALUES (N'One¬d¦Uº'), it would generate invalid XML element. – Ishpal Oct 15 '13 at 20:30
  • If you want to additionally sanitize the content, as you state in your edit, you can process it through a function, such as found in this other answer from SO. http://stackoverflow.com/questions/1007697/how-to-strip-all-non-alphabetic-characters-from-string-in-sql-server – ZombiePiranha80 Oct 16 '13 at 02:56
  • I want to use the XML engine in the SQL server so I can have internationalization as well and just escape what is required. This has already been solved by the SQL Server, so dont want to compile a big list of invalid characters to escape manually. I was hoping I could reuse what SQL server already has implemented. – Ishpal Oct 16 '13 at 13:45