Returning multiple XML from ref cursor oracle-CodePudding

There is a procedure in the Oracle DB which takes a date range and generates XML file for each day and the return type is ref cursor.

When the procedure is called from C# code, I am unable to extract the XML from the cursor. Any pointers will be helpful on how to extract the XML

Below given is the small example of the query which is returning the XML's as ref cursor from the procedure call.

PROCEDURE GET_XML_DATERANGE(
    start_date IN DATE,
    end_date IN DATE,
    p_cursor_xml OUT SYS_REFCURSOR
)
IS
V_CURSOR SYS_REFCURSOR;
BEGIN
 OPEN V_CURSOR FOR
     WITH DATES AS(
                SELECT (to_date(start_date ,'yyyy-mm-dd')   rownum -1) as d_date
                FROM ALL_OBJECTS
                WHERE rownum <= to_date(end_date,'yyyy-mm-dd')-to_date(start_date,'yyyy-mm-dd') 1
                )
           SELECT XMLELEMENT("ExampleXML", 
            XMLATTRIBUTES('1.1.1.1' AS "version",
             REPLACE(to_char((d.d_date),'yyyy-mm-dd HH:MM:SS'),' ','T') AS "timeStamp"
             )) from DATES d;
p_cursor_xml := V_CURSOR ;
END GET_XML_DATERANGE;

As per the query it returns multiple XML for each date that is returned as ref cursor. Just wanted some pointers on how to extract the multiple xml's from the ref cursor that will be output parameter from the C# code.

CodePudding user response：

Your code has a few issues (including calling TO_DATE on values that are already dates) and can be much simpler; however, the main change is to call .getStringVal() on the created element as this will convert it from an XMLTYPE data type to a VARCHAR2 string and then you can just get the XML as you would any other string from each row of the returned cursor:

CREATE PROCEDURE GET_XML_DATERANGE(
    start_date IN DATE,
    end_date IN DATE,
    p_cursor_xml OUT SYS_REFCURSOR
)
IS
BEGIN
  OPEN p_cursor_xml FOR
    SELECT XMLELEMENT(
             "ExampleXML", 
             XMLATTRIBUTES(
               '1.1.1.1' AS "version",
               to_char(TRUNC(start_date)   LEVEL - 1, 'yyyy-mm-dd"T"HH24:MI:SS')
                 AS "timeStamp"
             )
           ).getStringVal() AS xml
    FROM   DUAL
    CONNECT BY LEVEL <= TRUNC(end_date)-TRUNC(start_date)   1;
END GET_XML_DATERANGE;
/

If the data you generate is too large for a VARCHAR2 then use .getClobVal() instead.