xml response in pl/sql
with t as( select xmltype('< ROWSET>
< ROW>
< DEPTNO>10 < /DEPTNO>
< NAME>ACCOUNTING< /NAME>
< LOC>NEW YORK< /LOC>
< /ROW>
< ROW>
< DEPTNO>20< /DEPTNO>
< DNAME>RESEARCH< /DNAME>
< LOC>DALLAS< /LOC>
< /ROW>
< ROW>
< DEPTNO>30< /DEPTNO>
< DNAME>SALES</DNAME>
< LOC>CHICAGO < /LOC>
</ROW>
< ROW>
< DEPTNO>40< /DEPTNO>
< DNAME>OPERATIONS< /DNAME>
< LOC>BOSTON< /LOC>
< /ROW>
< /ROWSET>
') x from dual)
select extractvalue(t2.column_value,'ROW/DEPTNO') DEPTNO ,
extractvalue(t2.column_value,'ROW/DNAME') DNAME,
extractvalue(t2.column_value,'ROW/LOC') LOCATION
from t t, table(xmlsequence(t.x.extract('ROWSET/ROW'))) t2
Result:
DEPTNO DNAME LOCATION
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
But in my case the XML response is stored in a table in column of xmltype. In which the same response which is there at the top is present in the xmltype column. so i used it as follows:
with t as( select xml_doc x from xml_table where xml_name='EMPDETAILS' )
select extractvalue(t2.column_value,'ROW/DEPTNO') DEPTNO ,
开发者_运维问答 extractvalue(t2.column_value,'ROW/DNAME') DNAME,
extractvalue(t2.column_value,'ROW/LOC') LOCATION
from t t, table(xmlsequence(t.x.extract('ROWSET/ROW'))) t2
It is not returning any rows. for which the same response is used in both the queries.
Please let me know whether i need to change the way how i am using the second query.
Plz help...thanks in advance.
Unable to duplicate. See code sample below:
Create table
create table xml_table (
xml_name varchar2(100),
xml_doc xmltype
);
Insert data
insert into xml_table (xml_name, xml_doc) values (
'EMPDETAILS',
xmltype('
<ROWSET>
<ROW>
<DEPTNO>10 </DEPTNO>
<NAME>ACCOUNTING</NAME>
<LOC>NEW YORK</LOC>
</ROW>
<ROW>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
<LOC>DALLAS</LOC>
</ROW>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
<LOC>CHICAGO</LOC>
</ROW>
<ROW>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
</ROW>
</ROWSET>
'));
insert into xml_table (xml_name, xml_doc) values (
'OTHERDETAILS',
xmltype('
<ROWSET>
<ROW>
<ELEMENT>HELIUM</ELEMENT>
</ROW>
</ROWSET>')
);
Query using table data
with t as( select xml_doc x from xml_table where xml_name='EMPDETAILS' )
select extractvalue(t2.column_value,'ROW/DEPTNO') DEPTNO ,
extractvalue(t2.column_value,'ROW/DNAME') DNAME,
extractvalue(t2.column_value,'ROW/LOC') LOCATION
from t t, table(xmlsequence(t.x.extract('ROWSET/ROW'))) t2;
Query using XMLType instance
with t as( select xmltype('<ROWSET>
<ROW>
<DEPTNO>10 </DEPTNO>
<NAME>ACCOUNTING</NAME>
<LOC>NEW YORK</LOC>
</ROW>
<ROW>
<DEPTNO>20</DEPTNO>
<DNAME>RESEARCH</DNAME>
<LOC>DALLAS</LOC>
</ROW>
<ROW>
<DEPTNO>30</DEPTNO>
<DNAME>SALES</DNAME>
<LOC>CHICAGO </LOC>
</ROW>
<ROW>
<DEPTNO>40</DEPTNO>
<DNAME>OPERATIONS</DNAME>
<LOC>BOSTON</LOC>
</ROW>
</ROWSET>
') x from dual)
select extractvalue(t2.column_value,'ROW/DEPTNO') DEPTNO ,
extractvalue(t2.column_value,'ROW/DNAME') DNAME,
extractvalue(t2.column_value,'ROW/LOC') LOCATION
from t t, table(xmlsequence(t.x.extract('ROWSET/ROW'))) t2;
精彩评论