–using oracle SQL/PLSQL function get_xml to return value from xml code

CREATE OR REPLACE function CALLMANAGER_WS.get_xml(p_xml varchar2)  return CALLMANAGER_WS.Call_row_table PIPELINED  as

     

    p_call_id varchar2(300);

    p_called_address varchar2(300);

    p_called_address_name varchar2(300);

    p_calling_address varchar2(300);

    p_calling_address_name varchar2(300);

   

    begin

      

select   xmltype(p_xml).extract(‘//CALL_ID/text()’).getstringval() CALL_ID,

                         xmltype(p_xml).extract(‘//CALLED_ADDRESS/text()’).getstringval()CALLED_ADDRESS,

                         xmltype(p_xml).extract(‘//CALLED_ADDRESS_NAME/text()’).getstringval()CALLED_ADDRESS_NAME,

                         xmltype(p_xml).extract(‘//CALLING_ADDRESS/text()’).getstringval()CALLING_ADDRESS,

                         xmltype(p_xml).extract(‘//CALLING_ADDRESS_NAME/text()’).getstringval()CALLING_ADDRESS_NAME

                         into p_call_id, p_called_address, p_called_address_name,p_calling_address,p_calling_address_name

            from  dual;

         

     PIPE ROW(CALLMANAGER_WS.Call_row(p_call_id, p_called_address, p_called_address_name,p_calling_address,p_calling_address_name));

    

        RETURN;

       

    end get_xml;