gernerate XML file format from oracle Table

Question

I want a XML file format as below.

XML should look like below:-

<server name="Server123">
<schema name="cwmm">
<table name="ACC" rows="1000000"/>
<table name="KEYS" rows="1000000"/>
</schema>
<schema name="CWM1610">
<table name="ACC" rows="1000000"/>
<table name="KEYS" rows="1000000"/>
</schema>

what, I have done is created a temporary table called TAB_INFO with the information about the table_owner, tables and their rows. so please help in achieving the above format. I was thinking to use the package dbms_xmlgen but I am clueless how to achieve the above.

Table has columns as below:-

owner
table_name
num_rows

Thank you for the help.

Answer 1

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE TAB_INFO (owner, table_name, num_rows ) AS
SELECT 'cwmm',    'ACC',  1000000 FROM DUAL UNION ALL
SELECT 'cwmm',    'KEYS', 1000000 FROM DUAL UNION ALL
SELECT 'CWM1610', 'ACC',  1000000 FROM DUAL UNION ALL
SELECT 'CWM1610', 'KEYS', 1000000 FROM DUAL;

Query 1:

SELECT XMLElement(
         "server",
         XMLAttributes( 'Server123' AS "name" ),
         XMLAGG( schema )
       ).getClobVal() AS xml
FROM   (
  SELECT XMLElement(
           "schema",
           XMLAttributes( owner AS "name" ),
           XMLAgg(
             XMLElement(
               "table",
               XMLAttributes(
                 table_name AS "name",
                 num_rows AS "rows"
               )
             )
           )
         ) AS schema
  FROM   tab_info
  GROUP BY owner
)

Results:

|                                                                                                                                                                                                                                                                   XML |
|-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------|
| <server name="Server123"><schema name="CWM1610"><table name="ACC" rows="1000000"></table><table name="KEYS" rows="1000000"></table></schema><schema name="cwmm"><table name="ACC" rows="1000000"></table><table name="KEYS" rows="1000000"></table></schema></server> |