Thanks for the question, Ankit .

Asked: June 08, 2011 - 11:40 pm UTC

Last updated: July 01, 2017 - 2:55 am UTC

Version: 10g

Viewed 10K+ times! This question is

You Asked

Hi,

My requirement is to generate XML output for different tables which can have different number of columns and for the same i am looking for a generic solution wherein i'll just have to put the table-name and XML out put of the table can be generated.

I am aware this can be achieved using different tools like Toad, but i was looking from code level perspective as well.

Best Regards,
Ankit

and Tom said...

https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB1600


ops$tkyte%ORA11GR2> select xmlelement( "Emp", xmlforest( empno, ename, hiredate, sal ) ) from scott.emp;

XMLELEMENT("EMP",XMLFOREST(EMPNO,ENAME,HIREDATE,SAL))
-------------------------------------------------------------------------------
<Emp><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><HIREDATE>1980-12-17</HIREDATE><SAL
>800</SAL></Emp>

<Emp><EMPNO>7499</EMPNO><ENAME>ALLEN</ENAME><HIREDATE>1981-02-20</HIREDATE><SAL
>1600</SAL></Emp>

<Emp><EMPNO>7521</EMPNO><ENAME>WARD</ENAME><HIREDATE>1981-02-22</HIREDATE><SAL>
1250</SAL></Emp>

<Emp><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><HIREDATE>1981-04-02</HIREDATE><SAL
>2975</SAL></Emp>

<Emp><EMPNO>7654</EMPNO><ENAME>MARTIN</ENAME><HIREDATE>1981-09-28</HIREDATE><SA
L>1250</SAL></Emp>

<Emp><EMPNO>7698</EMPNO><ENAME>BLAKE</ENAME><HIREDATE>1981-05-01</HIREDATE><SAL
>2850</SAL></Emp>

<Emp><EMPNO>7782</EMPNO><ENAME>CLARK</ENAME><HIREDATE>1981-06-09</HIREDATE><SAL
>2450</SAL></Emp>

<Emp><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><HIREDATE>1982-12-09</HIREDATE><SAL
>3000</SAL></Emp>

<Emp><EMPNO>7839</EMPNO><ENAME>KING</ENAME><HIREDATE>1981-11-17</HIREDATE><SAL>
5000</SAL></Emp>

<Emp><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><HIREDATE>1981-09-08</HIREDATE><SA
L>1500</SAL></Emp>

<Emp><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><HIREDATE>1983-01-12</HIREDATE><SAL
>1100</SAL></Emp>

<Emp><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><HIREDATE>1981-12-03</HIREDATE><SAL
>950</SAL></Emp>

<Emp><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><HIREDATE>1981-12-03</HIREDATE><SAL>
3000</SAL></Emp>

<Emp><EMPNO>7934</EMPNO><ENAME>MILLER</ENAME><HIREDATE>1982-01-23</HIREDATE><SA
L>1300</SAL></Emp>


14 rows selected.

is one approach

Rating

