CODEWITHSUNDEEP
xmloracle-databaseplsqloracle11gxmltype
Gaurav Dhavale
Gaurav Dhavale

Reputation: 151

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

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;

Upvotes: 1

Views: 2451

Answers (2)

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59557

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;

Upvotes: 0

Alex Poole
Alex Poole

Reputation: 191560

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.

Upvotes: 3

Related Questions