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.

"I was thing to use the package dbms_xmlgen but I am clueless how to achieve the above." So read the doc, try something and if it doesn't work, post a specific question. — The Archetypal Paul
– The Archetypal Paul, Commented Sep 25, 2017 at 12:57
I suggest using another programming language to convert your query results to xml. — Dan Bracuk
– Dan Bracuk, Commented Sep 25, 2017 at 13:06
@DanBracuk Why use another language when Oracle can easily do the transformation? — MT0
– MT0, Commented Sep 25, 2017 at 13:21

MT0 · Accepted Answer · 2017-09-25 13:20:15Z

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

Collectives™ on Stack Overflow

gernerate XML file format from oracle Table

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related