8 Replies Latest reply on Jun 11, 2013 12:59 PM by Alexander26k

    Apex. XML to report

    Alexander26k
      Hi. I have a procedure by which I get the data as XML. How to represent them in the form of a report?
        • 1. Re: Apex. XML to report
          Roel Hartman
          Take a look at this http://psoug.org/reference/xml_functions.html (or the real Oracle Docs).
          You need to familiarize yourself with these XML functions to transform your XML into a table structure (and then use that to base your report upon)
          • 2. Re: Apex. XML to report
            Alexander26k
            Thanks for your answer.
            I want to explain to you in more detail what I do.
            I have a procedure:

            DECLARE
            Ret_value varchar2(32767);
            STAT NUMBER;
            BEGIN
            STAT := NULL;
            Ret_value := CHECK_HTTP ( STAT );
            return Ret_value;
            END;

            Then i create Item(Display Only) with Pl/SQL function.

            Finally I got here this XML code:
            <?xml version="1.0"?>
            <ROOT>
            <ROW>
            <SMIB>1</SMIB>
            <VERSION>S058.223X</VERSION>
            </ROW>
            </ROOT>

            As can present this code in the form of a report? (except css and html).

            Thank you very much.
            Best Regards, Alexander.
            • 5. Re: Apex. XML to report
              Alexander26k
              UP!!!!!
              • 6. Re: Apex. XML to report
                MortenBraten
                I have a procedure:

                DECLARE
                Ret_value varchar2(32767);
                STAT NUMBER;
                BEGIN
                STAT := NULL;
                Ret_value := CHECK_HTTP ( STAT );
                return Ret_value;
                END;

                Then i create Item(Display Only) with Pl/SQL function.

                Finally I got here this XML code:
                <?xml version="1.0"?>
                <ROOT>
                <ROW>
                <SMIB>1</SMIB>
                <VERSION>S058.223X</VERSION>
                </ROW>
                </ROOT>

                As can present this code in the form of a report? (except css and html).
                I'll second Roel's advice that "You need to familiarize yourself with these XML functions to transform your XML into a table structure (and then use that to base your report upon)".

                Here is a simple example to get a single value from the first row:
                select xmltype('<?xml version="1.0"?>
                <ROOT>
                <ROW>
                <SMIB>1</SMIB>
                <VERSION>S058.223X</VERSION>
                </ROW>
                </ROOT>').extract('//ROW[1]/VERSION/text()').getstringval()
                from dual
                Used in your context you could create a PL/SQL region and do something like:
                DECLARE
                  l_xml xmltype;
                  STAT NUMBER;
                BEGIN
                  STAT := NULL;
                  l_xml := xmltype(CHECK_HTTP ( STAT ));
                  htp.p (l_xml.extract('//ROW[1]/VERSION/text()').getstringval());
                END;
                You'd use a loop if you expect more than one row returned (or use a Report Region instead and transform the XML into tabular data as suggested by Roel), but I think you get the idea...

                - Morten

                http://ora-00001.blogspot.com
                • 7. Re: Apex. XML to report
                  Mike Kutz
                  I'd use XMLTable() to transform it into a table and just use a regular dynamic report page.

                  The Oracle Docs have a better example of how you'll need to use it.
                  http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions253.htm#CIHGGHFB

                  MK
                  • 8. Re: Apex. XML to report
                    Alexander26k

                    More information. I have a procedure

                     

                    declare

                    p_stat number;

                    v_resp VARCHAR2 (32000);

                    v_url varchar2 (32000): = 'http://xxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

                    v_request varchar2 (32000): = 'xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx';

                    begin

                    v_status: = apex.main.http_cgi_request (v_url, v_request, v_response);

                    return v_response;

                    end;

                     

                     

                    For her, I create (say P1_XML) Item: Dusplay Only, properties:

                    1.Always, replacing any existing value in session state.

                    2.PL/SQL FUNCTION BODY

                     

                     

                    And write this procedure in a Source value or expression.

                     

                     

                    I load the page and see:

                    <? xml version = "1.0"?>

                    <ROOT>

                    <ROW>

                    <TVIN> 1 </ TVIN>

                    <VERS> M238.RR </ VERS>

                    </ ROW>

                    </ ROOT>

                     

                     

                    Next, create a region of HTML, where Source quote:

                    : P1_XML

                     

                     

                     

                     

                    In the end, I see:

                    <? xml version = "1.0"?>

                    <ROOT>

                    <ROW>

                    <TVIN> 1 </ TVIN>

                    <VERS> M238.RR </ VERS>

                    </ ROW>

                    </ ROOT>

                     

                    1 M238.RR

                     

                    As that's it. And you want to output either 1 or M238.RR.

                    If you want to remove the xml from a page, in the St-ah item'a instead of "Display Only" put "Stop and Start HTML Layout Table".