DECLARE
v_data_xml CLOB;
v_clob_upd_sql CLOB;
v_bool BOOLEAN := false;
LV_OUT_XML XMLTYPE;
lv_number_of_nodes number := 2426; -- Number of nodes in XML
BEGIN
-- Variable holding the data XML
v_data_xml := q'[<?xml version="1.0" encoding="UTF-8"?>
<myspace:datapacket
xmlns:html="http://www.w3.org/1999/xhtml"
xmlns:myspace="http://www.mywrb.com/test/myspace">
<myspace:data>
<myspace:sec>
]';
FOR I IN 1..lv_number_of_nodes
LOOP
dbms_lob.append(v_data_xml,'<myspace:row><myspace:pk'||i||'>'||i||'</myspace:pk'||i||'></myspace:row>');
END LOOP;
DBMS_LOb.APPEND(v_data_xml,'</myspace:sec></myspace:data></myspace:datapacket>');
-- XMLQUERY to update all the nodes in the data XML
V_CLOB_UPD_SQL := 'declare default element namespace "http://www.mywrb.com/test/myspace"; (: :) '
||' copy $tmp := $inxml modify (';
FOR i IN 1..lv_number_of_nodes
LOOP
IF v_bool THEN
dbms_lob.append(v_clob_upd_sql,',');
END IF;
DBMS_LOB.APPEND(v_clob_upd_sql,'(for $i in $tmp/datapacket/data/sec/row/pk'||I
||' return replace value of node $i with "'
||TO_CHAR(i+1)||'")');
V_BOOL := TRUE;
END LOOP;
DBMS_LOB.APPEND(V_CLOB_UPD_SQL,') return $tmp');
SELECT xmlquery( v_clob_upd_sql passing xmltype(v_data_xml) AS "inxml" returning content)
INTO lv_out_xml
FROM dual;
END;