Issue Summary
I am creating a webservice to return nested XML data. The data contains a special character that prevented IE 6.0 from displaying the XML output.
CODE
<Plant num="4">
<TypeCode>PFL</TypeCode>
<TypeDesc>Plant</TypeDesc>
<Code>ABAXA</Code>
<PrefScientificName>Arracacia xanthorrhiza</PrefScientificName>
<PrefCommonName>Arracacha</PrefCommonName>
<CommonNames>
<CommonNames_ROW num="3">
<Language>French</Language>
<CommonName>Pomme de terre céleri</CommonName>
</CommonNames_ROW>
</CommonNames>
</Plant>
<TypeCode>PFL</TypeCode>
<TypeDesc>Plant</TypeDesc>
<Code>ABAXA</Code>
<PrefScientificName>Arracacia xanthorrhiza</PrefScientificName>
<PrefCommonName>Arracacha</PrefCommonName>
<CommonNames>
<CommonNames_ROW num="3">
<Language>French</Language>
<CommonName>Pomme de terre céleri</CommonName>
</CommonNames_ROW>
</CommonNames>
</Plant>
By default, XMLGEN generates an xml header <?xml version = '1.0'?>
This generates the IE 6.0 error because of the accent marked e in céleri.
By converting to DBMS_XMLQUERY, I can set the XML header to our company standard encoding <?xml version = '1.0' encoding = 'ISO-8859-1'?>, and the webservice works just fine.
The code is almost identical. The irrelavant portions are snipped out here.
CODE
qryCtx := DBMS_XMLQuery.newContext(v_prd_query);
--
-- Set the encoding tag to handle Unicode
--
DBMS_XMLQuery.setEncodingTag(qryCtx, 'ISO-8859-1');
DBMS_XMLQuery.setRowsetTag(qryCtx, 'root');
DBMS_XMLQuery.setRowTag(qryCtx, v_bayer_group_tag);
--
-- Set up the bind variables.
-- Note: DBMS_XMLQuery cannot handle a bind variable occurring more than once.
--
DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_bayer_group', UPPER(i_bayer_group));
DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_filter1', UPPER(i_filter));
DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_filter2', UPPER(i_filter));
DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_filter3', UPPER(i_filter));
--
-- Execute the query and put the results into the CLOB
--
o_results := DBMS_XMLQuery.getXML(qryCtx);
--
-- Close the context
--
DBMS_OUTPUT.PUT_LINE('Close Context');
DBMS_XMLQuery.closeContext(qryCtx);
--
-- Set the encoding tag to handle Unicode
--
DBMS_XMLQuery.setEncodingTag(qryCtx, 'ISO-8859-1');
DBMS_XMLQuery.setRowsetTag(qryCtx, 'root');
DBMS_XMLQuery.setRowTag(qryCtx, v_bayer_group_tag);
--
-- Set up the bind variables.
-- Note: DBMS_XMLQuery cannot handle a bind variable occurring more than once.
--
DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_bayer_group', UPPER(i_bayer_group));
DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_filter1', UPPER(i_filter));
DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_filter2', UPPER(i_filter));
DBMS_XMLQuery.setbindvalue (qryCtx, 'cp_filter3', UPPER(i_filter));
--
-- Execute the query and put the results into the CLOB
--
o_results := DBMS_XMLQuery.getXML(qryCtx);
--
-- Close the context
--
DBMS_OUTPUT.PUT_LINE('Close Context');
DBMS_XMLQuery.closeContext(qryCtx);
As you can see from the comment, I learned that multiple occurences of a bind variable does not work in DBMS_XMLQUERY. You receive the followinig error.
oracle.xml.sql.OracleXMLSQLException: Missing IN or OUT parameter at index:: 2
Where the index refers to which occurence of a bind variable (starting at 0) failed. In this case, it was the third bind_variable occurence that failed.
CODE
|| 'FROM sde_bayer_code_pref_names_v bc' || CHR(10)
|| ' ,(SELECT bc2.type_code AS type_code' || CHR(10)
|| ' ,bc2.code_seq_id AS code_seq_id' || CHR(10)
|| ' FROM sde_bayer_code_pref_names_v bc2' || CHR(10)
|| ' WHERE UPPER(bc2.code) like UPPER(:cp_filter1)' || CHR(10)
|| ' UNION' || CHR(10)
|| ' SELECT sn2.type_code AS type_code' || CHR(10)
|| ' ,sn2.code_seq_id AS code_seq_id' || CHR(10)
|| ' FROM sde_bayer_code_sci_names_v sn2' || CHR(10)
|| ' WHERE UPPER(sn2.short_name) like UPPER(:cp_filter1 || :cp_filter2)' || CHR(10)
|| ' ,(SELECT bc2.type_code AS type_code' || CHR(10)
|| ' ,bc2.code_seq_id AS code_seq_id' || CHR(10)
|| ' FROM sde_bayer_code_pref_names_v bc2' || CHR(10)
|| ' WHERE UPPER(bc2.code) like UPPER(:cp_filter1)' || CHR(10)
|| ' UNION' || CHR(10)
|| ' SELECT sn2.type_code AS type_code' || CHR(10)
|| ' ,sn2.code_seq_id AS code_seq_id' || CHR(10)
|| ' FROM sde_bayer_code_sci_names_v sn2' || CHR(10)
|| ' WHERE UPPER(sn2.short_name) like UPPER(:cp_filter1 || :cp_filter2)' || CHR(10)
So, DBMS_XMLGEN is faster, recommended, but doesn't allow for the specification of the encoding tag. DBMS_XMLQUERY allows for the encoding tag, as well as the specification of an id variable and value for each row in a rowset, but it cannot handle multiple occurences of the same name for a bind variable.