 |
|
DBMS_XMLQUERY vs. DBMS_XMLGEN
Oracle Database Tips by Donald BurlesonMarch 10, 2015
|
The following issue came up for one of our
forum users while creating a webservice to return nested XML data. The data
contains a special character that prevented IE 6.0 from displaying the XML
output.
<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>
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, he could set
the XML header to his company standard encoding
<?xml
version = '1.0' encoding = 'ISO-8859-1'?>, and the webservice works
just fine.
The code is almost identical. The irrelevant portions are
snipped out here.
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);
As you can see from the comment, he learned that
multiple occurrences of a bind variable does not work in DBMS_XMLQUERY. You
receive the following error:
oracle.xml.sql.OracleXMLSQLException: Missing IN or OUT parameter at index::
2
Where the index refers to which occurrence of a bind
variable (starting at 0) failed. In this case, it was the third
bind_variable occurrence that failed.
|| '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)
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
and include the URL for the page.
