3 Replies Latest reply: Apr 10, 2013 12:22 PM by Marco Gralike RSS

    How to expose a function that returns a XML as a Web Service?

    GxFlint
      ---------------------------------------------------------------------------------
      Oracle Database 11g Release 11.2.0.3.0 - 64bit Production
      PL/SQL Release 11.2.0.3.0 - Production
      "CORE     11.2.0.3.0     Production"
      TNS for 64-bit Windows: Version 11.2.0.3.0 - Production
      NLSRTL Version 11.2.0.3.0 - Production
      ---------------------------------------------------------------------------------

      After going through the oracle documentation about generating xml data from the database, I managed to select normal tables and return an xml result exactly the way I want:
      -- My types
      create or replace 
      TYPE CHILD_T AS OBJECT ("@ID" VARCHAR2(20), "@NAME" VARCHAR2(20));
      
      create or replace 
      TYPE CHILDREN_T AS TABLE OF CHILD_T;
      
      create or replace 
      TYPE PARENT_T AS OBJECT ("@ID" VARCHAR2(20), CHILDREN CHILDREN_T );
      
      -- The actual select
      SELECT XMLFOREST (
                  PARENT_T(d.id,
                  CAST ( MULTISET (SELECT c.id, c.name
                                   FROM child c
                                   WHERE c.parent_id = p.id) AS CHILDREN_T )
                  ) AS "Parent"
              ) 
      FROM parent p;
      That gets me this:
      <Parent ID="1">
          <CHILDREN>
              <CHILD_T ID="1" NAME="xxxxx" />
              <CHILD_T ID="2" NAME="yyyyy" />
              <CHILD_T ID="3" NAME="zzzzz" />
          </CHILDREN>
      </Parent>
      That's perfect, but how do I expose this result as a Web Service like this link?
       http://localhost:8080/orawsv/MY_USER/GET_CHILDREN?wsdl
      I tried this function:
      FUNCTION GET_CHILDREN (
          PARENT_ID IN VARCHAR2
      ) RETURN CLOB
      AS
          L_RESULT CLOB;
      BEGIN
          SELECT to_clob(XMLFOREST (
                  PARENT_T (p.id,
                  CAST ( MULTISET (SELECT c.id, c.name
                                   FROM child c
                                   WHERE c.parentId = p.id) AS CHILDREN_T )
                  ) AS "ParentObj"
            )) AS MY_XML INTO L_RESULT
          FROM parent p
          WHERE p.i = PARENT_ID;
      
          RETURN (L_RESULT);
      END GET_CHILDREN;
      But when I access It though .NET Compact Framework I get this: "'Element' is an invalid XmlNodeType "

      As you can see, I just need a simple way to transport information between a Windows CE Handheld computer and our database. Functions that return simple values are working.
      Do I have to return this data as xml?
      What return value should I use? Varchar2 throws an out of buffer error.

      ---------
      This is my first post here, also my first contact with OracleDB and Web Services.
        • 1. Re: How to expose a function that returns a XML as a Web Service?
          odie_63
          Hi,

          Welcome to Oracle and XML DB!
          After going through the oracle documentation about generating xml data from the database, I managed to select normal tables and return an xml result exactly the way I want:
          Did you also read about SQL/XML functions ?
          You don't have to create SQL type objects to generate XML.

          Using only XMLElement, XMLAgg, XMLAttributes, etc. you should be able to generate any kind of complex structure out of relational data, and with full control over the namings (which you don't have with object types).
          Your example can be rewritten to :
          SELECT XMLElement("Parent",
                   XMLAttributes(p.id as "Id")
                 , XMLElement("Children",
                     (
                       SELECT XMLAgg(
                                XMLElement("Child",
                                  XMLAttributes(
                                    c.id as "Id"
                                  , c.name as "Name"
                                  )
                                )
                              )
                       FROM child c
                       WHERE c.parent_id = p.id
                     )
                   )
                 )
          FROM parent p 
          WHERE p.id = :parent_id ;
          That's perfect, but how do I expose this result as a Web Service like this link?
          [...]
          Do I have to return this data as xml?
          Yes.
          Define the return datatype as XMLType :
          FUNCTION GET_CHILDREN (
              PARENT_ID IN VARCHAR2
          ) 
          RETURN XMLTYPE
          AS
              L_RESULT XMLTYPE;
          BEGIN
          
            SELECT XMLElement("Parent",
                     XMLAttributes(p.id as "Id")
                   , XMLElement("Children",
                       (
                         SELECT XMLAgg(
                                  XMLElement("Child",
                                    XMLAttributes(
                                      c.id as "Id"
                                    , c.name as "Name"
                                    )
                                  )
                                )
                         FROM child c
                         WHERE c.parent_id = p.id
                       )
                     )
                   )
            INTO L_RESULT
            FROM parent p 
            WHERE p.id = PARENT_ID ;
           
            RETURN (L_RESULT);
          
          END GET_CHILDREN;
          • 2. Re: How to expose a function that returns a XML as a Web Service?
            GxFlint
            I didn't know about SQL/XML functions, now everything is working and the xml result is even better than what I expected.

            Now I can get back to actually developing my application.
            Thanks.
            • 3. Re: How to expose a function that returns a XML as a Web Service?
              Marco Gralike
              GxFlint wrote:
              Now I can get back to actually developing my application.
              LOL. You were developing your application. This bit is part of your design as well... ;-)