06 July 2006 07:37
tonyrogerson
Concatenation with FOR XML and eleminating control/encoded characters
This follows on from a problem I got in my Camel Case routine entry and also raised by Adam Machanic in the private MVP groups.
SELECT
*
FROM
(
SELECT x AS [data()]
FROM
(
SELECT 'something'
UNION ALL
SELECT 'something else'
UNION ALL
SELECT 'something & something'
) y (x)
FOR XML PATH('')
) z (final)
The problem with this is that it encodes the & as & which is no good to us.
A way round this problem is to use XQuery to get the value from the XML type and convert it into varchar(max).
select
(
SELECT mydata
FROM (
SELECT x AS [data()]
FROM
(
SELECT 'something'
UNION ALL
SELECT 'something else'
UNION ALL
SELECT 'something & something'
) AS y (x)
FOR XML PATH(''), TYPE
) AS d ( mydata ) FOR XML RAW, TYPE ).value( '/row[1]/mydata[1]', 'varchar(max)' )
AS concat
And my 'fixed' camel case routine is as follows :-
set
nocount on
declare
@seq table (
seq int not null primary key
)
declare
@i int
set
@i = 1
while
@i <= 50
begin
insert @seq values( @i )
set @i = @i + 1
end
declare
@names table (
word varchar(50) not null
)
declare
@breaks table (
break_on_character char(1) not null
)
declare
@exclude table (
subtext varchar(20) not null
)
insert
@names ( word ) values( 'colin leversuch-roberts being a pain lol' )
insert @names ( word ) values( 'jim von trapp' )
insert @breaks ( break_on_character ) values( ' ' )
insert @breaks ( break_on_character ) values( '-' )
insert
@exclude ( subtext ) values( ' von' )
select
CamelCase =
(
select Camel
from ( select case when seq = 1
or ( substring( n.word, seq-1, 1 ) IN ( select break_on_character from @breaks )
and not exists ( select *
from @exclude e
where subtext = substring( n.word, seq-1, len( subtext ) )
) )
then upper( substring( n.word, seq, 1 ) )
else lower( substring( n.word, seq, 1 ) )
end as [text()]
from @seq
where seq <= len( n.word )
order by seq
for xml path( '' ), type ) AS c ( Camel )
for xml raw, type ).value( '/row[1]/Camel[1]', 'varchar(max)' )
from
@names n
Filed under: SQL Server