6 Replies Latest reply on Sep 15, 2016 12:33 PM by Jon Dixon

    Create xml web service

    Pavel_p

      Hello,

      it used to be possible to create declaratively a web service in APEX that produced xml. This option had been removed (because of some reason I don't understand) and now web services can produce either text or JSON, even if xml seems to be still way more popular and commonly used (I mean in the business world, not some fancy webapps). Oracle DB offers way better support for xml than for JSON and working with JSON in APEX is painful compared to xml (just my personal opinion), so if I could get back xml support, I would be really glad.

      So please, what's the recommended procedure now if I want to create a web service that produces xml?

      Thanks a lot,

      Pavel

        • 1. Re: Create xml web service
          Pavel_p

          Well, if I get it right, there is currently no recommended procedure to create a xml web service. So I'll stick with NetBeans and RESTful services from database then.

          • 2. Re: Create xml web service
            Chris Menten

            With ORDS it's possible to use a stored procedure to generate an XML.

            Create a query that selects an XMLELEMENT and then use .getclobval to convert it to CLOB.

            Pass that CLOB variable to HTP.print.

            Finally create your webservice and use ords.source_type_plsql to be able to use a PL/SQL block as a source to call your procedure.

             

            For example:

             

            CREATE OR REPLACE PROCEDURE P_SHOW_XML_TABLE

            IS

            l_clob CLOB;

            BEGIN

             

            SELECT XMLELEMENT("article",

                           XMLFOREST( e.id as "id",

                                                .....

                           )

                     ).getClobVal()

              INTO   l_clob

              FROM article e

              where rownum = 1;

             

              OWA_UTIL.mime_header('text/xml');

              HTP.print(l_clob);

            EXCEPTION

              WHEN OTHERS THEN

                raise;

            END P_SHOW_XML_TABLE;

             

            Then just create your service and pass this newly created procedure in the p_source parameter:

             

            BEGIN

              ords.create_service (

                  p_module_name => 'test.xmltable' ,

                  p_base_path   => '/test/',

                  p_pattern     => 'xmltable/',

                  p_method      => 'GET',

                  p_source_type => ords.source_type_plsql,

                  p_source      => 'begin p_show_xml_table; end;'

              );

            commit;

            END;

             

            You can also add parameters to your procedure.

            Add some bind variables to you pattern and add them to the procedure call in the PL/SQL block.


            1 person found this helpful
            • 3. Re: Create xml web service
              jnicholas330

              I would like to see this as well. We have applications that are only capable of consuming XML. Right now we write custom .NET code to convert the relational structures to XML. Ignoring XML attributes, JSON and XML should map just about 1-1.

              • 4. Re: Create xml web service
                Mike Kutz

                Answering based on your comment here:

                Re: How to connect to Oracle DB with Excel VBA

                 

                They removed XML?!? Ugh!

                I've had problems with SYS_GENXML() function.  I'm assuming the decision to stop is related to this.

                 

                What Chris has mentioned may be the best way using ORDS.

                I do agree that creating that code for each service will be a PITA.

                 

                But, there is oddgen

                Once a generator is created, then producing the required package should be as easy as "point + click".

                 

                I believe that the "web service" feature is tied to ORDS, not APEX.

                That suggest that you might be able to run ORDS 2.x on a second web server just to support XML.

                 

                Beyond that, until someone like Kris Rice-Oracle tells you how/why, I'd stick to the NetBeans method.

                 

                MK

                1 person found this helpful
                • 5. Re: Create xml web service
                  Pavel_p

                  Thank you for pointing me to oddgen, I had no idea such thing exists (I'll have to put some time and effort to understand what it's capable of though).

                  I believe that the "web service" feature is tied to ORDS, not APEX.

                  I did not formulate my question properly. Actually I wanted to know what is the recommended "best practice" to produce xml output from the database using ORDS (no matter if declaratively using APEX or not) since I just cannot believe that it's not possible to produce xml output at all. There is nothing wrong with Chris's method...but I would probably expect something more user friendly.

                  • 6. Re: Create xml web service
                    Jon Dixon

                    I think Chris's method is the best you are going to get. I have used a version of this on a number of occasions with success. Of course there are other ways of building the XML within the PL/SQL package/procedure https://docs.oracle.com/cd/B28359_01/appdev.111/b28369/xdb13gen.htm#CHDDIHBH and of course you can always build it manually by concatenating the XML tags