–passing parameters XMLQUERY Oracle

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());

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s