Home » Developer & Programmer » JDeveloper, Java & XML » Dynamically build xml SQL statement (Oracle 11g, windows 7)
Dynamically build xml SQL statement [message #583621] Thu, 02 May 2013 06:51
lokimisc
Messages: 72
Registered: February 2008
Member
Hi,
We have an requirement to create xml data for entire database (selected tables) which are in hierarchy.
Procedure should read node_mapping table having parent and child tables relationship info and build XML Select statement.

Currently it is building SQL statement whenthere are one parent having multiple childrens i.e Dept having emp, emp_act, emp_rsch....
but when child node are having childrens then it is not working - it has to repeatedly call this procedure (recursive) and build below given SQL statement.

Please help to achive

1. To change procedure to build xml sql statement when there are multiple childrens to child nodes (hierarchy)
2. To format the output in xml data

We are using ORACLE 11G and WINDOWS 7


CREATE TABLE node_mapping 
(
 NODE_ID NUMBER(5) PRIMARY KEY,
 PARENT_NODE VARCHAR2(100),
 CHILD_NODE VARCHAR2(100),
 PARENT_NODEID VARCHAR2(50),
 CHILD_NODEID VARCHAR2(50)
 )

INSERT INTO NODE_MAPPING VALUES(1,'DEPT','EMP','DEPTNO','DEPTNO');
INSERT INTO NODE_MAPPING VALUES(2,'DEPT','EMP_ACT','DEPTNO','DEPTNO');
INSERT INTO NODE_MAPPING VALUES(3,'DEPT','EMP_MGT','DEPTNO','DEPTNO');
INSERT INTO NODE_MAPPING VALUES(4,'DEPT','EMP_RSCH','DEPTNO','DEPTNO');
INSERT INTO NODE_MAPPING VALUES(5,'EMP','EMP_BONUS','EMPNO','EMPNO');
INSERT INTO NODE_MAPPING VALUES(6,'EMP_BONUS','BONUS_PERCENTAGE','EMP_BONUSID','EMP_BONUSID');
INSERT INTO NODE_MAPPING VALUES(7,'EMP_ACT','EMP_BONUS','EMPNO','EMPNO');
INSERT INTO NODE_MAPPING VALUES(8,'EMP_MGT','EMP_BONUS','EMPNO','EMPNO');
INSERT INTO NODE_MAPPING VALUES(9,'EMP_RSCH','EMP_BONUS','EMPNO','EMPNO');



CREATE OR REPLACE PROCEDURE create_xml
  (
    pi_buildlabel VARCHAR2
  ) 
  IS

  v_vwprcols          VARCHAR2(32767);
  v_vwchldcols        VARCHAR2(32767);  
  v_childnodecnt      NUMBER := 0;
  v_childnode         VARCHAR2(200);
  v_fromflag          BOOLEAN;
  v_sql               CLOB;
  v_dynsql_mstr       CLOB;
  v_dynsql_chld       CLOB;
  v_dynsql_from       VARCHAR2(4000);
  ......
  ....

  TYPE targetdata_tbl IS TABLE OF VARCHAR2(50); 
  targetdata_tbl_list targetdata_tbl := targetdata_tbl();

  BEGIN

  FOR i IN 1..targetdata_tbl_list.count LOOP
    v_vwname := targetdata_tbl_list(i);

       v_dynsql_mstr := NULL;
       v_dynsql_chld := NULL;
       v_vwprcols := get_view_cols(v_vwname, v_schema);  -- fetch table columns from all_tab_col
       v_dynsql_mstr := 'SELECT XMLAGG(XMLFOREST(';
       v_dynsql_mstr := v_dynsql_mstr ||v_vwprcols;
       v_fromflag := TRUE;
       
    SELECT COUNT(*)
      INTO v_childnodecnt
      FROM node_mapping
     WHERE parent_node = v_vwname;

       FOR j IN (SELECT parent_node, child_node, parent_nodeid, child_nodeid FROM node_mapping WHERE parent_node = v_vwname) LOOP
           v_childnode := j.child_node;
           
           IF v_fromflag = TRUE THEN
              v_dynsql_from :=  ')) AS '
                                 ||j.parent_node
                                 ||' FROM '
                                 ||j.parent_node
                                 ||' WHERE '
                                 ||j.parent_node||'.'||'TESTING'
                                 ||' = '
                                 ||''''||v_buildlabel||'''';
              v_fromflag := FALSE;
           END IF;                 
           
           IF v_childnodecnt > 0 THEN
           --DBMS_OUTPUT.PUT_LINE('more than 1 child');
        
            v_vwchldcols := get_view_cols(v_childnode, v_schema); 
            v_dynsql_chld := v_dynsql_chld ||', '||'(SELECT XMLAGG(XMLELEMENT('||v_childnode
                                                                    ||', XMLFOREST('
                                                                    ||v_vwchldcols
                                                                    ||'))) FROM '||v_childnode
                                                                    ||' WHERE '||j.parent_node||'.'||j.parent_nodeid 
                                                                    ||' = '
                                                                    ||j.child_node||'.'||j.child_nodeid
                                                                    ||' AND '
                                                                    ||j.parent_node||'.'||'TESTING'
                                                                    ||' = '
                                                                    ||j.child_node||'.'||'TESTING'
                                                                    ||') AS '
                                                                    ||j.child_node;

     ELSE
       v_dynsql_mstr := v_dynsql_mstr ||' FROM '
                            ||j.parent_node
                            ||' WHERE '
                            ||j.parent_node||'.'||'TESTING'
                            ||' = '
                            ||v_buildlabel; 
      
     END IF;                                                                                                                                               
     DBMS_OUTPUT.PUT_LINE('v_dynsql - '||v_dynsql_mstr||v_dynsql_chld||v_dynsql_from);
     END LOOP;

     END LOOP;   
    
  END create_xml;


SAMPLE OUTPUT:
SELECT XMLAGG(XMLFOREST(d.deptno,
                        d.dname,
                        d.loc,
                        (SELECT XMLAGG(XMLELEMENT("EMP",
                                                  XMLFOREST(e.empno,
                                                            e.ename,
                                                            e.job,
                                                            e.hiredate,
                                                            e.sal,
                                                            (SELECT XMLAGG(XMLELEMENT("EMP_BONUS",
                                                                                      XMLFOREST(ename,
                                                                                                job,
                                                                                                sal,
                                                                                                comm,
                                                                                                empno)))
                                                               FROM EMP_BONUS
                                                              WHERE EMP_BONUS.EMPNO =
                                                                    e.EMPNO) AS
                                                            EMP_BONUS)))
                           FROM EMP e
                          WHERE e.deptno = d.deptno) as emp)) as dept
  from dept d
 ORDER BY deptno


When query executed:

<DEPTNO>50</DEPTNO><DNAME>ADMIN</DNAME><LOC>BANGALOER</LOC><EMP><EMP><EMPNO>7888</EMPNO><ENAME>DON</ENAME><JOB>CEO</JOB><HIREDATE>1988-01-23</HIREDATE><SAL>5000</SAL><EMP_BONUS><EMP_BONUS><ENAME>SMITH</ENAME><JOB>CLERK</JOB><SAL>20000</SAL><COMM>50</COMM><EMPNO>7888</EMPNO></EMP_BONUS></EMP_BONUS></EMP></EMP><DEPTNO>10</DEPTNO><DNAME>ACCOUNTING</DNAME><LOC>NEW YORK</LOC><EMP><EMP><EMPNO>7782</EMPNO><ENAME>CLARK</ENAME><JOB>MANAGER</JOB><HIREDATE>1981-06-09</HIREDATE><SAL>2450</SAL></EMP><EMP><EMPNO>7839</EMPNO><ENAME>KING</ENAME><JOB>PRESIDENT</JOB><HIREDATE>1981-11-17</HIREDATE><SAL>5000</SAL></EMP><EMP><EMPNO>7934</EMPNO><ENAME>MILLER</ENAME><JOB>CLERK</JOB><HIREDATE>1982-01-23</HIREDATE><SAL>1300</SAL></EMP></EMP><DEPTNO>20</DEPTNO><DNAME>RESEARCH</DNAME><LOC>DALLAS</LOC><EMP><EMP><EMPNO>7369</EMPNO><ENAME>SMITH</ENAME><JOB>CLERK</JOB><HIREDATE>1980-12-17</HIREDATE><SAL>800</SAL></EMP><EMP><EMPNO>7566</EMPNO><ENAME>JONES</ENAME><JOB>MANAGER</JOB><HIREDATE>1981-04-02</HIREDATE><SAL>2975</SAL><EMP_BONUS><EMP_BONUS><ENAME>VIRU</ENAME><JOB>SOFTWARE</JOB><SAL>10000</SAL><COMM>10</COMM><EMPNO>7566</EMPNO></EMP_BONUS><EMP_BONUS><ENAME>VEERU</ENAME><JOB>SOFTWARE</JOB><SAL>60000</SAL><COMM>10</COMM><EMPNO>7566</EMPNO></EMP_BONUS></EMP_BONUS></EMP><EMP><EMPNO>7788</EMPNO><ENAME>SCOTT</ENAME><JOB>ANALYST</JOB><HIREDATE>1987-04-19</HIREDATE><SAL>3000</SAL></EMP><EMP><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME><JOB>CLERK</JOB><HIREDATE>1987-05-23</HIREDATE><SAL>1100</SAL></EMP><EMP><EMPNO>7902</EMPNO><ENAME>FORD</ENAME><JOB>ANALYST</JOB><HIREDATE>1981-12-03</HIREDATE><SAL>3000</SAL></EMP></EMP><DEPTNO>30</DEPTNO><DNAME>SALES</DNAME><LOC>CHICAGO</LOC><EMP><EMP><EMPNO>7499</EMPNO><ENAME>ALLEN</ENAME><JOB>SALESMAN</JOB><HIREDATE>1981-02-20</HIREDATE><SAL>1600</SAL></EMP><EMP><EMPNO>7521</EMPNO><ENAME>WARD</ENAME><JOB>SALESMAN</JOB><HIREDATE>1981-02-22</HIREDATE><SAL>1250</SAL><EMP_BONUS><EMP_BONUS><ENAME>ALLEN</ENAME><JOB>SALEMAN</JOB><SAL>50000</SAL><COMM>10</COMM><EMPNO>7521</EMPNO></EMP_BONUS></EMP_BONUS></EMP><EMP><EMPNO>7654</EMPNO><ENAME>MARTIN</ENAME><JOB>SALESMAN</JOB><HIREDATE>1981-09-28</HIREDATE><SAL>1250</SAL></EMP><EMP><EMPNO>7698</EMPNO><ENAME>BLAKE</ENAME><JOB>MANAGER</JOB><HIREDATE>1981-05-01</HIREDATE><SAL>2850</SAL></EMP><EMP><EMPNO>7844</EMPNO><ENAME>TURNER</ENAME><JOB>SALESMAN</JOB><HIREDATE>1981-09-08</HIREDATE><SAL>1500</SAL></EMP><EMP><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME><JOB>CLERK</JOB><HIREDATE>1981-12-03</HIREDATE><SAL>950</SAL></EMP></EMP><DEPTNO>40</DEPTNO><DNAME>OPERATIONS</DNAME><LOC>BOSTON</LOC><EMP><EMP><EMPNO>7823</EMPNO><ENAME>HP</ENAME><JOB>CLERK</JOB><HIREDATE>1988-04-12</HIREDATE><SAL>3000</SAL></EMP><EMP><EMPNO>7773</EMPNO><ENAME>SAMSUNG</ENAME><JOB>SALESMAN</JOB><HIREDATE>1980-10-10</HIREDATE><SAL>2000</SAL></EMP></EMP>



Thanks & Regards,
Lokesh
Previous Topic: Re: ORA-12505 TNS:listener does not currently know of SID given in connect descriptor
Next Topic: Xml data to be passed
Goto Forum:
  


Current Time: Tue Sep 02 09:15:36 CDT 2014

Total time taken to generate the page: 0.20031 seconds