Skip to Main Content
Forums

DevOps, CI/CD and Automation

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

xml tags are not showing properly using xmldom

439571May 11 2009 — edited May 12 2009
FUNCTION gettitlelob (
pi_holdings_type IN CHAR,
pi_client_id IN scholar_status.clnt_id%TYPE,
pi_logfilename IN VARCHAR2,
pi_exception_filename IN VARCHAR2,
po_holdings_file OUT CLOB,
po_message_grp_id OUT NUMBER
)
RETURN NUMBER
IS
lv_func_name VARCHAR2 (50)
:= 'TLSKGSTL.gettitlelob';

TYPE gs_journals IS RECORD (
institution organizations.ORG_NAME%Type ,
contact varchar2(500),
keywords organizations.ORG_Qualifier%Type,
institution_links organizations.ORG_NAME_CS%Type,
pq_pmid pq_pmid.pq_pmid%TYPE,
premium_access EXT.premium_access%TYPE
);

rec_journals gs_journals;
lv_msg_cur NUMBER;
lv_msg_err NUMBER;


CURSOR cur_clnt_journals (v_clnt_id IN NUMBER)
IS
select distinct
org.org_name institution,
per_first_name || ' ' || per_middle_initial || ' ' ||per_last_name
|| ' < ' || ea.ea_description ||'>'|| ' ' contact ,
org.ORG_QUALIFIER keywords,
org.ORG_NAME_CS institution_links,
PQ_PMID ,
ge.premium_access premium_access
from clients c ,
local_administrators lc ,
people p ,
electronic_addresses ea ,
organizations org ,
ext ge ,
pq_pmid gpp
where c.clnt_id = 11920 v_clnt_id 68919 -- 10442
and c.lad_id = lc.lad_id
and ea.per_id = lc.per_id
and p.per_id = lc.per_id
and org.org_id = c.org_id
and ge.clnt_id = c.clnt_id
and gpp.pq_pmid = ge.pmid
and ge.status = 0
and INSTR (ge.format_avail, 'Y', 3, 1) > 2 ;

CURSOR cur_ip_add (v_clnt_id IN NUMBER)
IS
select ausr_id , AIA_IP_ADDRESS_START , AIA_IP_ADDRESS_END
from authorized_ip_addresses
where Ausr_id in (
select Ausr_id
from adds_users
where clnt_id = 11920 v_clnt_id 11920
and ausr_hold_status = 'N' );

lv_clnt_id NUMBER (9);
lv_exception_flag NUMBER;
lv_message_group_id VARCHAR2 (100);
log_fl_handle UTL_FILE.file_type;
lv_exception_filename VARCHAR2 (100);
lv_message_print VARCHAR2 (500);
lv_err_msg VARCHAR2 (500);
cur_ip_rec cur_ip_add%rowtype;
--------------------------------------------------------------------------------
doc xmldom.domdocument;
main_node xmldom.domnode;
root_node xmldom.domnode;
user_node xmldom.domnode;
item_node xmldom.domnode;
item_node_query xmldom.domnode;
item_node_subobj xmldom.domnode;
item_node_obj xmldom.domnode;
root_elmt xmldom.domelement;
item_elmt xmldom.domelement;
item_text xmldom.domtext;
c CLOB := ' ';

lv_pq_pmid NUMBER;


BEGIN
po_message_grp_id := 10;


dbms_output.put_line( 'pi_holdings_type'||pi_holdings_type );


dbms_output.put_line( 'Client_id Holdings_file' ||po_holdings_file );

lv_clnt_id := 11920 ;

OPEN cur_clnt_journals (pi_client_id);

LOOP
po_message_grp_id := 15;

FETCH cur_clnt_journals
INTO rec_journals;
EXIT WHEN cur_clnt_journals%NOTFOUND;

