#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    1
    Rep Power
    0

    Dynamically build xml SQL statement


    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
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,143
    Rep Power
    9398
    I'm pretty sure the difficulty here is in the procedure and not the XML itself. Moving to Oracle.

IMN logo majestic logo threadwatch logo seochat tools logo