Escaped U+2019 into XSLT stored in SQL as XML

Question

0.00/5 (No votes)

See more:

O.k., so I'm loading french translations into a DB, as XML ...
I can use '' for the simple apostrophies, I can't use '(because the SQL errors) and There is the ’ (this one looks like an apostrophe/or tick, but apparently comes out as a funky accented A (’) when I try to use it here natively..? it is U+2019) which is causing problems:

SQL

Declare @FRENCHxml xml
    set @FRENCHxml = '
  <xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:output method="html" />
  <xsl:template match="/">
    <html>
      <head>
        <title>
          some title
        </title>
      </head>
      <body>
      LOTS of stuff here including --- l'application(looks like)
      LOTS of stuff here including --- l’application
      </body>
    </html>
  </xsl:template>
</xsl:stylesheet>' -- end FRENCH


UPDATE [TemplateTable]
SET [Template] =@FRENCHxml WHERE [ID] = @Number

Has anyone seen this or can assist me?

incidentally CDATA won't work because this is an SQL exception, not an XML issue.. basically how do you get the "dangerous" characters to save into your XML haven't found a useable solution that involves the & sign inside the XML block

Mainly concerned with U+2019 character can't use the ’ because of the ampersand.. And I can't try \’ because that results in \’ in the XML node with no way to remove the slash!!!

And any other method I can think of will not work also...

ARGH! :doh: :mad:

please and thank you .. I'm going mad here....!!!

Apparently" i'm even experiencing problems trying to show the non-escaped characters here as well...hmmm Well If you want to know more about my offensive punctuation mark Look Here[^]

Posted 8-Aug-10 5:32am

ely_bob

Updated 8-Aug-10 5:39am

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

E.F. Nijboer · Answer 1 · 2010-08-08T23:50:00

Solution 1

The most simple way is a stored procedure because the problem would then automatically be solved. There are no "dangerous" characters then. You could also try to replace the ' by '' (double single-quotes).

Good luck!

Posted 8-Aug-10 23:50pm

E.F. Nijboer

Comments

ely_bob 9-Aug-10 8:16am

I'm trying to write the script to get the data into the DB, the SCRIPT will not allow U2019 (and also a few others) as existing inside xml, and i 'm not a native french speaker so I can't just change the translation... from U2019, to U0027....

E.F. Nijboer 9-Aug-10 9:23am

In SQL you can normally escape a single quote by simply replacing it with two single quote characters. I don't know if this works for the other characters too. I also do not fully understand why ' would give you any sql errors because this is in a quoted text so it would not be parsed. What would help the most is to ensure the encoding on both sides. For example UTF-8 and should also be added to the HTML to make it also clear for the browser when sending the html out, so adding:

It is somewhat hard to give you the exact solution because I do not exactly know what is going wrong and where. But read the following links good and I think you will be able to solve it yourself and also give you a good idea about it for future reference too.
Character encoding
http://www.joelonsoftware.com/articles/Unicode.html

disable-output-escaping
http://www.dpawson.co.uk/xsl/sect2/N2215.html

ely_bob · Answer 2 · 2010-08-09T07:19:00

Solution 2

I found the answer...
I had this held over from the Editor

SQL

set @sample = '<?xml version="1.0" encoding="utf-8"?>'

deleted:encoding="utf-8"
and it works fine...

SQL

set @sample = '<?xml version="1.0" ?>'

Thanks to everyone who weighted in.

Posted 9-Aug-10 7:19am

ely_bob

Comments

E.F. Nijboer 9-Aug-10 16:28pm

It might work but beware it isn't best practice to simply discard the encoding. Instead of deleting this single correct encoding definition it would be much more safe to add it to the documents where it is now somewhat implied.

ely_bob 9-Aug-10 16:31pm

Right..! thanks for reminding me of the implications...