If you have ' in your varchar fields you may first change the entities
into characters such as '. I have no idea with xslt. DOM or Xquery will do.

create table t1(a varchar(10), b xml)
go
insert into t1 values(''', '')
go

update t1 set b = '<a>' + a + '</a>'
update t1 set a = b.value('data((/a)[1])', 'nvarchar(max)')
select a from t1

drop table t1

"Jethro" <Jethro_uk@h...> wrote in message
news:cjj5d1$p7a$1@h......
> Hi Jonathon
>
> this is the second of my queries you have helped with : I'm not worthy !
>
> > Don't worry about that. The transformation will do it for you. Drag and
> drop
> > your XML into IE. It will show the correct characters for example.
>
> My browser is still showing "&apos;" not ' .... looking at the resultant
> HTML I have :
>
> <P>This &amp;apos;Back to Back&amp;apos; Style House is in need of
complete
> modernisation throughout but does already benefit from part UPVC double
> glazing and a lounge over 15&amp;apos; in length. Ideal investment
> opportunity.</P>
>
> which was derived from the XML :
>
> <ShortDescription>This &amp;apos;Back to Back&amp;apos; Style House is in
> need of complete modernisation throughout but does already benefit from
part
> UPVC double glazing and a lounge over 15&amp;apos; in length. Ideal
> investment opportunity.</ShortDescription>
>
> so, with my thinking cap on, the SQL query I am running is returning the
> data already parsed this way. So further questions :
>
> 1) Can I stop the SQL doing this (I suspect not, as the whole *point* of
XML
> is to wrap up data).
> 2) Can I tell the XSL to change the "&amp;"  to &, which would then turn
the
> &amp;apos to &apos; which (given your previous comments) would render OK
in
> the browser.
>
> hmmmmmmmmm
>
>
>