converting output of a Query into xml

Question

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;
/

MT0 · Accepted Answer · 2020-09-28 12:40:58Z

1

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

answered Sep 28, 2020 at 12:40

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

prudhvi bikumalla Over a year ago

I tried with the columns in my table. I am not getting any output. Here is the URL for my fiddle. dbfiddle.uk/… can you please help me where I am going wrong?

MT0 Over a year ago

@prudhvibikumalla db<>fiddle.

Sujitmohanty30 · Accepted Answer · 2020-09-28 14:00:44Z

0

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.

answered Sep 28, 2020 at 14:00

Sujitmohanty30

3,3362 gold badges9 silver badges24 bronze badges

3 Comments

Sujitmohanty30 Over a year ago

@MT0, I have used the dbfiddle you have created. Thank you

prudhvi bikumalla Over a year ago

Thank you for the answer @Sujitmohanty30. But MTO has answered it first. So I had to accept his answer. But I had edited my question with both of your solutions

Sujitmohanty30 Over a year ago

@prudhvi bikumalla, no problem and I give credit to you because you think this way where many people in SO doesn't. Give an up-vote when you have enough reputation which will make me happy too.. :D..cheers..

Collectives™ on Stack Overflow

converting output of a Query into xml

2 Answers 2

2 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related