Wrap collection to XMLTYPE in PL/SQL (Oracle)

Question

smart guys!

Lets say I have a table:

id NUMBER     name VARCHAR2       info XMLTYPE
--
1             Brad              <?xml version="1.0" encoding="ISO-8859-5"?>
2             Angelina          <?xml version="1.0" encoding="ISO-8859-5"?>

In my package I can select it to some collection variable v_table.

And I need to convert it to XML and return XMLTYPE, like this:

<?xml version="1.0" encoding="ISO-8859-5"?>
<rows>
    <row>
        <id type="NUMBER">1</id>
        <name type="VARCHAR2">Brad</name>
        <info type="XMLTYPE"><!-- somehow insert here xml, maybe base64 encoded --></info>
    </row>
    <row>
        <id type="NUMBER">2</id>
        <name type="VARCHAR2">Angelina</name>
        <info type="XMLTYPE"><!-- somehow insert here xml, maybe base64 encoded --></info>
    </row>
</rows>

So I need to make some universal function to convert any collection to XML. Unfortunately I don't know PL/SQL (only MySQL, MsSQL), and I just don't know the right way to start. Could you give me some advice?

PS: "type" attribute is not required, but would be cool.

Answer 1

Thanks, Jon Heller!

The package DBMS_XMLGEN already does that. For example: select dbms_xmlgen.getxml('select * from dual') from dual;. Then you could perhaps apply a stylesheet to the results through the XMLType function TRANSFORM.