I am using the xmldom oracle package to generate an XML file
<?xml version="1.0" encoding="US-ASCII"?>
<!--r Students File for five College Libraries-->
<!-- Fri Aug 07 2015 13:57:50 EDT -->
The user is complaining because the character &apos
<z303-name>O'Barr, Lucia Margarita</z303-name>
<patron-record>
<z303>
<match-id-type>02</match-id-type>
<match-id>CSC990977933</match-id>
<record-action>A</record-action>
<z303-id>CSC990977933</z303-id>
<z303-name>O'Barr, Lucia Margarita</z303-name>
<dispatch-library>SCNLS</dispatch-library>
<z303-home-library>SCNLS</z303-home-library>
<z303-title-req-limit>0020</z303-title-req-limit>
<z303-plain-html>P</z303-plain-html>
<z303-export-consent>Y</z303-export-consent>
<z303-send-all-letters>Y</z303-send-all-letters>
<z303-profile-id>SC STUDENT</z303-profile-id>
</z303>
I am using a function in the cursor that to retrieve the name that is move to
<z303-name>O'Barr, Lucia Margarita</z303-name>
Here is the cursor of the function:
select
DISTINCT
utl_i18n.unescape_reference(
rtrim(xmlagg( XMLELEMENT(E,substr(spriden_last_name||', '||spriden_first_name||' '||spriden_mi, 1,80) ||':')
).extract('//text()'),':'
)
) as Fname
from spriden
where spriden_pidm = 90663545
and spriden_change_ind is null;
If I am selecting the name from the function
SELECT library_xml_files_pkg.f_GetFormatted_Name(SPRIDEN_PIDM)
FROM
SPRIDEN
WHERE
SPRIDEN_PIDM = 90663545
AND SPRIDEN_CHANGE_IND IS NULL
I got this result:
O'Barr, Lucia Margarita
I believe this is not a problem because the &apos is the symbol of the apostrophe or single-quote character (')
but the user is expecting to see the name like this:
<z303-name>O'Barr, Lucia Margarita;Barr, Lucia Margarita</z303-name>
Please advise and if I am wrong, how I can correct the code so the . O'Barr, Lucia Margarita;Barr, Lucia Margarita
will show like this:
<z303-name>O'Barr, Lucia Margarita;Barr, Lucia Margarita</z303-name>
INSTEAD of
<z303-name>O'Barr, Lucia Margarita</z303-name>
Thank you