' replacement for apostrophe within XML file for pdf output

Hello,
I want to add this string ''' to this snippet of sql code.

Original Code:
SUBSTR(TIR_CLINICAL_RATIONALE_1,1,LENGTH(TIR_CLINICAL_RATIONALE_1) - 1)||
SUBSTR(TIR_CLINICAL_RATIONALE_2,1,LENGTH(TIR_CLINICAL_RATIONALE_2) - 1)||
SUBSTR(TIR_CLINICAL_RATIONALE_3,1,LENGTH(TIR_CLINICAL_RATIONALE_3) - 1)||
SUBSTR(TIR_CLINICAL_RATIONALE_4,1,LENGTH(TIR_CLINICAL_RATIONALE_4) - 1)||
SUBSTR(TIR_CLINICAL_RATIONALE_5,1,LENGTH(TIR_CLINICAL_RATIONALE_5) - 1)

Modified Code:
replace(SUBSTR(TIR_CLINICAL_RATIONALE_1,1,LENGTH(TIR_CLINICAL_RATIONALE_1) - 1)
,'''',''') ||
replace(SUBSTR(TIR_CLINICAL_RATIONALE_2,1,LENGTH(TIR_CLINICAL_RATIONALE_2) - 1)
,'''',''')||
replace(SUBSTR(TIR_CLINICAL_RATIONALE_3,1,LENGTH(TIR_CLINICAL_RATIONALE_3) - 1)
,'''',''')||
replace(SUBSTR(TIR_CLINICAL_RATIONALE_4,1,LENGTH(TIR_CLINICAL_RATIONALE_4) - 1)
,'''',''')||
replace(SUBSTR(TIR_CLINICAL_RATIONALE_5,1,LENGTH(TIR_CLINICAL_RATIONALE_5) - 1)
,'''',''') var_notetext,

Is this the correct way?  Somehow, it seems to not work.  It is for adding a apostrophe to a
text comments field.  This is written to an xml file, which is translated to a pdf file letter output.

Example:
"We always meet the recipient's needs"
Needs to have:   We always meet the recipient&apos;s needs.</VAR_NOTETEXT>  
in order to escape the apostrophe.   The &amp;  is for '&'.  needs &apos; for ‘

Thanks
MachinegunnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
To make a string suitable for an XML doc, just convert the whole string.

dbms_xmlgen.convert does this.

Try:

dbms_xmlgen.convert(SUBSTR(TIR_CLINICAL_RATIONALE_1,1,LENGTH(TIR_CLINICAL_RATIONALE_1) - 1)||
SUBSTR(TIR_CLINICAL_RATIONALE_2,1,LENGTH(TIR_CLINICAL_RATIONALE_2) - 1)||
SUBSTR(TIR_CLINICAL_RATIONALE_3,1,LENGTH(TIR_CLINICAL_RATIONALE_3) - 1)||
SUBSTR(TIR_CLINICAL_RATIONALE_4,1,LENGTH(TIR_CLINICAL_RATIONALE_4) - 1)||
SUBSTR(TIR_CLINICAL_RATIONALE_5,1,LENGTH(TIR_CLINICAL_RATIONALE_5) - 1)
,2)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
slightwv (䄆 Netminder) Commented:
For a simple test to see what it does, just do:
select dbms_xmlgen.convert('a''<>b',2) from dual;
0
MachinegunnerAuthor Commented:
Thanks for the reply.
I tried this test, based on your simple test:
select dbms_xmlgen.convert('recepient's needs',2) from dual;

I don't think this is correct, can you please take a look at it on what I'm doing incorrect?

Thanks
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
From your last question, you need two single quotes to get one:

select dbms_xmlgen.convert('recepient''s needs',2) from dual;

That is two single quotes, not a double quote.
0
MachinegunnerAuthor Commented:
Sorry to open this question up again, but when you open the xml document with notepad or textpad, it shows up as a:  'recipient&amp;apos;s'

But... if you open it in an xml editor or internet explorer, it shows up as a:
'recipient&apos;s'  (shows up correctly)

Question - Why does it show up different within notepad / textpad and not correctly as in internet explorer?

Question - Is there another function or logic that is needed to add or change when using the dbms_xmlgen package, besides the convert function?

Thanks!
0
slightwv (䄆 Netminder) Commented:
>>it shows up as a:  'recipient&amp;apos;s'

This means the data is being double-encoded.

The single quote is first encoded to &quot;  Then the '&' is being encoded again as &amp;.

From what you posted, it looks like the data had already been encoded once and the call to CONVERT encoded it again.
0
MachinegunnerAuthor Commented:
So the CONVERT function should not be used then, as to not have it double-encoded.
I wonder why it didn't convert the first time, we are only using the convert function once and not twice, as you have it.
Thanks
0
slightwv (䄆 Netminder) Commented:
The convert function takes a non-encoded string and makes it XML compliant.

I cannot say if you should use it or not.  I don't know your data or system.

Based on the need of the question, some of the data is obviously not encoded.

My guess is some of your data may have already been encoded before.  This can and will cause a double encoding that you are seeing.

You will need to look at your raw data to see if you have a mix of encoded and unencoded data.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.