Cdata Sections

• Gary Woodfine-408359

  SSC Veteran

  Points: 225

  More actions

  June 18, 2007 at 10:05 am

  #177066

  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')

  ---

  Kindest Regards,

  Gary Woodfine
  threenineconsulting.com

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  June 18, 2007 at 10:25 am

  #713603

  You have to use XML EXPLICIT and specify CDATA for each column you want.

  SELECT

  1AS TAG,

  NULLAS parent,

  'Some <XML'AS [X!1!SourceID!CDATA]

  FROM

  DataSource AS X

  FOR XML EXPLICIT

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

• Gary Woodfine-408359

  SSC Veteran

  Points: 225

  More actions

  June 18, 2007 at 10:35 am

  #713605

  I don't understand?

  do you have a reference I can look at?

  ---

  Kindest Regards,

  Gary Woodfine
  threenineconsulting.com

• Crispin Proctor

  SSCoach

  Points: 18161

  More actions

  June 18, 2007 at 1:47 pm

  #713642

  Only FOR XML EXPLICIT supports CDATA. You'll have to look in BOL for FOR XML EXPLICIT for some examples.

  For the query you have, it is nothing major to convert. Let me know if you don't come right...

   

   

   

  Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!