XML-HELP

From: nirup <vis.prpuppala_at_gmail.com>
Date: 2 Dec 2004 11:35:58 -0800
Message-ID: <707a50d2.0412021135.69320164@posting.google.com>

I have the data in clob which has the parent_id and their child information is stored as ALIAS.How do I read this child information from the alias.
I have tried using the extract function but I will get the empty string but it has the values....How do I read this ALIAS info ?Any one can help me in this regard...
I will give the example as dept and emp

Data looks like the following
select d.deptno,'<?xml version="1.0"
encoding="UTF-8"?>'||xmlagg(xmlelement("EMPDATA",xmlelement("EMPNO",e.empno), xmlelement("ENAME",e.ename))).getclobval() as "EMPINFO" from scott.dept d,scott.emp e where d.deptno=e.deptno group by d.deptno DEPTNO EMPINFO

---------- --------------------------------------------------------------------------------

10 <?xml version="1.0"
encoding="UTF-8"?><EMPDATA><EMPNO>7782</EMPNO><ENAME>CLARK</ ENAME></EMPDATA><EMPDATA><EMPNO>7839</EMPNO><ENAME>KING</ENAME></EMPDATA><EMPDAT A><EMPNO>7934</EMPNO><ENAME>MILLER</ENAME></EMPDATA>

20 <?xml version="1.0"
encoding="UTF-8"?><EMPDATA><EMPNO>7369</EMPNO><ENAME>SMITH</ ENAME></EMPDATA><EMPDATA><EMPNO>7902</EMPNO><ENAME>FORD</ENAME></EMPDATA><EMPDAT A><EMPNO>7876</EMPNO><ENAME>ADAMS</ENAME></EMPDATA><EMPDATA><EMPNO>7788</EMPNO>< ENAME>SCOTT</ENAME></EMPDATA><EMPDATA><EMPNO>7566</EMPNO><ENAME>JONES</ENAME></E MPDATA> 30 <?xml version="1.0"
encoding="UTF-8"?><EMPDATA><EMPNO>7499</EMPNO><ENAME>ALLEN</

DEPTNO EMPINFO

---------- --------------------------------------------------------------------------------

ENAME></EMPDATA><EMPDATA><EMPNO>7698</EMPNO><ENAME>BLAKE</ENAME></EMPDATA><EMPDA TA><EMPNO>7654</EMPNO><ENAME>MARTIN</ENAME></EMPDATA><EMPDATA><EMPNO>7844</EMPNO
><ENAME>TURNER</ENAME></EMPDATA><EMPDATA><EMPNO>7900</EMPNO><ENAME>JAMES</ENAME>
</EMPDATA><EMPDATA><EMPNO>7521</EMPNO><ENAME>WARD</ENAME></EMPDATA>

Now I would like to read each alias information for that particular deptno and store it in the child table.

I am getting this result...
SQL> select xmltype(empdata).extract('/DEPTDATA/alias/EMPNO/text()').getstringval() from test_data;

XMLTYPE(EMPDATA).EXTRACT('/DEPTDATA/ALIAS/EMPNO/TEXT()').GETSTRINGVAL()

---

778278397934
73697902787677887566
749976987654784479007521

SQL> select xmltype(empdata).extract('/DEPTDATA/alias/ENAME/text()').getstringval() from test_data;

XMLTYPE(EMPDATA).EXTRACT('/DEPTDATA/ALIAS/ENAME/TEXT()').GETSTRINGVAL()

---

CLARKKINGMILLER
SMITHFORDADAMSSCOTTJONES
ALLENBLAKEMARTINTURNERJAMESWARD But I need the data for each corresponding empno as row... DEPTNO DNAME EMPNO ENAME
10 Accounting 7782 CLARK

Can any one help how do I get this alias information for each deptno ...

Thanks for your time...
Nirup Received on Thu Dec 02 2004 - 13:35:58 CST