8 Replies Latest reply: Jun 11, 2013 7:59 AM by Alexander26k RSS

    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
          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.
            • 3. Re: Apex. XML to report
              Alexander26k
              Up
              • 4. Re: Apex. XML to report
                Alexander26k
                UP
                • 5. Re: Apex. XML to report
                  Alexander26k
                  UP!!!!!
                  • 6. Re: Apex. XML to report
                    mobra
                    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".