How to rename an Oracle XMLTYPE`s generated ROW and ROWSET node and convert it to varchar

Question

In my stored procedure, I am fetching result sys_refcursor RESULTSET. When I am converting it to XMLTYPE using RESULTSET_XML := XMLTYPE(RESULTSET); RESULTSET_XML is of SYS.XMLTYPE type I am getting it as

    <?xml version="1.0"?>
    <ROWSET>
     <ROW>
      <DELIVERY_ORDER_ID>2</DELIVERY_ORDER_ID>
      <COMPARTMENT_ID>162</COMPARTMENT_ID>
     </ROW>
     <ROW>
      <DELIVERY_ORDER_ID>2</DELIVERY_ORDER_ID>
      <COMPARTMENT_ID>163</COMPARTMENT_ID>
     </ROW>
     <ROW>
      <DELIVERY_ORDER_ID>2</DELIVERY_ORDER_ID>
  <COMPARTMENT_ID>164</COMPARTMENT_ID>
 </ROW>
</ROWSET>

This result I got by converting it to char using RESULTSET_XML.getStringVal(); Now I want to send this data to stored procedure as a VARCHAR2. But the stored procedure required data in the format given below as a VARCHAR2

 <DocumentElement>
        <Maingrid>
          <DELIVERY_ORDER_ID>2</DELIVERY_ORDER_ID>
          <COMPARTMENT_ID>162</COMPARTMENT_ID>
        </Maingrid>
       <Maingrid>
          <DELIVERY_ORDER_ID>2</DELIVERY_ORDER_ID>
          <COMPARTMENT_ID>163</COMPARTMENT_ID>
         </Maingrid>
        <Maingrid>
          <DELIVERY_ORDER_ID>2</DELIVERY_ORDER_ID>
          <COMPARTMENT_ID>164</COMPARTMENT_ID>
        </Maingrid>
        </DocumentElement>

So is there any way to rename these tags and send it as varchar2. I was trying the solution given in link [[1]: How to rename an Oracle XMLTYPE node

but since I have resultset I am not able to use this.

Or is there an way in select statement itself i will get the result in above format. My oracle query is

SELECT 
    P_DO_ID AS DELIVERY_ORDER_ID, COMPARTMENT_ID 
FROM 
    T_M_COMPARTMENT 
WHERE 
    VEHICLE_ID = V_TRAILER;

Alex Poole · Accepted Answer · 2014-10-01T10:01:09.040

You can use the DBMS_XMLGEN package to set the node names and generate the XML object. As a simple example in an anonymous block:

set serveroutput on;
declare
  v_trailer number := 42; -- local for demo
  ctx dbms_xmlgen.ctxhandle;
  resultset sys_refcursor;
  resultset_xml xmltype;
begin
  open resultset for
    select p_do_id as delivery_order_id,compartment_id 
    from t_m_compartment
    where vehicle_id = v_trailer;
  ctx := dbms_xmlgen.newcontext(resultset);
  dbms_xmlgen.setrowsettag(ctx, 'DocumentElement');
  dbms_xmlgen.setrowtag(ctx, 'Maingrid');

  resultset_xml := dbms_xmlgen.getxmltype(ctx);
  dbms_xmlgen.closecontext(ctx);
  close resultset;

  dbms_output.put_line(resultset_xml.getStringVal());
end;
/

I'm using your query string to create the result set, but if you're calling another function to get that just substitute whatever mechanism/call you're using now instead of that assignment. If you're executing your query inside the procedure to create the result set (i.e. it isn't coming from a call to another function), you can just pass the query text in to new_context instead of an open ref cursor; although you'd have to concatenate your v_trailer value so the ref cursor is going to be cleaner overall.

I set a dummy table up to look like your sample, and this gets the output:

<DocumentElement>
 <Maingrid>
  <DELIVERY_ORDER_ID>2</DELIVERY_ORDER_ID>
  <COMPARTMENT_ID>162</COMPARTMENT_ID>
 </Maingrid>
 <Maingrid>
  <DELIVERY_ORDER_ID>2</DELIVERY_ORDER_ID>
  <COMPARTMENT_ID>163</COMPARTMENT_ID>
 </Maingrid>
 <Maingrid>
  <DELIVERY_ORDER_ID>2</DELIVERY_ORDER_ID>
  <COMPARTMENT_ID>164</COMPARTMENT_ID>
 </Maingrid>
</DocumentElement>

getStringVal() gives you a CLOB, but you can insert that into a varchar2 column as long as it will always be less than 4k (or 32k if you're on 12c), or pass it to another procedure as long as it's less than 32k. You'd probably be better off passing it as a CLOB or XMLType though, to keep it consistent.

This works. I put this things in a function and i am returning it as `CLOB` but my return `varible` of `CLOB` type is not showing any output even though its available.I saw output using `DBMS_OUTPUT.PUT_LINE` — Gaurav Dhavale, Oct 01 '14 at 11:31
@GauravDhavale - not quite sure what you mean; how are you calling the function? Do you mean an SQL*Plus variable - if so how are you assigning and displaying it; did you `print` it after the call? — Alex Poole, Oct 01 '14 at 11:33
using below query i got the required structure `SELECT XMLELEMENT("DocumentElement", XMLAGG(XMLELEMENT("MainGrid",XMLFOREST( COMPARTMENT_ID , DELIVERY_ORDER_ID)))) ABC FROM T_M_COMPARTMENT, T_DE_DELIVERY_ORDER_DETAILS DO WHERE VEHICLE_ID = :V_TRAILER and DO.DELIVERY_ORDER_ID = 2;` — Gaurav Dhavale, Oct 01 '14 at 11:33
@GauravDhavale - [this works as a function too](http://sqlfiddle.com/#!4/47685/1), so may be down to how you're calling it, or what you're doing with the result if it's assigned to a variable. Your `xmlelement` approach looks OK too if you don't want to use a stored procedure. (Though you have no join condition between your two tables at the moment). — Alex Poole, Oct 01 '14 at 11:42

score 0 · Answer 2 · answered Oct 01 '14 at 14:29

Another way is to define an ObjectType and create XML from there:

CREATE OR REPLACE TYPE "Maingrid" AS OBJECT 
   ("DELIVERY_ORDER_ID" NUMBER, "COMPARTMENT_ID" NUMBER);
/

SELECT XMLELEMENT("DocumentElement", 
    XMLAGG(XMLTYPE("Maingrid"(P_DO_ID, COMPARTMENT_ID)))).getClobVal() 
FROM T_M_COMPARTMENT 
WHERE VEHICLE_ID = V_TRAILER;

How to rename an Oracle XMLTYPE`s generated ROW and ROWSET node and convert it to varchar

2 Answers2