This discussion is archived
3 Replies Latest reply: Apr 10, 2013 10:22 AM by Marco Gralike RSS

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

GxFlint Newbie
Currently Being Moderated
---------------------------------------------------------------------------------
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE Director
    Currently Being Moderated
    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... ;-)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points