Direct XMLTYPE manipulation with SQL/(XML) in Oracle

Question

I have temporary *XMLTYPE*s in my PL/SQL code. I would like to manipulate those temporary XMLs with SQL, like with UPDATE and UPDATEXML.

I am new to XML processing, and what I find in the Oracle documentation is to first make a table, put the XML file in it, and then I can manipulate it with SQL like this (pseudocode):

CREATE TABLE xml_table OF XMLTYPE;  
INSERT INTO xml_table values (l_xml);  
UPDATE xml_table SET = UPDATEXML(…) WHERE …;  

But I don’t want to make (temporary) tables to hold my XML files. I simply want to process them directly like this (pseudocode):

UPDATE l_xml SET = UPDATEXML(…) WHERE ….;  

So my questions are:

1. is direct manipulation of XMLTYPE with SQL even possible and
2. what is the preferred method for XML processing (values of attributes) if my functions are of the form process_function(xml XMLTYPE) RETURN XMLTYPE; (i.e. is my approach überhaupt sane?)

Answer 1

You can actually do this without using any temp tables in PL/SQL. A construction I've seen before is this one:

set serveroutput on;
declare
     myxml xmltype; 
begin

    myxml := xmltype('<root><tag1>somevalue</tag1></root>');

    select updatexml(myxml,'/root/tag1/text()','someOtherValue') 
    into myxml
    from dual;

    dbms_output.put_line(myxml.getClobVal());

end;

The approach you're taking seems the right one to me, although you might consider using the NOCOPY hint if you're passing around large quantities of XML.