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: 101
Registered: February 2008
|
Senior 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
|
|
|
|
Goto Forum:
Current Time: Sun Jan 26 01:13:14 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