(5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Ankit Gupta, June 09, 2011 - 11:16 am UTC

Hi Tom,

Thank you for your esteemed guidance.

I have found another solution which may prove to be beneficial for others as well.

Please review the below query :

select dbms_xmlgen.getxml('select * from <tablename>') xml from dual;

This query gave me the complete XML structure output of the table along with its data and this was exactly what i was looking for.

Kindly provide your feedback on the same.

Best Regards,
Ankit

June 09, 2011 - 12:12 pm UTC

That's why I pointed you to that chapter - there are *dozens* if not *hundreds* of ways.

The two approaches generate different output - use whichever one makes the most sense for you.

Ankit Gupta, June 10, 2011 - 1:16 am UTC

While executing the above query through SQLPLUS in unix environment i got incomplete output. I have attached the output for your reference :

SQL> select dbms_xmlgen.getxml('select * from tt_state_config') xml from dual;

XML
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <REQUEST_ID>000000000000001</REQUEST_ID>


Interestingly i am getting the complete output when executing the same query in SQL Developer :

<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <REQUEST_ID>000000000000001</REQUEST_ID>
  <SUBMITTER>cj</SUBMITTER>
  <CREATE_DATE>985700966</CREATE_DATE>
  <LAST_MODIFIED_BY>cj</LAST_MODIFIED_BY>
  <MODIFIED_DATE>999182192</MODIFIED_DATE>
  <SYS_STATUS>0</SYS_STATUS>
  <SHORT_DESCRIPTION>x</SHORT_DESCRIPTION>
  <STATE>Queued</STATE>
  <STATE_ID>30</STATE_ID>
  <SORT_ORDER>3</SORT_ORDER>
 </ROW>
 <ROW>
  <REQUEST_ID>000000000000002</REQUEST_ID>
  <SUBMITTER>cj</SUBMITTER>
  <CREATE_DATE>985700976</CREATE_DATE>
  <LAST_MODIFIED_BY>cj</LAST_MODIFIED_BY>
  <MODIFIED_DATE>999182055</MODIFIED_DATE>
  <SYS_STATUS>0</SYS_STATUS>
  <SHORT_DESCRIPTION>x</SHORT_DESCRIPTION>
  <STATE>Open</STATE>
  <STATE_ID>40</STATE_ID>
  <SORT_ORDER>4</SORT_ORDER>
 </ROW>
 <ROW>
  <REQUEST_ID>000000000000003</REQUEST_ID>
  <SUBMITTER>cj</SUBMITTER>
  <CREATE_DATE>985700986</CREATE_DATE>
  <LAST_MODIFIED_BY>cj</LAST_MODIFIED_BY>
  <MODIFIED_DATE>999182050</MODIFIED_DATE>
  <SYS_STATUS>0</SYS_STATUS>
  <SHORT_DESCRIPTION>x</SHORT_DESCRIPTION>
  <STATE>Deferred</STATE>
  <STATE_ID>50</STATE_ID>
  <SORT_ORDER>5</SORT_ORDER>
 </ROW>
 <ROW>
  <REQUEST_ID>000000000000004</REQUEST_ID>
  <SUBMITTER>cj</SUBMITTER>
  <CREATE_DATE>985700995</CREATE_DATE>
  <LAST_MODIFIED_BY>cj</LAST_MODIFIED_BY>
  <MODIFIED_DATE>999182045</MODIFIED_DATE>
  <SYS_STATUS>0</SYS_STATUS>
  <SHORT_DESCRIPTION>x</SHORT_DESCRIPTION>
  <STATE>Cleared</STATE>
  <STATE_ID>60</STATE_ID>
  <SORT_ORDER>6</SORT_ORDER>
 </ROW>
 <ROW>
  <REQUEST_ID>000000000000005</REQUEST_ID>
  <SUBMITTER>cj</SUBMITTER>
  <CREATE_DATE>985701008</CREATE_DATE>
  <LAST_MODIFIED_BY>cj</LAST_MODIFIED_BY>
  <MODIFIED_DATE>999182028</MODIFIED_DATE>
  <SYS_STATUS>0</SYS_STATUS>
  <SHORT_DESCRIPTION>x</SHORT_DESCRIPTION>
  <STATE>Closed</STATE>
  <STATE_ID>70</STATE_ID>
  <SORT_ORDER>7</SORT_ORDER>
 </ROW>
 <ROW>
  <REQUEST_ID>000000000000006</REQUEST_ID>
  <SUBMITTER>cj</SUBMITTER>
  <CREATE_DATE>999182215</CREATE_DATE>
  <LAST_MODIFIED_BY>cj</LAST_MODIFIED_BY>
  <MODIFIED_DATE>999182215</MODIFIED_DATE>
  <SYS_STATUS>0</SYS_STATUS>
  <SHORT_DESCRIPTION>x</SHORT_DESCRIPTION>
  <STATE>New</STATE>
  <STATE_ID>10</STATE_ID>
  <SORT_ORDER>1</SORT_ORDER>
 </ROW>
</ROWSET>


I beleive some parameter needs to be set in unix environment before running the query.

Kindly advise.

Best Regards,
Ankit

June 10, 2011 - 8:00 am UTC

SQL> set long nnnnnnnnnnnnnnn

where nnnnnnnnnnnnn is the size sqlplus should print of a lob/long variable. Default is pretty small

@Ankit

Narendra, June 10, 2011 - 4:57 am UTC

Ankit,

In SQL*plus, you need to issue following

set long 100000

and then execute your query, which should produce the expected results.

Ankit Gupta, June 13, 2011 - 4:27 am UTC

Thanks Tom & Narendra,

I was able to successfully create and XML file.

I have to insert this xml into a table with same structure and I am using the following procedure :

create or replace procedure insProc(xmlDoc IN CLOB, tableName IN VARCHAR2) is
   insCtx DBMS_XMLSave.ctxType;
   rows number;
 begin
    insCtx := DBMS_XMLSave.newContext(tableName);
    rows := DBMS_XMLSave.insertXML(insCtx,xmlDoc);
    DBMS_XMLSave.closeContext(insCtx);
end;
/


I am executing the statement with the following statement :

exec insProc('ttstateconfig.xml', 'test1');

where ttstateconfig.xml resides in the same directory where SQLPLUS session was initiated.

AND test1 has the same structure of table tt_state_config :


SQL> desc test1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 REQUEST_ID                                NOT NULL VARCHAR2(15)
 SUBMITTER                                          VARCHAR2(254)
 CREATE_DATE                               NOT NULL NUMBER(15)
 ASSIGNED_TO                                        VARCHAR2(254)
 LAST_MODIFIED_BY                          NOT NULL VARCHAR2(254)
 MODIFIED_DATE                             NOT NULL NUMBER(15)
 SYS_STATUS                                NOT NULL NUMBER(15)
 SHORT_DESCRIPTION                         NOT NULL VARCHAR2(1)
 ASSIGNEE_GROUP                                     VARCHAR2(30)
 STATE                                              VARCHAR2(30)
 STATE_ID                                           NUMBER(15)
 SORT_ORDER                                         NUMBER(15)
 DESCRIPTION                                        VARCHAR2(255)


When i execute the procedure i am getting the following error :

SQL> exec insProc('ttstateconfig.xml', 'test1');
BEGIN insProc('ttstateconfig.xml', 'test1'); END;

*
ERROR at line 1:
ORA-29532: Java call terminated by uncaught Java exception:
oracle.xml.sql.OracleXMLSQLException: Start of root element expected.
ORA-06512: at "SYS.DBMS_XMLSAVE", line 115
ORA-06512: at "ARADMIN.INSPROC", line 6
ORA-06512: at line 1
 
Interestingly if i use XMLStore in place of XMLSave in the procedure :

create or replace procedure insProc(xmlDoc IN CLOB, tableName IN VARCHAR2) is
   insCtx DBMS_XMLStore.ctxType;
   rows number;
 begin
    insCtx := DBMS_XMLStore.newContext(tableName);
    rows := DBMS_XMLStore.insertXML(insCtx,xmlDoc);
    DBMS_XMLStore.closeContext(insCtx);
end;
/


I get a different ouput with errors :

SQL> exec insProc('ttstateconfig.xml', 'test1');
BEGIN insProc('ttstateconfig.xml', 'test1'); END;

*
ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00210: expected '<' instead of 't'
ORA-06512: at "SYS.DBMS_XMLSTORE", line 78
ORA-06512: at "ARADMIN.INSPROC", line 6
ORA-06512: at line 1


Kindly advise on how this problem can be resolved.

Best Regards,
Ankit Gupta

The link in this answer is no longer valid

Topher Eliot, June 30, 2017 - 6:45 pm UTC

In the original response, the link included now gets a 404. The link is:

http://docs.oracle.com/docs/cd/E11882_01/appdev.112/e16659/xdb13gen.htm#g1047191

July 01, 2017 - 2:55 am UTC

Thanks for letting us know.

Here's an updated link

https://docs.oracle.com/cd/E11882_01/appdev.112/e23094/xdb13gen.htm#ADXDB1600

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

@Ankit

The link in this answer is no longer valid

More to Explore

Design

Generate XML output of complete table

Breadcrumb

Question and Answer

You Asked

and Tom said...

Rating

Comments

@Ankit

The link in this answer is no longer valid

More to Explore

Design