Database Software

Hi,

I have some dynamic XML generated by a query ( SELECT xmlelement(xmlagg etc. ) and some static XML stored in a clob column in a table which I need to insert into the dynamic content using PL/SQL and despite reading the documentation / forum's etc. i'm struggling to find how you do this. Any pointers would be appreciated.

In the PL/SQL I have both the dynamic and static content stored in XMLType variables, I need to add the static content after the first element from the dynamic content and then return the new XML string

Its on Oracle 10.2

The following PL/SQL block ...

DECLARE

l_xmlstring1 xmltype;
l_xmlstring2 xmltype;

BEGIN

SELECT xmltype(xml_string1), xmltype(xml_string2)
into l_xmlstring1, l_xmlstring2
from drop_me;

dbms_output.put_line ( 'l_xmlstring1=' || l_xmlstring1.getclobval() );
dbms_output.put_line ( 'l_xmlstring2=' || l_xmlstring2.getclobval() );

END;

gives me ...

l_xmlstring1=<MAIN><NAME>fred</NAME></MAIN>
l_xmlstring2=<EXTRA><NO_OF_RECS>1</NO_OF_RECS></EXTRA>

I want to insert all of l_xmlstring2 into l_xmlstring1 between the <MAIN><NAME> tags without knowing the names of the elements in l_xmlstring


Cheers,

Edited by: pscriv on 06-May-2009 01:20

Edited by: pscriv on 06-May-2009 02:32