Re: Querying XMLType data (CLOB storage)

From: Richard Kuhler <noone_at_nowhere.com>
Date: Wed, 03 Sep 2003 23:12:09 GMT
Message-ID: <dzu5b.13964$gt1.577174@twister.socal.rr.com>

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

---

Someone
Anyone
Someone else

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