SELECT XMLQuery(
‘for $i in ora:view(“SCOTT”, “DEPT”)/ROW
return <DEPT id=”{$i/DEPTNO}” DNAME =”{$i/DNAME}” >
{for $j in ora:view(“SCOTT”, “EMP”)/ROW
where $j/DEPTNO = $i/DEPTNO
return <EMP ID=”{$j/EMPNO}” Test=”{if ($param eq “EN”) then “True” else “False”}” ></EMP>
}
</DEPT>’
passing cast(‘EN’ as varchar2(10)) as “param”
RETURNING CONTENT).getstringval() a FROM DUAL;
Regards
Mohmmad Talat
SELECT XMLQuery(
‘for $i in ora:view(“SCOTT”, “DEPT”)/ROW
where $i/DEPTNO =xs:integer(string($param))
return <DEPT id=”{$i/DEPTNO}” DNAME =”{$i/DNAME}” >
{for $j in ora:view(“SCOTT”, “EMP”)/ROW
where $j/DEPTNO = $i/DEPTNO
return <EMP ID=”{$j/EMPNO}” ENAME=”{$j/ENAME}”></EMP>}
</DEPT>’ passing xmlelement(dummy,20) as “param”
RETURNING CONTENT) INTO XML FROM DUAL;
dbms_output.put_line(xml.getstringval());
end;
OR using
passing cast(20 as number) as “param”
SELECT XMLQuery(
‘for $i in ora:view(“SCOTT”, “DEPT”)/ROW
where $i/DEPTNO =xs:integer(string($param))
return <DEPT id=”{$i/DEPTNO}” DNAME =”{$i/DNAME}” >
{for $j in ora:view(“SCOTT”, “EMP”)/ROW
where $j/DEPTNO = $i/DEPTNO
return <EMP ID=”{$j/EMPNO}” ENAME=”{$j/ENAME}”></EMP>}
</DEPT>’ passing xmlelement(dummy,20) as “param”
RETURNING CONTENT) INTO XML FROM DUAL;
dbms_output.put_line(xml.getstringval());
SELECT XMLQuery(
‘for $i in ora:view(“SCOTT”, “DEPT”)/ROW
return <DEPT id=”{$i/DEPTNO}” DNAME =”{$i/DNAME}” >
{for $j in ora:view(“SCOTT”, “EMP”)/ROW
where $j/DEPTNO = $i/DEPTNO
return <EMP ID=”{$j/EMPNO}” ENAME=”{$j/ENAME}”></EMP>}
</DEPT>’
RETURNING CONTENT) FROM DUAL;
————-OUTPUT——–
<DEPT id=”10″ DNAME=”ACCOUNTING”>
<EMP ID=”7782″ ENAME=”CLARK”></EMP>
<EMP ID=”7839″ ENAME=”KING”></EMP>
<EMP ID=”7934″ ENAME=”MILLER”></EMP>
</DEPT>
<DEPT id=”20″ DNAME=”RESEARCH”>
<EMP ID=”7369″ ENAME=”SMITH”></EMP>
<EMP ID=”7566″ ENAME=”JONES”></EMP>
<EMP ID=”7788″ ENAME=”SCOTT”></EMP>
<EMP ID=”7876″ ENAME=”ADAMS”></EMP>
<EMP ID=”7902″ ENAME=”FORD”></EMP>
</DEPT>
<DEPT id=”30″ DNAME=”SALES”>
<EMP ID=”7499″ ENAME=”ALLEN”></EMP>
<EMP ID=”7521″ ENAME=”WARD”></EMP>
<EMP ID=”7654″ ENAME=”MARTIN”></EMP>
<EMP ID=”7698″ ENAME=”BLAKE”></EMP>
<EMP ID=”7844″ ENAME=”TURNER”></EMP>
<EMP ID=”7900″ ENAME=”JAMES”></EMP>
</DEPT>
<DEPT id=”40″ DNAME=”OPERATIONS”></DEPT>
Regards
Mohammad Talat
Mohammad Talat 