OraFAQ Forum: JDeveloper, Java & XML » Generate XML for table definiton

Home » Developer & Programmer » JDeveloper, Java & XML » Generate XML for table definiton (Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Generate XML for table definiton [message #660319]

Tue, 14 February 2017 15:12

dba_vijay
Messages: 11
Registered: February 2009

Junior Member

Hi Experts
I am trying to generate XMLs of table definitions.
I know how to use functions like XMLGEN for e.g. select dbms_xmlgen.getxml('select * from data_files') xml from dual;

But I want the describe table command output in an xml.
for example

Every tag below is a column in a table.

<?xml version="1.0" encoding="UTF-8"?>
<DOC_ID> 1</DOC_ID>
<COPY_ID> 1</COPY_ID>
<COPY_NUMBER> 1</COPY_NUMBER
<FORM_ABF> 1</FORM_ABF>
<FORM_TYPE> 1</FORM_TYPE>
<BILLING> 1</BILLING>
<CREATED_BY> 1</CREATED_BY>
<CREATED_DT> 1</CREATED_DT>
<UPDATED_BY> 1</UPDATED_BY>
<UPDATED_DT> 1</UPDATED_DT>

All inputs are appreciated.

Report message to a moderator

Re: Generate XML for table definiton [message #660321 is a reply to message #660319]

Tue, 14 February 2017 16:34

Barbara Boehmer
Messages: 9097
Registered: November 2002
Location: California, USA

Senior Member

I am not sure exactly what you are looking for.

-- Suppose you have a table like the dept table below:

SCOTT@orcl_12.1.0.2.0> describe dept
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 DEPTNO                                    NOT NULL NUMBER(2)
 DNAME                                              VARCHAR2(14)
 LOC                                                VARCHAR2(13)

SCOTT@orcl_12.1.0.2.0> select * from dept
  2  /

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

4 rows selected.

-- you say you already know how to do this:

SCOTT@orcl_12.1.0.2.0> select dbms_xmlgen.getxml('select * from dept') xml from dual
  2  /

XML
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <LOC>NEW YORK</LOC>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <LOC>DALLAS</LOC>
 </ROW>
 <ROW>
  <DEPTNO>30</DEPTNO>
  <DNAME>SALES</DNAME>
  <LOC>CHICAGO</LOC>
 </ROW>
 <ROW>
  <DEPTNO>40</DEPTNO>
  <DNAME>OPERATIONS</DNAME>
  <LOC>BOSTON</LOC>
 </ROW>
</ROWSET>


1 row selected.

-- but you want the describe data, like this?:

SCOTT@orcl_12.1.0.2.0> select dbms_xmlgen.getxml
  2  	      ('select column_name, nullable, data_type
  3  		from   user_tab_columns
  4  		where  table_name = ''DEPT''
  5  		order  by column_id')
  6  from   dual
  7  /

DBMS_XMLGEN.GETXML('SELECTCOLUMN_NAME,NULLABLE,DATA_TYPEFROMUSER_TAB_COLUMNSWHER
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <COLUMN_NAME>DEPTNO</COLUMN_NAME>
  <NULLABLE>N</NULLABLE>
  <DATA_TYPE>NUMBER</DATA_TYPE>
 </ROW>
 <ROW>
  <COLUMN_NAME>DNAME</COLUMN_NAME>
  <NULLABLE>Y</NULLABLE>
  <DATA_TYPE>VARCHAR2</DATA_TYPE>
 </ROW>
 <ROW>
  <COLUMN_NAME>LOC</COLUMN_NAME>
  <NULLABLE>Y</NULLABLE>
  <DATA_TYPE>VARCHAR2</DATA_TYPE>
 </ROW>
</ROWSET>


1 row selected.

-- or like this?:

SCOTT@orcl_12.1.0.2.0> select xml
  2  from   (select '<?xml version="1.0" encoding="UTF-8"?>' xml, 0 column_id
  3  	     from   dual
  4  	     union all
  5  	     select '<' || column_name || '>' || column_id || '</' || column_name || '>' xml, column_id
  6  	     from   user_tab_columns
  7  	     where  table_name = 'DEPT')
  8  order  by column_id
  9  /

XML
--------------------------------------------------------------------------------
<?xml version="1.0" encoding="UTF-8"?>
<DEPTNO>1</DEPTNO>
<DNAME>2</DNAME>
<LOC>3</LOC>

4 rows selected.

or something else?

It might help if you describe the purpose.

Report message to a moderator

Re: Generate XML for table definiton [message #660322 is a reply to message #660321]

Tue, 14 February 2017 16:47

dba_vijay
Messages: 11
Registered: February 2009

Junior Member

Hi Barbara
thanks for the reply.
What I was looking for is your last query.
I was doing this manual approach but my intentions were to find if Oracle has any functions to do generate XML for SCHEMA definitions, for e.g. I was looking to generate XML for table.
and I guess not.

Thanks for your time Smile

Vijay

Report message to a moderator

Re: Generate XML for table definiton [message #660331 is a reply to message #660322]

Wed, 15 February 2017 01:06

Michel Cadot
Messages: 68683
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

my intentions were to find if Oracle has any functions to do generate XML for SCHEMA definitions

It depends on what you mean by this.
Oracle has it (and use it in expdp/impdp), but is this what you want?
Example for table DEPT:

SQL> select dbms_metadata.get_xml('TABLE','DEPT') from dual;
DBMS_METADATA.GET_XML('TABLE','DEPT')
---------------------------------------------------------------------------------
<?xml version="1.0"?><ROWSET><ROW>
  <TABLE_T>
 <VERS_MAJOR>1</VERS_MAJOR>
 <VERS_MINOR>3 </VERS_MINOR>
 <OBJ_NUM>78607</OBJ_NUM>
 <SCHEMA_OBJ>
  <OBJ_NUM>78607</OBJ_NUM>
  <DATAOBJ_NUM>78607</DATAOBJ_NUM>
  <OWNER_NUM>73</OWNER_NUM>
  <OWNER_NAME>MICHEL</OWNER_NAME>
  <NAME>DEPT</NAME>
  <NAMESPACE>1</NAMESPACE>
  <TYPE_NUM>2</TYPE_NUM>
  <TYPE_NAME>TABLE</TYPE_NAME>
  <CTIME>2011-01-27 20:59:38</CTIME>
  <MTIME>2016-11-26 11:39:49</MTIME>
  <STIME>2012-08-18 22:10:39</STIME>
  <STATUS>1</STATUS>
  <FLAGS>0</FLAGS>
  <SPARE1>6</SPARE1>
  <SPARE2>1</SPARE2>
  <SPARE3>73</SPARE3>
  <OWNER_NAME2>MICHEL</OWNER_NAME2>
 </SCHEMA_OBJ>
 <STORAGE>
  <FILE_NUM>4</FILE_NUM>
  <BLOCK_NUM>10610</BLOCK_NUM>
  <TYPE_NUM>5</TYPE_NUM>
  <TS_NUM>4</TS_NUM>
  <TRANSPORTABLE>0</TRANSPORTABLE>
  <BLOCKS>8</BLOCKS>
  <EXTENTS>1</EXTENTS>
  <INIEXTS>8</INIEXTS>
  <MINEXTS>1</MINEXTS>
  <MAXEXTS>2147483645</MAXEXTS>
  <EXTSIZE>128</EXTSIZE>
  <EXTPCT>0</EXTPCT>
  <USER_NUM>73</USER_NUM>
  <LISTS>1</LISTS>
  <GROUPS>1</GROUPS>
  <BITMAPRANGES>2147483645</BITMAPRANGES>
  <CACHEHINT>0</CACHEHINT>
  <SCANHINT>0</SCANHINT>
  <HWMINCR>78607</HWMINCR>
  <FLAGS>4325633</FLAGS>
 </STORAGE>
 <TS_NAME>TS_D01</TS_NAME>
 <BLOCKSIZE>8192</BLOCKSIZE>
 <DATAOBJ_NUM>78607</DATAOBJ_NUM>
 <COLS>3</COLS>
 <PCT_FREE>10</PCT_FREE>
 <PCT_USED>40</PCT_USED>
 <INITRANS>1</INITRANS>
 <MAXTRANS>255</MAXTRANS>
 <FLAGS>1073742353</FLAGS>
 <AUDIT_VAL>--------------------------------------</AUDIT_VAL>
 <ROWCNT>4</ROWCNT>
 <BLKCNT>4</BLKCNT>
 <EMPCNT>0</EMPCNT>
 <AVGSPC>0</AVGSPC>
 <CHNCNT>0</CHNCNT>
 <AVGRLN>20</AVGRLN>
 <AVGSPC_FLB>0</AVGSPC_FLB>
 <FLBCNT>0</FLBCNT>
 <ANALYZETIME>2012/08/18 22:11:04</ANALYZETIME>
 <SAMPLESIZE>4</SAMPLESIZE>
 <INTCOLS>3</INTCOLS>
 <KERNELCOLS>3</KERNELCOLS>
 <PROPERTY>536870912</PROPERTY>
 <PROPERTY2>0</PROPERTY2>
 <XMLSCHEMACOLS>N</XMLSCHEMACOLS>
 <TSTZ_COLS>N</TSTZ_COLS>
 <TRIGFLAG>0</TRIGFLAG>
 <SPARE1>736</SPARE1>
 <SPARE6>2012/08/18 20:10:39</SPARE6>
 <COL_LIST>
  <COL_LIST_ITEM>
   <OBJ_NUM>78607</OBJ_NUM>
   <COL_NUM>1</COL_NUM>
   <INTCOL_NUM>1</INTCOL_NUM>
   <SEGCOL_NUM>1</SEGCOL_NUM>
   <PROPERTY>0</PROPERTY>
   <NAME>DEPTNO</NAME>
   <TYPE_NUM>2</TYPE_NUM>
   <LENGTH>22</LENGTH>
   <PRECISION_NUM>2</PRECISION_NUM>
   <SCALE>0</SCALE>
   <NOT_NULL>1</NOT_NULL>
   <CHARSETID>0</CHARSETID>
   <CHARSETFORM>0</CHARSETFORM>
   <BASE_INTCOL_NUM>1</BASE_INTCOL_NUM>
   <BASE_COL_TYPE>0</BASE_COL_TYPE>
   <SPARE1>0</SPARE1>
   <SPARE2>0</SPARE2>
   <SPARE3>0</SPARE3>
  </COL_LIST_ITEM>
  <COL_LIST_ITEM>
   <OBJ_NUM>78607</OBJ_NUM>
   <COL_NUM>2</COL_NUM>
   <INTCOL_NUM>2</INTCOL_NUM>
   <SEGCOL_NUM>2</SEGCOL_NUM>
   <PROPERTY>8388608</PROPERTY>
   <NAME>DNAME</NAME>
   <TYPE_NUM>1</TYPE_NUM>
   <LENGTH>14</LENGTH>
   <NOT_NULL>1</NOT_NULL>
   <CHARSETID>178</CHARSETID>
   <CHARSETFORM>1</CHARSETFORM>
   <BASE_INTCOL_NUM>2</BASE_INTCOL_NUM>
   <BASE_COL_TYPE>0</BASE_COL_TYPE>
   <CON>
    <OWNER_NUM>73</OWNER_NUM>
    <NAME>SYS_C0023957</NAME>
    <CON_NUM>23957</CON_NUM>
    <OBJ_NUM>78607</OBJ_NUM>
    <NUMCOLS>1</NUMCOLS>
    <CONTYPE>7</CONTYPE>
    <ENABLED>1</ENABLED>
    <INTCOLS>1</INTCOLS>
    <MTIME>2012/08/18 22:10:39</MTIME>
    <FLAGS>12</FLAGS>
   </CON>
   <SPARE1>0</SPARE1>
   <SPARE2>0</SPARE2>
   <SPARE3>14</SPARE3>
  </COL_LIST_ITEM>
  <COL_LIST_ITEM>
   <OBJ_NUM>78607</OBJ_NUM>
   <COL_NUM>3</COL_NUM>
   <INTCOL_NUM>3</INTCOL_NUM>
   <SEGCOL_NUM>3</SEGCOL_NUM>
   <PROPERTY>8388608</PROPERTY>
   <NAME>LOC</NAME>
   <TYPE_NUM>1</TYPE_NUM>
   <LENGTH>13</LENGTH>
   <NOT_NULL>0</NOT_NULL>
   <CHARSETID>178</CHARSETID>
   <CHARSETFORM>1</CHARSETFORM>
   <BASE_INTCOL_NUM>3</BASE_INTCOL_NUM>
   <BASE_COL_TYPE>0</BASE_COL_TYPE>
   <SPARE1>0</SPARE1>
   <SPARE2>0</SPARE2>
   <SPARE3>13</SPARE3>
  </COL_LIST_ITEM>
 </COL_LIST>
 <CON0_LIST/>
 <CON1_LIST>
  <CON1_LIST_ITEM>
   <OWNER_NUM>73</OWNER_NUM>
   <NAME>DEPT_PK</NAME>
   <CON_NUM>23953</CON_NUM>
   <OBJ_NUM>78607</OBJ_NUM>
   <PROPERTY>536870912</PROPERTY>
   <NUMCOLS>1</NUMCOLS>
   <CONTYPE>2</CONTYPE>
   <ENABLED>92640</ENABLED>
   <INTCOLS>1</INTCOLS>
   <MTIME>2012/08/18 22:08:56</MTIME>
   <FLAGS>4</FLAGS>
   <OID_OR_SETID>0</OID_OR_SETID>
   <COL_LIST>
    <COL_LIST_ITEM>
     <CON_NUM>23953</CON_NUM>
     <OBJ_NUM>78607</OBJ_NUM>
     <INTCOL_NUM>1</INTCOL_NUM>
     <POS_NUM>1</POS_NUM>
     <SPARE1>0</SPARE1>
     <OID_OR_SETID>0</OID_OR_SETID>
     <COL>
      <OBJ_NUM>78607</OBJ_NUM>
      <COL_NUM>1</COL_NUM>
      <INTCOL_NUM>1</INTCOL_NUM>
      <SEGCOL_NUM>1</SEGCOL_NUM>
      <PROPERTY>0</PROPERTY>
      <NAME>DEPTNO</NAME>
      <TYPE_NUM>2</TYPE_NUM>
     </COL>
    </COL_LIST_ITEM>
   </COL_LIST>
   <IND>
    <VERS_MAJOR>1</VERS_MAJOR>
    <VERS_MINOR>5 </VERS_MINOR>
    <OBJ_NUM>92640</OBJ_NUM>
    <SCHEMA_OBJ>
     <OBJ_NUM>92640</OBJ_NUM>
     <DATAOBJ_NUM>92640</DATAOBJ_NUM>
     <OWNER_NUM>73</OWNER_NUM>
     <OWNER_NAME>MICHEL</OWNER_NAME>
     <NAME>DEPT_PK</NAME>
     <NAMESPACE>4</NAMESPACE>
     <TYPE_NUM>1</TYPE_NUM>
     <TYPE_NAME>INDEX</TYPE_NAME>
     <CTIME>2012-08-18 22:08:56</CTIME>
     <MTIME>2012-08-18 22:08:56</MTIME>
     <STIME>2012-08-18 22:08:56</STIME>
     <STATUS>1</STATUS>
     <FLAGS>0</FLAGS>
     <SPARE1>0</SPARE1>
     <SPARE2>65535</SPARE2>
     <SPARE3>73</SPARE3>
     <OWNER_NAME2>MICHEL</OWNER_NAME2>
    </SCHEMA_OBJ>
    <COL_LIST>
     <COL_LIST_ITEM>
      <OBJ_NUM>92640</OBJ_NUM>
      <BO_NUM>78607</BO_NUM>
      <INTCOL_NUM>1</INTCOL_NUM>
      <COL>
       <OBJ_NUM>78607</OBJ_NUM>
       <COL_NUM>1</COL_NUM>
       <INTCOL_NUM>1</INTCOL_NUM>
       <SEGCOL_NUM>1</SEGCOL_NUM>
       <PROPERTY>0</PROPERTY>
       <NAME>DEPTNO</NAME>
       <TYPE_NUM>2</TYPE_NUM>
      </COL>
      <POS_NUM>1</POS_NUM>
      <SEGCOL_NUM>0</SEGCOL_NUM>
      <SEGCOLLEN>0</SEGCOLLEN>
      <OFFSET>0</OFFSET>
      <FLAGS>0</FLAGS>
      <SPARE2>0</SPARE2>
      <SPARE3>0</SPARE3>
      <OID_OR_SETID>0</OID_OR_SETID>
     </COL_LIST_ITEM>
    </COL_LIST>
    <TS_NAME>TS_D01</TS_NAME>
    <BLOCKSIZE>8192</BLOCKSIZE>
    <STORAGE>
     <FILE_NUM>4</FILE_NUM>
     <BLOCK_NUM>10978</BLOCK_NUM>
     <TYPE_NUM>6</TYPE_NUM>
     <TS_NUM>4</TS_NUM>
     <TRANSPORTABLE>0</TRANSPORTABLE>
     <BLOCKS>8</BLOCKS>
     <EXTENTS>1</EXTENTS>
     <INIEXTS>8</INIEXTS>
     <MINEXTS>1</MINEXTS>
     <MAXEXTS>2147483645</MAXEXTS>
     <EXTSIZE>128</EXTSIZE>
     <EXTPCT>0</EXTPCT>
     <USER_NUM>73</USER_NUM>
     <LISTS>1</LISTS>
     <GROUPS>1</GROUPS>
     <BITMAPRANGES>2147483645</BITMAPRANGES>
     <CACHEHINT>0</CACHEHINT>
     <SCANHINT>0</SCANHINT>
     <HWMINCR>92640</HWMINCR>
     <FLAGS>4325633</FLAGS>
    </STORAGE>
    <DATAOBJ_NUM>92640</DATAOBJ_NUM>
    <BASE_OBJ_NUM>78607</BASE_OBJ_NUM>
    <BASE_OBJ>
     <OBJ_NUM>78607</OBJ_NUM>
     <DATAOBJ_NUM>78607</DATAOBJ_NUM>
     <OWNER_NUM>73</OWNER_NUM>
     <OWNER_NAME>MICHEL</OWNER_NAME>
     <NAME>DEPT</NAME>
     <NAMESPACE>1</NAMESPACE>
     <TYPE_NUM>2</TYPE_NUM>
     <TYPE_NAME>TABLE</TYPE_NAME>
     <CTIME>2011-01-27 20:59:38</CTIME>
     <MTIME>2016-11-26 11:39:49</MTIME>
     <STIME>2012-08-18 22:10:39</STIME>
     <STATUS>1</STATUS>
     <FLAGS>0</FLAGS>
     <SPARE1>6</SPARE1>
     <SPARE2>1</SPARE2>
     <SPARE3>73</SPARE3>
     <OWNER_NAME2>MICHEL</OWNER_NAME2>
    </BASE_OBJ>
    <ANC_OBJ>
     <OBJ_NUM>78607</OBJ_NUM>
     <DATAOBJ_NUM>78607</DATAOBJ_NUM>
     <OWNER_NUM>73</OWNER_NUM>
     <OWNER_NAME>MICHEL</OWNER_NAME>
     <NAME>DEPT</NAME>
     <NAMESPACE>1</NAMESPACE>
     <TYPE_NUM>2</TYPE_NUM>
     <TYPE_NAME>TABLE</TYPE_NAME>
     <CTIME>2011-01-27 20:59:38</CTIME>
     <MTIME>2016-11-26 11:39:49</MTIME>
     <STIME>2012-08-18 22:10:39</STIME>
     <STATUS>1</STATUS>
     <FLAGS>0</FLAGS>
     <SPARE1>6</SPARE1>
     <SPARE2>1</SPARE2>
     <SPARE3>73</SPARE3>
     <OWNER_NAME2>MICHEL</OWNER_NAME2>
    </ANC_OBJ>
    <INDMETHOD_NUM>0</INDMETHOD_NUM>
    <COLS>1</COLS>
    <PCT_FREE>10</PCT_FREE>
    <INITRANS>2</INITRANS>
    <MAXTRANS>255</MAXTRANS>
    <TYPE_NUM>1</TYPE_NUM>
    <FLAGS>2050</FLAGS>
    <PROPERTY>4097</PROPERTY>
    <BLEVEL>0</BLEVEL>
    <LEAFCNT>1</LEAFCNT>
    <DISTKEY>4</DISTKEY>
    <LBLKKEY>1</LBLKKEY>
    <DBLKKEY>1</DBLKKEY>
    <CLUFAC>1</CLUFAC>
    <ANALYZETIME>2012/08/18 22:11:04</ANALYZETIME>
    <SAMPLESIZE>4</SAMPLESIZE>
    <ROWCNT>4</ROWCNT>
    <INTCOLS>1</INTCOLS>
    <NUMCOLSDEP>1</NUMCOLSDEP>
    <SPARE6>2012/08/18 20:08:56</SPARE6>
    <FOR_PKOID>0</FOR_PKOID>
    <FOR_REFPAR>0</FOR_REFPAR>
    <OID_OR_SETID>0</OID_OR_SETID>
   </IND>
  </CON1_LIST_ITEM>
 </CON1_LIST>
 <CON2_LIST/>
 <REFPAR_LEVEL>0</REFPAR_LEVEL>
</TABLE_T>
</ROW></ROWSET>

You can choose more or less information you want from the object setting DBMS_METADATA options.

[Updated on: Wed, 15 February 2017 01:07]

Report message to a moderator

Previous Topic:	Calling Java function from pl/sql
Next Topic:	Converting XML Data to Rows.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Sep 15 18:55:19 CDT 2024