CDATA - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

CDATA

Author

Topic

cykophysh
Starting Member

24 Posts

Posted - 2007-06-18 : 12:21:13

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

<A href="http://www.threenineconsulting.com" target="_blank" >My Website</a> || <a href="http://threenineconsulting.com/forum/blogs/cykophysh/default.aspx" targer="_blank">My Blog</a>

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-18 : 12:59:07

It sounds and look like you're getting a little too crazy with the illegal characters. When you run a FOR XML, it appears to me that all the escaping you need to do is done automatically by Sql Server :

declare @t table(val varchar(110))

insert into @t
select '<'

select * from @t for xml raw

results
-------------
<row val="<"/>

cykophysh
Starting Member

24 Posts

Posted - 2007-06-18 : 13:04:11

I see this, and I had noticed it before, but for some reason now and then the proc does fall over , saying Illegal character in XML, and doesn't return data

Kind Regards,
Gary

<A href="http://www.threenineconsulting.com" target="_blank" >My Website</a> || <a href="http://threenineconsulting.com/forum/blogs/cykophysh/default.aspx" targer="_blank">My Blog</a>

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-18 : 15:21:50

Not sure if y ou ran this or not, but the result was not "<", but rather the escaped code.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources