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 =

                           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:

Comments

# re: Concatentation with FOR XML and eleminating control/encoded characters

10 July 2006 16:34 by Matija Lah

Uh, just one tiny remark... it's called concatenation. :)

Yer just a tad too generous with T's.


ML

p.s. Keep this comment only if you decide to keep the misspelling.

# Aggregate concatenation in SQL Server 2005 &laquo; okmallegravenetia

# Database Programming: The String Concatenation XML Trick Revisited (Or, Adam Is Right, But We Can Fix It)

15 March 2008 09:22 by Ward Pond's SQL Server blog

A find shared by one friend leads to correspondence from another.. The redoubtable Adam Machanic left

# re: Concatenation with FOR XML and eleminating control/encoded characters

18 March 2008 13:47 by simonsabin

The issue with using the .value is that you invoke the XML Reader operators which aren't the best in terms of performance

# re: Concatenation with FOR XML and eleminating control/encoded characters

18 March 2008 14:01 by tonyrogerson

I know - they terrible, luckily I avoid XML in the database like a plague!

I guess another way would be CTE but I wonder what performance would be like.

# Abfrage | hilpers

20 January 2009 15:17 by Abfrage | hilpers

Pingback from  Abfrage | hilpers

# master table and child table andfield concatenation and loop records ? | keyongtech

Pingback from  master table and child table andfield concatenation and loop records ? | keyongtech