po_message_grp_id := 180;
doc := xmldom.newdomdocument;
main_node := xmldom.makenode (doc);
root_elmt := xmldom.createelement (doc, 'Institution');
root_node :=
xmldom.appendchild (main_node, xmldom.makenode (root_elmt));
--------------------------------------------------------------------------------
item_elmt := xmldom.createelement (doc, 'Institutional_links');
xmldom.setAttribute(item_elmt, 'id =', rec_journals.institution_links);
user_node :=
xmldom.appendchild (root_node, xmldom.makenode (item_elmt));
--------------------------------------------------------------------------------
item_elmt := xmldom.createelement (doc, 'Institution');
item_node :=
xmldom.appendchild (user_node, xmldom.makenode (item_elmt));
item_text := xmldom.createtextnode (doc, rec_journals.Institution);
item_node :=
xmldom.appendchild (item_node, xmldom.makenode (item_text));
--------------------------------------------------------------------------------
item_elmt := xmldom.createelement (doc, 'keyword');
item_node :=
xmldom.appendchild (user_node, xmldom.makenode (item_elmt));
item_text := xmldom.createtextnode (doc, rec_journals.keywords);
item_node :=
xmldom.appendchild (item_node, xmldom.makenode (item_text));
--------------------------------------------------------------------------------
item_elmt := xmldom.createelement (doc, 'Contact');
item_node :=
xmldom.appendchild (user_node, xmldom.makenode (item_elmt));
item_text := xmldom.createtextnode (doc, rec_journals.contact);

item_node :=
xmldom.appendchild (item_node, xmldom.makenode (item_text));
--------------------------------------------------------------------------------
item_elmt := xmldom.createelement (doc, 'Electronic_link_label');
item_node :=
xmldom.appendchild (user_node, xmldom.makenode (item_elmt));
item_text := xmldom.createtextnode (doc, 'BBC eText');
item_node :=
xmldom.appendchild (item_node, xmldom.makenode (item_text));
--------------------------------------------------------------------------------

item_elmt := xmldom.createelement (doc, 'Openurl_base');
item_node :=
xmldom.appendchild (user_node, xmldom.makenode (item_elmt));
item_text := xmldom.createtextnode (doc, ' http://bbc.edu');
item_node :=
xmldom.appendchild (item_node, xmldom.makenode (item_text));
--------------------------------------------------------------------------------

item_elmt := xmldom.createelement (doc, 'Openurl_Option');
item_node :=
xmldom.appendchild (user_node, xmldom.makenode (item_elmt));
item_text := xmldom.createtextnode (doc, ' http://bbc.edu');
item_node :=
xmldom.appendchild (item_node, xmldom.makenode (item_text));
--------------------------------------------------------------------------------


FOR cur_ip_rec IN cur_ip_add(lv_clnt_id )
LOOP
dbms_output.put_line('ip_address'||cur_ip_rec.AIA_IP_ADDRESS_START);
item_elmt := xmldom.createelement (doc, 'Patron_ip_range');
item_node_query :=
xmldom.appendchild (item_node, xmldom.makenode (item_elmt));
item_text := xmldom.createtextnode (doc, cur_ip_rec.AIA_IP_ADDRESS_START);
item_node_query :=
xmldom.appendchild (item_node_query,
xmldom.makenode (item_text)
);
END LOOP;



--------------------------------------------------------------------------------

item_elmt := xmldom.createelement (doc, 'Patron_ip_required');
item_node :=
xmldom.appendchild (user_node, xmldom.makenode (item_elmt));
item_text := xmldom.createtextnode (doc, 'no');
item_node :=
xmldom.appendchild (item_node, xmldom.makenode (item_text));
--------------------------------------------------------------------------------
item_elmt := xmldom.createelement (doc, 'Electronic_Holdings');
item_node_obj :=
xmldom.appendchild (user_node, xmldom.makenode (item_elmt));
item_elmt := xmldom.createelement (doc, 'Electronic_Holdings');
user_node :=
xmldom.appendchild (item_node_subobj,
xmldom.makenode (item_elmt)
);
-------------------------------------------------------------------------------
item_elmt := xmldom.createelement (doc, 'master_holdings_url');
item_node :=
xmldom.appendchild (item_node_obj, xmldom.makenode (item_elmt));
item_text :=
xmldom.createtextnode
(doc,'http://lib.bbc.ac.it/');
item_node :=
xmldom.appendchild (item_node, xmldom.makenode (item_text));

