How to return Table as a row using Oracle XML DB Webservices

Question

I am trying to expose an Oracle Table as a web service using XML DB Service. I want to return multiple rows of table based on where condition passed in the Request of the web service.

Following -: native-oracle-xml-db-web-services-11gr1

A procedure is developed which takes PK column as input, and returns column as output. It works when return is column by column. But when I try to return the complete row as type, it is not working.

This is working.

URL of webservice -: http://domain:8080/orawsv/TEST/GET_TEST_TAB?wsdl

create or replace PROCEDURE GET_TEST_TAB (
  p_id   IN  test_tab.id%TYPE,
  p_description   OUT test_tab.description%type) AS
  BEGIN

      SELECT description into p_description   FROM   test_tab
  WHERE  id = p_id;
END GET_TEST_TAB;

Now I want to return the multiple rows of TEST_TAB table, without hard coding the column name.

Answer 1

I have modified the procedure as

CREATE OR replace PROCEDURE P_TableAsWS_XMLIN_XMLOUT (
    p_in    IN XMLTYPE,
    p_out   OUT XMLTYPE
) AS
  BEGIN
--  cursor_ OUT SYS_REFCURSOR) AS
--  with data as
--(select '<a><c>1</c><c>2</c></a>' xmlval
-- from dual)
    SELECT
        XMLELEMENT(
            "employees",XMLAGG(XMLELEMENT(
                "employee",XMLFOREST(e.id AS "empno",e.description AS "ename")
            ) )
        ) 
    INTO p_out
    FROM
        test_tab e,

                XMLTABLE ( '/inp/*' PASSING  p_in 
                    COLUMNS
                        id  varchar(50)   PATH '/id'
                )  inp
WHERE
  e.id = inp.id  ;

end P_TableAsWS_XMLIN_XMLOUT;

Passing Input -:

<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:p="http://xmlns.oracle.com/orawsv/TEST/P_TABLEASWS_XMLIN_XMLOUT">
   <soapenv:Header/>
   <soapenv:Body>
      <p:P_TABLEASWS_XMLIN_XMLOUTInput>
         <p:P_OUT-XMLTYPE-OUT/>
         <p:P_IN-XMLTYPE-IN>
            <inp>
             <id>1</id>
             <id>2</id>
            </inp>
         </p:P_IN-XMLTYPE-IN>
      </p:P_TABLEASWS_XMLIN_XMLOUTInput>
   </soapenv:Body>
</soapenv:Envelope>

Getting Output -:

<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
   <soap:Body>
      <P_TABLEASWS_XMLIN_XMLOUTOutput xmlns="http://xmlns.oracle.com/orawsv/TEST/P_TABLEASWS_XMLIN_XMLOUT">
         <P_OUT>
            <employees>
               <employee>
                  <empno>1</empno>
                  <ename>ONE</ename>
               </employee>
               <employee>
                  <empno>2</empno>
                  <ename>TWO</ename>
               </employee>
            </employees>
         </P_OUT>
      </P_TABLEASWS_XMLIN_XMLOUTOutput>
   </soap:Body>
</soap:Envelope>

Problem is -:

a) I have manually added each column in the procedure. If any new column is added, procedure needs to be modified

b) XMLTABLE output column type has to be hard coded. Unable to define column as TABLE_NAME.COLUM_NAME%TYPE