Home » Developer & Programmer » JDeveloper, Java & XML » How can I define formatting rules for XML output while using dbms_xmlgen? (Oracle 11.2.0.4 on Linux)
How can I define formatting rules for XML output while using dbms_xmlgen? [message #637191] Tue, 12 May 2015 09:12 Go to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello

In the following example we get XML output with the Elements arranged (parent - child) + Indented, as we used co-related query using CURSOR

select dbms_xmlgen.getxml('
    select deptno, dname,
    cursor(select ename, empno
    from EMP e
    where e.deptno = d.deptno) emp_row
    from DEPT d
    where rownum < 3
   ') from dual;


However in above case we have EMP and DEPT as different tables

In my case I have a single de-normalized tables and want to get XML output with correct in Parent-child relation between the elements and indentations as above result

The tables would be FLAT table and queries on it would have no joins (not even self joins), CONNECT BY, Analytical functions etc.

For example say we have EMP_DEPT table consisting of columns from both EMP & DEPT tables and still wanted the XML output format in Parent-child relation of elements and indetations as above using simple "select * from EMP_DEPT"

To achieve this for all tables I thought of creating a RULE table as mentioned following which dbma_xmlgen would use and output XML elements accordingly

How can I achieve this using the mentioned RULE table as metadata and get correct XML output for all required tables?

Quote:

----------------------------------------|-------
Table Name......... | Column Name |Level |
--------------------------------------- |-------
EMP_DEPT........... | DEPTNO......... |.... 1 |
----------------------------------------|-------
EMP_DEPT........... | EMPNO.......... |.... 2 |
----------------------------------------|--------
PURCHASE_ORDER | Region.......... |.... 1 |
----------------------------------------|-------
PURCHASE_ORDER | ClientID........ |.... 2 |
----------------------------------------|---------
PURCHASE_ORDER | ItemID......... |.... 3 |
----------------------------------------|----------


Thanks in advance

Kind Regards
Orapratap

Report message to a moderator

Re: How can I define formatting rules for XML output while using dbms_xmlgen? [message #637204 is a reply to message #637191] Tue, 12 May 2015 11:49 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
 
SCOTT@orcl> select * from emp_dept order by deptno, empno
  2  /

    DEPTNO DNAME          ENAME           EMPNO
---------- -------------- ---------- ----------
        10 ACCOUNTING     CLARK            7782
        10 ACCOUNTING     KING             7839
        10 ACCOUNTING     MILLER           7934
        20 RESEARCH       SMITH            7369
        20 RESEARCH       JONES            7566
        20 RESEARCH       SCOTT            7788
        20 RESEARCH       ADAMS            7876
        20 RESEARCH       FORD             7902

8 rows selected.

SCOTT@orcl> select dbms_xmlgen.getxml
  2  	      ('select deptno, dname,
  3  		       cursor
  4  			 (select ename, empno
  5  			  from	 emp_dept e
  6  			  where  e.deptno = d.deptno
  7  			  order  by empno) emp_row
  8  		from   (select distinct deptno, dname
  9  			from   emp_dept) d
 10  		order  by deptno')
 11  from   dual
 12  /

DBMS_XMLGEN.GETXML('SELECTDEPTNO,DNAME,CURSOR(SELECTENAME,EMPNOFROMEMP_DEPTEWHER
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <EMP_ROW>
   <EMP_ROW_ROW>
    <ENAME>CLARK</ENAME>
    <EMPNO>7782</EMPNO>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <ENAME>KING</ENAME>
    <EMPNO>7839</EMPNO>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <ENAME>MILLER</ENAME>
    <EMPNO>7934</EMPNO>
   </EMP_ROW_ROW>
  </EMP_ROW>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <EMP_ROW>
   <EMP_ROW_ROW>
    <ENAME>SMITH</ENAME>
    <EMPNO>7369</EMPNO>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <ENAME>JONES</ENAME>
    <EMPNO>7566</EMPNO>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <ENAME>SCOTT</ENAME>
    <EMPNO>7788</EMPNO>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <ENAME>ADAMS</ENAME>
    <EMPNO>7876</EMPNO>
   </EMP_ROW_ROW>
   <EMP_ROW_ROW>
    <ENAME>FORD</ENAME>
    <EMPNO>7902</EMPNO>
   </EMP_ROW_ROW>
  </EMP_ROW>
 </ROW>
</ROWSET>


1 row selected.

Report message to a moderator

Re: How can I define formatting rules for XML output while using dbms_xmlgen? [message #637207 is a reply to message #637204] Tue, 12 May 2015 15:53 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
The following is a partial example of how you might implement your rules, but it can get complicated. It might be better to just right a query for each table and store that query as the rule.

SCOTT@orcl> select * from emp_dept order by deptno, empno
  2  /

    DEPTNO DNAME          ENAME           EMPNO
---------- -------------- ---------- ----------
        10 ACCOUNTING     CLARK            7782
        10 ACCOUNTING     KING             7839
        10 ACCOUNTING     MILLER           7934
        20 RESEARCH       SMITH            7369
        20 RESEARCH       JONES            7566
        20 RESEARCH       SCOTT            7788
        20 RESEARCH       ADAMS            7876
        20 RESEARCH       FORD             7902

8 rows selected.

SCOTT@orcl> select * from rule_tab
  2  /

TABLE_NAME      COLUMN_NAME            LVL JOIN_COL        ROW_NAME           ORDERBY
--------------- --------------- ---------- --------------- --------------- ----------
EMP_DEPT        DEPTNO                   1                 DEPT                     1
EMP_DEPT        DNAME                    1                 DEPT                     2
EMP_DEPT        EMPNO                    2 DEPTNO          EMP                      2
EMP_DEPT        ENAME                    2 DEPTNO          EMP                      1

4 rows selected.

SCOTT@orcl> create or replace function your_xml
  2    (p_tab in varchar2)
  3    return	 clob
  4  as
  5    v_sql1	 clob;
  6    v_sql3	 clob;
  7    v_sql2	 clob;
  8    v_join	 varchar2(30);
  9    v_name	 varchar2(30);
 10    v_sql	 clob;
 11    v_xml	 clob;
 12  begin
 13    v_sql1 := 'select ';
 14    v_sql3 := v_sql3 || ' from (select distinct ';
 15    for r1 in
 16  	 (select column_name from rule_tab where table_name = p_tab and lvl = 1 order by orderby)
 17    loop
 18  	 v_sql1 := v_sql1 || r1.column_name || ',';
 19  	 v_sql3 := v_sql3 || r1.column_name || ',';
 20    end loop;
 21    v_sql1 := rtrim (v_sql1, ',');
 22    v_sql3 := rtrim (v_sql3, ',');
 23    v_sql3 := v_sql3 || ' from ' || p_tab || ') t1';
 24    --
 25    v_sql2 := v_sql2 || ',cursor(select ';
 26    for r2 in
 27  	 (select column_name from rule_tab where table_name = p_tab and lvl = 2 order by orderby)
 28    loop
 29  	 v_sql2 := v_sql2 || r2.column_name || ',';
 30    end loop;
 31    v_sql2 := rtrim (v_sql2, ',');
 32    v_sql2 := v_sql2 || ' from ' || p_tab || ' t2';
 33    v_sql2 := v_sql2 || ' where t1.';
 34    select join_col, row_name
 35    into   v_join, v_name
 36    from   rule_tab
 37    where  table_name = p_tab
 38    and    lvl = 2
 39    and    orderby = 1;
 40    v_sql2 := v_sql2 || v_join || '=t2.' || v_join;
 41    v_sql2 := v_sql2 || ') ' || v_name;
 42    --
 43    v_sql := v_sql1 || v_sql2 || v_sql3;
 44    v_xml := dbms_xmlgen.getxml (v_sql);
 45    return v_xml;
 46  end your_xml;
 47  /

Function created.

SCOTT@orcl> show errors
No errors.
SCOTT@orcl> select your_xml ('EMP_DEPT') from dual
  2  /

YOUR_XML('EMP_DEPT')
------------------------------------------------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
 <ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <EMP>
   <EMP_ROW>
    <ENAME>CLARK</ENAME>
    <EMPNO>7782</EMPNO>
   </EMP_ROW>
   <EMP_ROW>
    <ENAME>KING</ENAME>
    <EMPNO>7839</EMPNO>
   </EMP_ROW>
   <EMP_ROW>
    <ENAME>MILLER</ENAME>
    <EMPNO>7934</EMPNO>
   </EMP_ROW>
  </EMP>
 </ROW>
 <ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <EMP>
   <EMP_ROW>
    <ENAME>SMITH</ENAME>
    <EMPNO>7369</EMPNO>
   </EMP_ROW>
   <EMP_ROW>
    <ENAME>JONES</ENAME>
    <EMPNO>7566</EMPNO>
   </EMP_ROW>
   <EMP_ROW>
    <ENAME>SCOTT</ENAME>
    <EMPNO>7788</EMPNO>
   </EMP_ROW>
   <EMP_ROW>
    <ENAME>ADAMS</ENAME>
    <EMPNO>7876</EMPNO>
   </EMP_ROW>
   <EMP_ROW>
    <ENAME>FORD</ENAME>
    <EMPNO>7902</EMPNO>
   </EMP_ROW>
  </EMP>
 </ROW>
</ROWSET>


1 row selected.

Report message to a moderator

Re: How can I define formatting rules for XML output while using dbms_xmlgen? [message #637303 is a reply to message #637207] Thu, 14 May 2015 09:58 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Barbara

Thank you so much for your reply and time

This piece of code is nice and for sure it would give required results up to a limit

However for a FLAT table which has got de-normalized results after querying multiple tables we would want to query it only once (single pass) for performance implications

In above example we would end up doing one SELECT (on FLAT table i.e. EMP_DEPT) per nested level

Initially I thought of using dbms_xmlgen.getxml we might achieve the nested elements with single pass on the FLAT table

I checked other options DBMS_XMLGEN.newContextFromHierarchy etc. but it too does not produce required output

Now I am checking possibilities using nested XMLELEMENT involving XMLAGG and XMLFOREST to pick nested element dynamically from Rule Table with primary condition as single SELECT on the FLAT table

Any pointers on it please, if possible?

Thanks again

Kind Regards
Orapratap

Report message to a moderator

Re: How can I define formatting rules for XML output while using dbms_xmlgen? [message #637318 is a reply to message #637303] Thu, 14 May 2015 17:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
You could create a function that accesses the rule table to create and execute a query like the one below, but nesting levels could get complicated, especially if level 3 could be nested under level 1 or level 2 and so on. As I said before, you would probably be better off writing one query like the one below for each table and storing that query as your rule.


SCOTT@orcl> SELECT XMLSERIALIZE
  2  	      (DOCUMENT
  3  	       XMLELEMENT
  4  		 ("DEPT_ROW",
  5  		  XMLFOREST
  6  		    (deptno, dname),
  7  		  XMLAGG
  8  		    (XMLELEMENT
  9  		      ("EMP_ROW",
 10  		       XMLFOREST
 11  			 (ename, empno))))
 12  	       INDENT SIZE=2) emp_dept
 13  FROM   emp_dept
 14  GROUP  BY deptno, dname
 15  /

EMP_DEPT
------------------------------------------------------------------------------------------------------------------------
<DEPT_ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <EMP_ROW>
    <ENAME>CLARK</ENAME>
    <EMPNO>7782</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>MILLER</ENAME>
    <EMPNO>7934</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>KING</ENAME>
    <EMPNO>7839</EMPNO>
  </EMP_ROW>
</DEPT_ROW>

<DEPT_ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <EMP_ROW>
    <ENAME>SMITH</ENAME>
    <EMPNO>7369</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>FORD</ENAME>
    <EMPNO>7902</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>ADAMS</ENAME>
    <EMPNO>7876</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>SCOTT</ENAME>
    <EMPNO>7788</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>JONES</ENAME>
    <EMPNO>7566</EMPNO>
  </EMP_ROW>
</DEPT_ROW>


2 rows selected.

[Updated on: Thu, 14 May 2015 17:27]

Report message to a moderator

Re: How can I define formatting rules for XML output while using dbms_xmlgen? [message #637319 is a reply to message #637318] Thu, 14 May 2015 17:51 Go to previous messageGo to next message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Here is some starter code for 2 levels.

SCOTT@orcl> create or replace procedure your_xml
  2    (p_tab in  varchar2,
  3  	p_xml out sys_refcursor)
  4  as
  5    v_sql1	 clob;
  6    v_sql3	 clob;
  7    v_sql2	 clob;
  8    v_name	 varchar2(30);
  9    v_sql	 clob;
 10  begin
 11    select row_name
 12    into   v_name
 13    from   rule_tab
 14    where  table_name = p_tab
 15    and    lvl = 1
 16    and    orderby = 1;
 17    v_sql1 := 'SELECT XMLSERIALIZE(DOCUMENT XMLELEMENT("' || v_name || '",XMLFOREST(';
 18    v_sql3 := ') INDENT SIZE=2) ' || p_tab || ' FROM ' || p_tab || ' GROUP BY ';
 19    for r1 in
 20  	 (select column_name from rule_tab where table_name = p_tab and lvl = 1 order by orderby)
 21    loop
 22  	 v_sql1 := v_sql1 || r1.column_name || ',';
 23  	 v_sql3 := v_sql3 || r1.column_name || ',';
 24    end loop;
 25    v_sql1 := rtrim (v_sql1, ',') || ')';
 26    v_sql3 := rtrim (v_sql3, ',');
 27    --
 28    select row_name
 29    into   v_name
 30    from   rule_tab
 31    where  table_name = p_tab
 32    and    lvl = 2
 33    and    orderby = 1;
 34    v_sql2 := ',XMLAGG(XMLELEMENT("' || v_name || '",XMLFOREST(';
 35    for r2 in
 36  	 (select column_name from rule_tab where table_name = p_tab and lvl = 2 order by orderby)
 37    loop
 38  	 v_sql2 := v_sql2 || r2.column_name || ',';
 39    end loop;
 40    v_sql2 := rtrim (v_sql2, ',') || ')))';
 41    --
 42    v_sql := v_sql1 || v_sql2 || v_sql3;
 43    open p_xml for v_sql;
 44  end your_xml;
 45  /

Procedure created.

SCOTT@orcl> show errors
No errors.
SCOTT@orcl> variable g_xml refcursor
SCOTT@orcl> exec your_xml ('EMP_DEPT', :g_xml)

PL/SQL procedure successfully completed.

SCOTT@orcl> print g_xml

EMP_DEPT
------------------------------------------------------------------------------------------------------------------------
<DEPT_ROW>
  <DEPTNO>10</DEPTNO>
  <DNAME>ACCOUNTING</DNAME>
  <EMP_ROW>
    <ENAME>CLARK</ENAME>
    <EMPNO>7782</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>MILLER</ENAME>
    <EMPNO>7934</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>KING</ENAME>
    <EMPNO>7839</EMPNO>
  </EMP_ROW>
</DEPT_ROW>

<DEPT_ROW>
  <DEPTNO>20</DEPTNO>
  <DNAME>RESEARCH</DNAME>
  <EMP_ROW>
    <ENAME>SMITH</ENAME>
    <EMPNO>7369</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>FORD</ENAME>
    <EMPNO>7902</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>ADAMS</ENAME>
    <EMPNO>7876</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>SCOTT</ENAME>
    <EMPNO>7788</EMPNO>
  </EMP_ROW>
  <EMP_ROW>
    <ENAME>JONES</ENAME>
    <EMPNO>7566</EMPNO>
  </EMP_ROW>
</DEPT_ROW>


2 rows selected.

[Updated on: Thu, 14 May 2015 17:52]

Report message to a moderator

Re: How can I define formatting rules for XML output while using dbms_xmlgen? [message #637513 is a reply to message #637319] Tue, 19 May 2015 07:24 Go to previous messageGo to next message
orapratap
Messages: 134
Registered: November 2011
Location: Canada
Senior Member
Hello Barbara

Thank you so much for your help

The method you have described is exactly what I was looking for

It is nesting elements dynamically as per "user defined rule" however, if the nesting crosses 2 levels then it is not possible to get the results in single SELECT pass

For example if we have denormalized table of country, state, district, city and we want XML output in following manner it needs more than One SELECT statement regardless of how we entangle XMLELEMENT, XMLAGG, XMLFOREST etc, thus affecting the performance

<DATA>
  <COUNTRY>
     <STATE>
        <CITY>
        <\CITY>
     <\STATE>		
  <\CONTRY>	  
  <COUNTRY>
     <STATE>
        <CITY>
        <\CITY>
     <\STATE>		
  <\COUNTRY>  
<\DATA>


Could you please suggest on this?

Thanks and Regards
Orapratap

Report message to a moderator

Re: How can I define formatting rules for XML output while using dbms_xmlgen? [message #637540 is a reply to message #637513] Tue, 19 May 2015 18:00 Go to previous message
Barbara Boehmer
Messages: 9104
Registered: November 2002
Location: California, USA
Senior Member
Sorry, but I am out of ideas. Perhaps someone else can suggest something. It is very difficult to take de-normalized data and normalize it again. It would be much better if you could select from the original normalized tables.

Report message to a moderator

Previous Topic: Is it possible to retrieve an array of pl/sql record which contain DATE elements in Java
Next Topic: Getting ICX_SESSION_FAILED error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Feb 03 17:44:09 CST 2025
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.