how to get complete xml in one row

See more:

Oracle

hai all ,

i have one table with data and i need to generate xml into once clob file.
i have tried with stored procedure and it is working fine .
But my superior wants query to genrate xml.

i have tried with below query but it is giving one row of data into once xml ,plese find below output from query:
can any body please suggest the way.
thanks in advance.

query:

SELECT XMLCONCAT(XMLELEMENT("OBJECT_ID", E.OBJECT_ID),
   XMLELEMENT("OBJECT_NAME", E.OBJECT_NAME),XMLELEMENT("PURPOSE", E.OBJECT_PURPOSE)) AS "Result"
   FROM DA_DI_OBJECT_MASTER E
   WHERE E.OBJECT_TYPE ='DB'

output: -

<pre lang="css"><OBJECT_ID>1</OBJECT_ID><OBJECT_NAME>XDEV</OBJECT_NAME><PURPOSE>Xavient Internal DB</PURPOSE>
<OBJECT_ID>2</OBJECT_ID><OBJECT_NAME>CORP</OBJECT_NAME><PURPOSE>Reference Master Data to drive functionality in applications. No transactional data.</PURPOSE>
<OBJECT_ID>3</OBJECT_ID><OBJECT_NAME>SUB</OBJECT_NAME><PURPOSE>Dish’s Customer Database. Contains all transactional customer data. SUB schema contains all Account information.</PURPOSE>
<OBJECT_ID>4</OBJECT_ID><OBJECT_NAME>MKTG</OBJECT_NAME><PURPOSE>Legacy DB. Schemas will be migrated to CORP and SUB, or decommissioned</PURPOSE>






output from stored procedure:-


<ddoc>
<dblist>
<dbid>1</dbid>
<dbname>XDEV</dbname>
<dbpurpose>Xavient Internal DB</dbpurpose>
</dblist>
<dblist>
<dbid>2</dbid>
<dbname>CORP</dbname>
<dbpurpose>Reference Master Data to drive functionality in applications. No transactional data.</dbpurpose>
</dblist>
<dblist>
<dbid>3</dbid>
<dbname>SUB</dbname>
<dbpurpose>Dish Customer Database. Contains all transactional customer data. SUB schema contains all Account information.</dbpurpose>
</dblist>
<dblist>
<dbid>4</dbid>
<dbname>MKTG</dbname>
<dbpurpose>Legacy DB. Schemas will be migrated to CORP and SUB, or decommissioned</dbpurpose>
</dblist>
<dblist>
<dbid>5</dbid>
<dbname>ECO</dbname>
<dbpurpose>Legacy DB. eCommerce transactions. Migrate to SUB.</dbpurpose>
</dblist>
<dblist>
<dbid>6</dbid>
<dbname>COM</dbname>
<dbpurpose>Commissions Master DB.</dbpurpose>
</dblist>
<dblist>
<dbid>7</dbid>
<dbname>DIA</dbname>
<dbpurpose>Short term audit data storage for messaging data. Retention 30 days for ENI data.</dbpurpose>
</dblist>
<dblist>
<dbid>8</dbid>
<dbname>CCD</dbname>
<dbpurpose>Telephony related reference and transactional data.</dbpurpose>
</dblist>
<dblist>
<dbid>9</dbid>
<dbname>EPP</dbname>
<dbpurpose>Legacy DB. External Partner Payments Migrate to COM</dbpurpose>
</dblist>
<dblist>
<dbid>10</dbid>
<dbname>RD2</dbname>
<dbpurpose>Equipment Master Data.</dbpurpose>
</dblist>
<dblist>
<dbid>11</dbid>
<dbname>IVR</dbname>
<dbpurpose>Reference data used to drive the externally hosted Convergsys IVR application</dbpurpose>
</dblist>
<dblist>
<dbid>12</dbid>
<dbname>STBH</dbname>
<dbpurpose>Persist Set Top Box Health information for Application Usage</dbpurpose>
</dblist>
<dblist>
<dbid>13</dbid>
<dbname>BAM</dbname>
<dbpurpose>Business Activity Monitoring Repository  Primarily an COTS application with 1 custom schema</dbpurpose>
</dblist>
<dblist>
<dbid>14</dbid>
<dbname>RHY</dbname>
<dbpurpose>General purpose database used for IT support applications and logging</dbpurpose>
</dblist>
<dblist>
<dbid>15</dbid>
<dbname>CLFY</dbname>
<dbpurpose>Clarify application for Return Auths. SCLFY in Prod</dbpurpose>
</dblist>
<dblist>
<dbid>16</dbid>
<dbname>VANTAGE</dbname>
<dbpurpose>CSG hosted nightly snapshot of CSG Subscriber data</dbpurpose>
</dblist>
<dblist>
<dbid>17</dbid>
<dbname>ERP</dbname>
<dbpurpose>Oracle Enterprise Resource Planning Application</dbpurpose>
</dblist>
<dblist>
<dbid>18</dbid>
<dbname>EPC</dbname>
<dbpurpose>Enterprise Product Catalog Object Repository</dbpurpose>
</dblist>
<dblist>
<dbid>19</dbid>
<dbname>PRM</dbname>
<dbpurpose>Siebel Retailer Care Site</dbpurpose>
</dblist>
</ddoc>