Apostrophe in data used in sp_xml_preparedocument statement

SQL 2000
I have bad chars, go figure :), from user input off the web.  I have the trademark  sign as well a ' marks a plenty.

I am stumped in getting rid of the apostrophe in some text that may have 5 + marks.

This is the syntax that I'm using:
set @doc = (select replace( @doc, '''', '"'))
set @doc = (select replace( @doc, '&', '&'))


But here is the finished @doc that fails after the above code.

<specialInstruction><section =20
id=3D"customerSuppliedShippingAccount"></section><section =20
id=3D"customerCare">Please add the following to each recipient's =20
package:    1)  2 "Be Progressive" Holograms (8.5x11) must be in each =20
recipient=92s package.  2) 1   Smith &amp; Nephew Group CD per order =20
recipient</section><section id=3D"finance"></section><section =20
id=3D"production.general">Please add the following to each recipient's =20
package:    1)  2 "Be Progressive" Holograms (8.5x11) must be in each =20
recipient=92s package.  2) 1   Smith &amp; Nephew Group CD per order =20
recipient</section><section =20
id=3D"production.inventory"></section><section =20
id=3D"production.packaging">Please add the following to each recipient's =20
package:    1)  2 "Be Progressive" Holograms (8.5x11) must be in each =20
recipient=92s package.  2) 1   Smith &amp; Nephew Group CD per order =20
recipient</section><section =20
id=3D"production.prePress"></section><section =20
id=3D"production.printJob"></section><section =20
id=3D"production.shipping"></section></specialInstruction>

I could do multiple statements to hit each char one at a time.

set @loc = (SELECT CHARINDEX('''', @doc) )
if @loc  > 0
   begin
   set @doc = substring(@doc, 1, @loc -1) + substring(@doc ,  @loc +1, len(@doc))
end

But when I have 10 of them the user will need to use 12.  :)

What is a better way?

TIA
LVL 1
srussell705Asked:
Who is Participating?
 
dbbishopConnect With a Mentor Commented:
Haven't worked much with XML, but does:
set @doc = (select replace( @doc, '&apos;', "'"))

work?
0
 
dbbishopCommented:
You can also use &copy; (c) &reg; (r) and &tm; (tm)
0
 
twoboatsConnect With a Mentor Commented:
Thinking of some form of regular expression... along lines of

replace (@doc, '%[a-z0-9 ]''[a-z0-9 ], '"')

0
 
twoboatsCommented:
that's missing a close qutoe or so... ;)
0
 
srussell705Author Commented:
Here were some of the little fish I was trolling for:

set @doc = (select Replace(@doc,'ú','u'))
set @doc = (select Replace(@doc,'',''))
set @doc = (select Replace(@doc,'',''))
set @doc = (select Replace(@doc,'',''))
set @doc = (select Replace(@doc,'',' '))
set @doc = (select Replace(@doc,'½','1/2'))
set @doc = (select Replace(@doc,'¼','1/4'))

My trick for finding the offending chars was to print the @doc before the prepreXML statement.

I then pasted that text into an xml file and opened the XML in IE.  IE explains the position of the burp, so it took quite a few runs at this.  I didn't show all that I was looking for above.    
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.