how to change the default element tag using dbms_xmlgen
here is my code that generate output for purchase order data. I followed the syntax shown in xml db developer guide.
I am getting the results but element tags are CAPS letters( As the coloumn names in the type defenitions are stored in CAPS in Oracle). but I need to show in small letters as per my requirement.
can anyone help me how to change the default tag names for elements.
==================================HERE IS THE CODE==================
DECLARE
qryCtx DBMS_XMLGEN.ctxHandle;
result CLOB;
BEGIN
qryCtx := DBMS_XMLGEN.newContext
('SELECT PODL_H_T
( CLOSEDDATETIME ,
COMPANY ,
CAST(MULTISET
(SELECT LINENUMBER ,
COMPANY ,
PURCHASEORDERID ,
ITEM ,
QUANTITYUM ,
TOTALQUANTITY
FROM cpo_wms_podl_LINES
WHERE PURCHASEORDERID = PH.PURCHASEORDERID) as PurchaseOrderDetailList
))
FROM cpo_wms_podl_HEADERS PH ');
-- now get the result
DBMS_XMLGEN.setRowSetTag(qryCtx, 'Receipts' );
DBMS_XMLGEN.setRowTag(qryCtx, 'PurchaseOrder' );
result := DBMS_XMLGEN.getXML(qryCtx);
INSERT INTO temp_clob_tab VALUES (result);
DBMS_XMLGEN.closeContext(qryCtx);
END;
-- select * from temp_clob_tab
===========create type script=====================
cpo_wms_podl_HEADERS
CREATE or replace TYPE PurchaseOrderDetail AS OBJECT(
LINENUMBER VARCHAR2(400 BYTE),
COMPANY VARCHAR2(400 BYTE),
PURCHASEORDERID VARCHAR2(400 BYTE),
ITEM VARCHAR2(400 BYTE),
QUANTITYUM VARCHAR2(400 BYTE),
TOTALQUANTITY NUMBER
)
create type PurchaseOrderDetailList as table of PurchaseOrderDetail
create table temp_clob_tab(result CLOB)
create type podl_HEADERS_list_t as table of podl_HEADERS_t
CREATE or replace TYPE PODL_H_T AS OBJECT
(
CLOSEDDATETIME DATE,
COMPANY VARCHAR2(400 BYTE),
CREATEDDATETIME DATE,
PURCHASEORDERID VARCHAR2(400 BYTE),
SHIP_TO VARCHAR2(400 BYTE),
linelist PurchaseOrderDetailList
)