CDATA Sections in For XML query
-
Hi Guys, I have a stored proc that is returning XML to the client, as listed below. The question I have is that the description field can contain some HTMLa and possibly some illegal XML characters i.e &,<,>, ' etc. I would like to wrap these sections in a CDATA field but am a little unsure how to do it. Oh yeah the XML is going to be transformed by XSLT on the client. Any tips would be greatly appreciated. Ideally what I would like to achieve is to get rid of calling my DBO.HTMLENcode function I have written to encode the illegal characters.
Select @Brief as briefdescription, Cast('' + replace(cast(DBO.HTMLENcode(@Desc) as varchar(max)) , char(13),'' ) + '' as xml) as descriptions , cast('' + replace(cast(DBO.HTMLENcode(Features) as varchar(max)), char(13)+char(10), '') + '' as xml) AS Features, cast(price as money) as price, bedrooms as bedroom, status_id, cast(replace(('' + replace(cast(DBO.HTMLENcode(rooms) as varchar(max)), char(13), '')+ ''),'','') as xml) as rooms, Size_metre_sq as sqm, COALESCE (ADDRESS1 + ', ', '') + COALESCE (ADDRESS2 + ', ', '') + COALESCE (ADDRESS3 + ', ', '') + COALESCE (ADDRESS4, '') AS Address ,directions , Saleterms.SALETERMS as saleterm ,Saleterms.price_label as PriceLabel, leaseterms, rating, Convert(varchar(11) ,Availability, 113) as availability from dbo.PROPERTY as Property inner join Saleterms on Property.Saleterms_ID = Saleterms.ID where Property.id = @id FOR XML PATH('Property'), ROOT('Detail')Kind Regards, Gary
My Website || My Blog || My Articles