3 Replies Latest reply: May 3, 2013 2:21 PM by gimbal2 RSS

    Dynamically build xml SQL statement

    642009
      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