1

Topic: Parsing XML in two tables

Kind time of the days, respected !

2

Re: Parsing XML in two tables

There are two tables:

create table schematest.dept
(
deptno number;
loc varchar2 (100 char);
dname varchar2 (100 char)
);
create table schematest.emp
(
deptno number;
empno number;
ename varchar2 (100 char);
sal number
);

And the code which should create object of type XMLType and then scatter contents of this object under two these tables.

declare
xml_var XMLType;
begin
select XMLType ('
<rowset>
<employee>
<deptno> 40 </deptno>
<empno> 1111 </empno>
<ename> Queen </ename>
<sal> 7000 </sal>
</employee>
<department>
<deptno> 50 </deptno>
<dname> MARKETING </dname>
<loc> MOSCOW </loc>
</department>
<employee>
<deptno> 40 </deptno>
<empno> 2222 </empno>
<ename> Duke </ename>
<sal> 4000 </sal>
</employee>
<employee>
<deptno> 50 </deptno>
<empno> 3333 </empno>
<ename> Earl </ename>
<sal> 6000 </sal>
</employee>
</rowset>
') into xml_var
from dual;
INSERT FIRST
WHEN empno IS NULL THEN INTO SCHEMATEST.dept (deptno, dname, loc)
VALUES (deptno, dname, loc)
WHEN empno IS NOT NULL THEN INTO SCHEMATEST.emp (empno, ename, sal, deptno)
VALUES (empno, ename, sal, deptno)
SELECT y.dname
, y.loc
, y.deptno
, y.empno
, y.ename
, y.sal
FROM XMLTABLE (' for $i in/ROWSET / (employee|department) return $i'
PASSING xml_var
COLUMNS dname varchar2 (100 char) PATH '/department/dname'
, loc VARCHAR2 (100 char) PATH '/department/loc'
, deptno number PATH './deptno'
, empno NUMBER PATH '/employee/empno'
, ename varchar2 (100 char) PATH '/employee/ename'
, sal NUMBER PATH '/employee/sal'
) AS y;
EXCEPTION
WHEN NO_DATA_FOUND THEN DBMS_OUTPUT.PUT_LINE (' NO_DATA_FOUND ');
WHEN TOO_MANY_ROWS THEN DBMS_OUTPUT.PUT_LINE (' TOO_MANY_ROWS');
end;

But as a result of a line to tables do not get. Where the error can disappear?

3

Re: Parsing XML in two tables

ultrasonic7;
XPath is case sensitive

4

Re: Parsing XML in two tables

Thanks, env . I rowset in the lower register forgot to translate.

5

Re: Parsing XML in two tables

And this line

for $i in/rowset / (employee|department) return $i

as I understand, a certain cycle on a dial-up is declared. Where to esteem about syntax of cycles for. in in XPath?

6

Re: Parsing XML in two tables

ultrasonic7 wrote:

And this line

for $i in/rowset / (employee|department) return $i

as I understand, a certain cycle on a dial-up is declared. Where to esteem about syntax of cycles for. in in XPath?

What for to you a cycle? To select all child members xquery it is not required, enough xpath