Q: converting output of a Query into xml I have a table named emp with name, id, salary as column names.
My procedure takes id as input and it can be more than one id also. like('1','2','3',...)
Required: For each and every id the values should be returned as XML.
Supoose: For id=1 and id=2 as input my output should be
<row>
<name>name1</name> 
<salary>salary1</salary>
</row>
<row>
<name>name2</name> 
<salary>salary2</salary>
</row>

I am able to run the code to separate id's and get the output. But I am getting stuck at sending values as xml.
Code i tried:
procedure get_details(P_ID varchar2) as
begin
select * from emp where id in (
    select regexp_substr(P_ID,'[^,]+', 1, level) from dual 
    connect BY regexp_substr(P_ID, '[^,]+', 1, level) 
    is not null);
end get_values;

Any input will be appreciated.
Edit regarding solutions: Both the solutions provided in db-fiddle are working perfectly fine.
Solution 1:
CREATE PROCEDURE get_details(
  P_IDs IN varchar2
) IS
  v_clob CLOB;
BEGIN
  SELECT XMLELEMENT(
           "root",
           XMLAGG(
             XMLELEMENT(
               "row",
               XMLFOREST(
                 name AS "name",
                 salary AS "salary"
               )
             )
           )
         ).getClobVal()
  INTO   v_clob
  FROM   emp
  where  p_ids LIKE '%''' || id || '''%';
  
  DBMS_OUTPUT.PUT_LINE( v_clob );
end get_details;
/

BEGIN
  DBMS_OUTPUT.PUT_LINE( 'Your output:' );
  get_details( '''1'',''3''' );
END;
/

Solution 2:
create or replace procedure get_details(p_id varchar2) 
as
 lo_xml_output clob;
begin
  select to_clob(xmltype(cursor(select * 
                          from emp 
                         where id in 
                            (select regexp_substr(p_id,'[^,]+', 1, level) 
                               from dual 
                             connect by regexp_substr(P_ID, '[^,]+', 1,level) is 
                    not null))))
   into lo_xml_output from dual;
   DBMS_OUTPUT.PUT_LINE( lo_xml_output );
end get_details;
/

BEGIN
  DBMS_OUTPUT.PUT_LINE('');
  get_details('1,3');
END;
/


A: You can use the XML functions:
CREATE PROCEDURE get_details(
  P_IDs IN varchar2
) IS
  v_clob CLOB;
BEGIN
  SELECT XMLELEMENT(
           "root",
           XMLAGG(
             XMLELEMENT(
               "row",
               XMLFOREST(
                 name AS "name",
                 salary AS "salary"
               )
             )
           )
         ).getClobVal()
  INTO   v_clob
  FROM   emp
  where  p_ids LIKE '%''' || id || '''%';
  
  DBMS_OUTPUT.PUT_LINE( v_clob );
end get_details;
/

So, for the test data:
CREATE TABLE emp ( id, name, salary ) AS
SELECT 1, 'name1', 1000 FROM DUAL UNION ALL
SELECT 2, 'name2', 2000 FROM DUAL UNION ALL
SELECT 3, 'name3', 3000 FROM DUAL

The anonymous block:
BEGIN
  get_details( '''1'',''3''' );
END;
/

Outputs:


<root><row><name>name1</name><salary>1000</salary></row><row><name>name3</name><salary>3000</salary></row></root>


db<>fiddle here

A: Another way will be by using xmltype,you can pass a query as a cursor to xmltype.
create or replace procedure get_details(p_id varchar2) 
as
 lo_xml_output clob;
begin
  select to_clob(xmltype(cursor(select * 
                          from emp 
                         where id in 
                            (select regexp_substr(p_id,'[^,]+', 1, level) 
                               from dual 
                             connect by regexp_substr(P_ID, '[^,]+', 1,level) is 
                    not null))))
   into lo_xml_output from dual;
   DBMS_OUTPUT.PUT_LINE( lo_xml_output );
end get_details;
/

-- test
BEGIN
  DBMS_OUTPUT.PUT_LINE('');
  get_details('1,3');
END;
/

Db<>fiddle for your reference
P.S. we can also usedbms_xmlgen.getxml but then we need to build the SQL dynamically before passing it to dbms_xmlgen.getxml. you can see in the fiddle as well.