--------------------------------------------------------------------------------
item_elmt := xmldom.createelement (doc, 'holdings_id');
item_node :=
xmldom.appendchild (item_node_obj, xmldom.makenode (item_elmt));
item_text := xmldom.createtextnode (doc, rec_journals.pq_pmid);
item_node :=
xmldom.appendchild (item_node, xmldom.makenode (item_text));
--------------------------------------------------------------------------------

xmldom.writetoclob (doc, c);

SELECT REPLACE (c, '&amp;', '&')
INTO c
FROM DUAL;

SELECT REPLACE (c, '&lt;', '<')
INTO c
FROM DUAL;

c :=
'<?xml version="1.0"?>
<!DOCTYPE Institutional_holdings ( view Source for full doctype...)>
'
|| c;


END LOOP; ---- CUR_JOURNALS

po_message_grp_id := 170;

CLOSE cur_clnt_journals;
po_message_grp_id := 185;
po_holdings_file := c;
RETURN 0;
EXCEPTION
WHEN invalid_dblg_ret_code
THEN
RETURN (s_ret_val);
WHEN OTHERS
THEN
/* DBMS_OUTPUT.put_line ( 'lv_exception_flag '
|| lv_exception_flag
|| ' lv_comp_stdt.count '
|| lv_comp_stdt.COUNT
|| ' lv_comp_enddt.count '
|| lv_comp_stdt.COUNT
|| ' lv_jrid '
|| lv_jrid
|| ' po_message_grp_id '
|| po_message_grp_id
); */
RETURN SQLCODE;
END gettitlelob;

This function is creating oputput xml like this below

<?xml version="1.0"?>
<!DOCTYPE Institutional_holdings ( view Source for full doctype...)>
<Institution>
<Institutional_links id =="(UMI)Frank Pizzo">
<Institution>(UMI)Frank Pizzo</Institution>
<keyword></keyword>
<Contact>Frank P Pizzo < frank.pizzo@il.proquest.com&gt; </Contact>
<Electronic_link_label>BBC eText</Electronic_link_label>
<Openurl_base>  http://bbc.edu</Openurl_base>
<Openurl_Option> http://bbc.edu</Openurl_Option>
<Patron_ip_required>no</Patron_ip_required>
<Electronic_Holdings>
<master_holdings_url>http://lib.bbc.ac.it/</master_holdings_url>
<holdings_id>17150</holdings_id>
</Electronic_Holdings>
</Institutional_links>
</Institution>
ronic_Holdings>
</Institutional_links>
</Institution>

But I need output like this below

<?xml version="1.0"?>
<!DOCTYPE Institutional_holdings ( view Source for full doctype...)>
<Institution>
<Institutional_links id =="(UMI)Frank Pizzo">
<Institution>(UMI)Frank Pizzo</Institution>
<keyword></keyword>
<Contact>Frank P Pizzo < frank.pizzo@il.proquest.com&gt; </Contact>
<Electronic_link_label>BBC eText</Electronic_link_label>
<Openurl_base>  http://bbc.edu</Openurl_base>
<Openurl_Option> http://bbc.edu</Openurl_Option>
<Patron_ip_range >66.92.76.32 </Patron_ip_range>
<Patron_ip_range >66.92.76.32 </Patron_ip_range>
<Patron_ip_required>no</Patron_ip_required>
<Electronic_Holdings>
<master_holdings_url>http://lib.bbc.ac.it/</master_holdings_url>
<holdings_id>17150</holdings_id>
</Electronic_Holdings>
</Institutional_links>
</Institution>

My problem is
1. problem in contact tag '>' is not showing , instead its showing &gt
2. Patron_ip_range is tag is not showing
3. ronic_Holdings> , </Institutional_links>, </Institution> tag is repeating
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 9 2009
Added on May 11 2009
#pl-sql-xml-programming, #xml
7 comments
2,700 views