sql server xml illegal character error

declare

@xmldata xml

declare @textdata nvarchar(max)

set @xmldata=(select convert(varchar,isnull(o.datestartorder,''),101) as datestartorder ,

o

.datestoporder ,

o

.datedc ,

orderdescription

=

rtrim(rtrim(ltrim(isnull(ph.namepharm,'') + ' ' ))

+ case when isnull(nd.namegeneric,'') = '' then ''

when isnull(nd.namegeneric,'') <> isnull(ph.namepharm,'')

--then ''

then ' (' + isnull(nd.namegeneric,'') + ')'

else ''

end ),

-- + ' ' +

isnull(nd.strength,'')

+ ' ' + isnull(nd.formdosage,'')

+ ' ' + isnull(o.instructionsdosing,'') as sig

from orders o

left outer join ordertype ot on o.idordertype = ot.idordertype

left outer join pharm ph on ph.idpharm = o.idpharm

left outer join ndc nd on o.idndc = nd.idndc

left outer join users u1 with (nolock) on isnull(o.iduserentered, 0) = u1.iduser

and o.idclinicnumber = u1.idclinicnumber

where o.idpatient = 1577411

and o.idclinicnumber = 60285

and o.idusernurse is not null

and o.datestartorder < getdate()

and o.datedc is null

and isnull(o.datestoporder, getdate() + 1) > getdate()

and isnull(o.idsignotrequired, 0) not in (3, 4)

and isnull(o.isrejected,0) = 0

and isnull(o.isdisputed,0) = 0

and isnull(o.isgeneratedinerror,0) = 0

and o.idordertype = 2

--order orderdescription

for xml path('results'),root('homemedication'))

select @xmldata

while running code getting error

xml parsing: line 1, character 121, illegal xml character

i want

to cast to avoid illegal characters. please specify where to use cast statement

you throw data temp table , clean there.  somethng this:

declare @xmldata xml declare @textdata nvarchar(max)   select convert(varchar,isnull(o.datestartorder,''),101) datestartorder , o.datestoporder , o.datedc , orderdescription  =  rtrim(rtrim(ltrim(isnull(ph.namepharm,'') + ' ' ))  + case when isnull(nd.namegeneric,'') = '' ''  when isnull(nd.namegeneric,'') <> isnull(ph.namepharm,'') --then ''  ' (' + isnull(nd.namegeneric,'') + ')'  else ''  end ),  -- + ' ' +  isnull(nd.strength,'')  + ' ' + isnull(nd.formdosage,'')  + ' ' + isnull(o.instructionsdosing,'') sig #tmp orders o 	left outer join ordertype ot on o.idordertype = ot.idordertype  	left outer join pharm ph on ph.idpharm = o.idpharm  	left outer join ndc nd on o.idndc = nd.idndc  	left outer join users u1 (nolock) on isnull(o.iduserentered, 0) = u1.iduser 		and o.idclinicnumber = u1.idclinicnumber  o.idpatient = 1577411  , o.idclinicnumber = 60285  , o.idusernurse not null  , o.datestartorder < getdate()  , o.datedc null  , isnull(o.datestoporder, getdate() + 1) > getdate()  , isnull(o.idsignotrequired, 0) not in (3, 4)  , isnull(o.isrejected,0) = 0  , isnull(o.isdisputed,0) = 0  , isnull(o.isgeneratedinerror,0) = 0  , o.idordertype = 2 order orderdescription    -- cleanup illegal characters update #tmp set orderdescription = replace( orderdescription, char(0x0b), '' )  set @xmldata  = 	( 	select *  	from #tmp 	for xml path('results'),root('homemedication') 	)  select @xmldata

SQL Server  >  SQL Server XML