CODEWITHSUNDEEP
xmloracle-databaseplsqlsqlplus
Cal
Cal

Reputation: 867

plsql dbms_xmlgen.getxmltype change xml tag format

We have been using SQL developer's database export function to create some data dump files. The file format looks like this:

<?xml version='1.0'  encoding='Cp1252' ?>
<RESULTS>
  <ROW>
      <COLUMN NAME="A_ID"><![CDATA[3]]></COLUMN>
      <COLUMN NAME="B_ID"><![CDATA[A3V09LJAF]]></COLUMN>
  </ROW>
  <ROW>
      <COLUMN NAME="A_ID"><![CDATA[5]]></COLUMN>
      <COLUMN NAME="B_ID"><![CDATA[4FAPU90FSD]]></COLUMN>
  </ROW>
</RESULTS>

But recently the management decided to automate the process, one option is to use spool:

SPOOL 'D:\SOME\PATH\TO\DIR\1.XML'
SELECT dbms_xmlgen.getxmltype('SELECT A_ID, B_ID FROM SOME_TABLE') FROM DUAL;
SPOOL OFF;

However the dbms_xmlgen.getxmltype gives different tag type in the exported file:

 <ROWSET>
   <ROW>
     <A_ID>3</A_ID>
     <B_ID>A3V09LJAF</B_ID>
   </ROW>
   <ROW>
     <A_ID>5</A_ID>
     <B_ID>4FAPU90FSD</B_ID>
   </ROW>
 </ROWSET>

Is there a way to change the TAG format and add the CDATA to be exact as SQL developer export file?

Thank you for your time.

Upvotes: 1

Views: 328

Answers (1)

Connor McDonald
Connor McDonald

Reputation: 11616

Perhaps an easier way would be use SQLcl, which has the same "engine" as SQL Developer. Thus in SQLcl you would do this

SQL> SET SQLFORMAT XML
SQL> SELECT * FROM emp WHERE deptno = 20;
<?xml version='1.0'  encoding='UTF8' ?>
<RESULTS>
        <ROW>
                <COLUMN NAME="EMPNO"><![CDATA[7369]]></COLUMN>
                <COLUMN NAME="ENAME"><![CDATA[SMITH]]></COLUMN>
                <COLUMN NAME="JOB"><![CDATA[CLERK]]></COLUMN>
                <COLUMN NAME="MGR"><![CDATA[7902]]></COLUMN>
                <COLUMN NAME="HIREDATE"><![CDATA[17-DEC-80]]></COLUMN>
                <COLUMN NAME="SAL"><![CDATA[800]]></COLUMN>
                <COLUMN NAME="COMM"><![CDATA[]]></COLUMN>
                <COLUMN NAME="DEPTNO"><![CDATA[20]]></COLUMN>
        </ROW>

Download SQLcl from here

https://www.oracle.com/au/tools/downloads/sqlcl-downloads.html

Upvotes: 2

Related Questions