Forum Stats

  • 3,769,695 Users
  • 2,253,009 Discussions
  • 7,875,155 Comments

Discussions

Create xml web service

Pavel_p
Pavel_p Member Posts: 2,311 Gold Trophy

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

Mark Sta Ana

Answers

  • Pavel_p
    Pavel_p Member Posts: 2,311 Gold Trophy
    edited Jun 2, 2016 9:27AM

    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.

  • Chris Menten
    Chris Menten Member Posts: 8
    edited Jun 20, 2016 10:56AM

    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.


    Pavel_pMark Sta Ana
  • jnicholas330
    jnicholas330 Member Posts: 92 Green Ribbon
    edited Jun 29, 2016 5:35PM

    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.

  • Mike Kutz
    Mike Kutz Member Posts: 5,816 Silver Crown
    edited Jul 1, 2016 10:59AM

    Answering based on your comment here:

    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

    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

    Pavel_p
  • Pavel_p
    Pavel_p Member Posts: 2,311 Gold Trophy
    edited Jul 2, 2016 5:54PM

    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.

  • Jon Dixon
    Jon Dixon Member Posts: 27 Red Ribbon
    edited Sep 15, 2016 8:33AM

    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

This discussion has been closed.