Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Querying XMLType data (CLOB storage)
Ralph Weires wrote:
<snip>
> Assume i stored the following two example documents in two rows of the
> table:
>
> ROW 1:
> ID: 1
> DATA: <document>
> <person>
> <name>Someone</name>
> </person>
> </document>
>
> ROW 2:
> ID: 2
> DATA: <document>
> <person>
> <name>Anyone</name>
> <person>
> <person>
> <name>Someone else</name>
> </person>
> </document>
<snip>
> The result i'd like to get i something like
>
> NAMES
> -------------------
> Someone
> Anyone
> Someone else
>
> with the different matching entries of a *single* row being separated in
> multiple rows of the returned result set.
<snip>
I don't believe there is a way to pivot a row into multiple rows using the basic XDB interfaces. Here's a pipeline table function I wrote that will split a cursor of XMLTYPE documents into a separate XMLTYPE document for each child element of each document (note: I didn't test this very thoroughly).
create or replace type ttab_xmltype is table of xmltype /
create or replace function xmltype_split(
in_refcur sys_refcursor) return ttab_xmltype pipelined is xml xmltype; doc dbms_xmldom.domdocument; root_element dbms_xmldom.domelement; root_node dbms_xmldom.domnode; child_node dbms_xmldom.domnode; child_clob clob; child_xml xmltype;
begin
dbms_lob.createtemporary(child_clob, cache => true);
loop
fetch in_refcur into xml; exit when in_refcur%notfound; -- get a dom for the xmltype so we can -- navigate through it. doc := dbms_xmldom.newdomdocument(xml); root_element := dbms_xmldom.getdocumentelement(doc); root_node := dbms_xmldom.makenode(root_element); -- now walk through each child and pivot -- that out as a separate xmltype child_node := dbms_xmldom.getfirstchild(root_node); loop exit when dbms_xmldom.isnull(child_node); -- convert the child node to an xmltype dbms_xmldom.writetoclob(child_node, child_clob); child_xml := xmltype(child_clob); -- pipe the child node out as it's own -- xmltype on it's own row pipe row(child_xml); child_node := dbms_xmldom.getnextsibling(child_node); end loop; dbms_xmldom.freedocument(doc); end loop; return;
end;
/
Then you could do something like ...
select *
from table(xmltype_split(cursor(
select sys_xmlagg(x.data.extract('/document/person/name')) name from xmltable x )))
<name>Someone</name> <name>Anyone</name> <name>Someone else</name>
3 rows selected.
Which, of course, would also allow ...
select x.column_value.extract('/name/text()') name from table(xmltype_split(cursor(
select sys_xmlagg(x.data.extract('/document/person/name')) name from xmltable x ))) x
NAME
3 rows selected.
I'd be interested to see if someone has a simpler solution (other than hard coded parsing and self joins, etc...).
Richard Kuhler Received on Wed Sep 03 2003 - 18:12:09 CDT