Skip to Main Content

Database Software

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!

how to change the default element tag using dbms_xmlgen

pradeep challaFeb 22 2010 — edited Feb 24 2010
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
)
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 24 2010
Added on Feb 22 2010
5 comments
2,357 views