SELECT deptno, dname FROM dept;
DEPTNO DNAME
---------- --------------
10 ACCOUNTING
20 RESEARCH
30 SALES
40 OPERATIONS
SELECT empno, ename, deptno FROM emp;
EMPNO ENAME DEPTNO
---------- ---------- ----------
7369 SMITH 20
7499 ALLEN 30
7521 WARD 30
7566 JONES 20
7654 MARTIN 30
7698 BLAKE 30
7782 CLARK 10
7788 SCOTT 20
7839 KING 10
7844 TURNER 30
7876 ADAMS 20
7900 JAMES 30
7902 FORD 20
7934 MILLER 10
set serveroutput on long 5000 wrap off linesize 120;
set long 2000;
SELECT dbms_xmlgen.getXML(
'SELECT empno "EMP_NO"
, ename "NAME"
, deptno "DEPT_NO"
FROM emp
WHERE deptno = 10'
, 0
) as resXML FROM dual;
RESXML
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMP_NO>7782</EMP_NO>
<NAME>CLARK</NAME>
<DEPT_NO>10</DEPT_NO>
</ROW>
<ROW>
<EMP_NO>7839</EMP_NO>
<NAME>KING</NAME>
<DEPT_NO>10</DEPT_NO>
</ROW>
<ROW>
<EMP_NO>7934</EMP_NO>
<NAME>MILLER</NAME>
<DEPT_NO>10</DEPT_NO>
</ROW>
</ROWSET>
SQL>
set serveroutput on long 5000 wrap off linesize 120;
declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
xmlstr varchar2(32767);
line varchar2(2000);
begin
-- set up the query context and get handle...!
queryCtx := DBMS_XMLQuery.newContext('select * from emp where rownum < 3');
-- get the result..!
result := DBMS_XMLQuery.getXML(queryCtx);
xmlstr := dbms_lob.SUBSTR(result,32767); -- extract part of it suitable to output buffer
-- ready to show result or
-- to use the result to put it in tables/send as messages..
loop
exit when xmlstr is null;
line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line('| '||line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
DBMS_XMLQuery.closeContext(queryCtx); -- you must close the query handle..
end;
/
| <?xml version = '1.0'?>
| <ROWSET>
| <ROW num="1">
| <EMPNO>7369</EMPNO>
| <ENAME>SMITH</ENAME>
| <JOB>CLERK</JOB>
| <MGR>7902</MGR>
| <HIREDATE>12/17/1980 0:0:0</HIREDATE>
| <SAL>800</SAL>
| <DEPTNO>20</DEPTNO>
| </ROW>
| <ROW num="2">
| <EMPNO>7499</EMPNO>
| <ENAME>ALLEN</ENAME>
| <JOB>SALESMAN</JOB>
| <MGR>7698</MGR>
| <HIREDATE>2/20/1981 0:0:0</HIREDATE>
| <SAL>1600</SAL>
| <COMM>300</COMM>
| <DEPTNO>30</DEPTNO>
| </ROW>
| </ROWSET>
set serveroutput on long 5000 wrap off linesize 120;
declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
xmlstr varchar2(32767);
line varchar2(2000);
begin
queryCtx := DBMS_XMLQuery.newContext('select * from emp where rownum < 3');
DBMS_XMLQuery.setRowTag(queryCtx,'EMP'); -- sets the row tag name
DBMS_XMLQuery.setRowSetTag(queryCtx,'EMPSET'); -- sets rowset tag name
result := DBMS_XMLQuery.getXML(queryCtx);
xmlstr := dbms_lob.SUBSTR(result,32767);
loop
exit when xmlstr is null;
line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line('| '||line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
DBMS_XMLQuery.closeContext(queryCtx); end;
/
| <?xml version = '1.0'?>
| <EMPSET>
| <EMP num="1">
| <EMPNO>7369</EMPNO>
| <ENAME>SMITH</ENAME>
| <JOB>CLERK</JOB>
| <MGR>7902</MGR>
| <HIREDATE>12/17/1980 0:0:0</HIREDATE>
| <SAL>800</SAL>
| <DEPTNO>20</DEPTNO>
| </EMP>
| <EMP num="2">
| <EMPNO>7499</EMPNO>
| <ENAME>ALLEN</ENAME>
| <JOB>SALESMAN</JOB>
| <MGR>7698</MGR>
| <HIREDATE>2/20/1981 0:0:0</HIREDATE>
| <SAL>1600</SAL>
| <COMM>300</COMM>
| <DEPTNO>30</DEPTNO>
| </EMP>
| </EMPSET>
set serveroutput on long 5000 wrap off linesize 120;
declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
xmlstr varchar2(32767);
line varchar2(2000);
begin
queryCtx := DBMS_XMLQuery.newContext('select * from emp where rownum < 10');
DBMS_XMLQuery.setSkipRows(queryCtx,7); -- set the number of rows to skip
DBMS_XMLQuery.setMaxRows(queryCtx,9); -- set the max number of rows per fetch
result := DBMS_XMLQuery.getXML(queryCtx);
xmlstr := dbms_lob.SUBSTR(result,32767);
loop
exit when xmlstr is null;
line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line('| '||line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
DBMS_XMLQuery.closeContext(queryCtx); end;
/
| <?xml version = '1.0'?>
| <ROWSET>
| <ROW num="8">
| <EMPNO>7788</EMPNO>
| <ENAME>SCOTT</ENAME>
| <JOB>ANALYST</JOB>
| <MGR>7566</MGR>
| <HIREDATE>4/19/1987 0:0:0</HIREDATE>
| <SAL>3000</SAL>
| <DEPTNO>20</DEPTNO>
| </ROW>
| <ROW num="9">
| <EMPNO>7839</EMPNO>
| <ENAME>KING</ENAME>
| <JOB>PRESIDENT</JOB>
| <HIREDATE>11/17/1981 0:0:0</HIREDATE>
| <SAL>5000</SAL>
| <DEPTNO>10</DEPTNO>
| </ROW>
| </ROWSET>
queryCtx := DBMS_XMLQuery.newContext('select * from emp where rownum < :rn');
2. Clear variable list if appropriate.
DBMS_XMLQuery.clearBindValues(queryCtx);
3. Bind each variable's value
DBMS_XMLQuery.setBindValue(queryCtx,'rn',10);
4. Get result:
result := DBMS_XMLQuery.getXML(queryCtx);
set serveroutput on long 5000 wrap off linesize 120;
declare
queryCtx DBMS_XMLquery.ctxType;
result CLOB;
xmlstr varchar2(32767);
line varchar2(2000);
begin
queryCtx := DBMS_XMLQuery.newContext('select * from emp where rownum < :rn');
--DBMS_XMLQuery.clearBindValues(queryCtx);
DBMS_XMLQuery.setBindValue(queryCtx,'rn',3);
result := DBMS_XMLQuery.getXML(queryCtx);
xmlstr := dbms_lob.SUBSTR(result,32767);
loop
exit when xmlstr is null;
line := substr(xmlstr,1,instr(xmlstr,chr(10))-1);
dbms_output.put_line('| '||line);
xmlstr := substr(xmlstr,instr(xmlstr,chr(10))+1);
end loop;
DBMS_XMLQuery.closeContext(queryCtx);
end;
/
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Commented:
Voted "YES" above.