Oracle uses the PL/SQL programming languages to not only give you the ability to write SQL but also to use the language to manipulate most objects within Oracle. The issue that came up was that there were duplicate tags off an XML node that where causing an error in the view we were generating. The view consists of an XMLTABLE declaration that lines up the columns to the Node of an xml file. There is an issue with Node having duplicate tags with the same naming convention. The idea behind this PL/SQL function was to figure out where the tag declaration was located and modify it. I found that the tag declaration for the view was stored in CLOB field. This field is short for character large object that can handle up to 4G of data. So what this function does is read in the CLOB column into a PL/SQL function and converts it to XMLTYPE data. The DBMS_XMLDOM API allows for use of its built-in functions to access XMLTYPE.
Create or Replace FUNCTION REMOVEDUPLICATETAGS ( ACSTRING IN CLOB ) RETURN CLOB AS ACSTRING_XML XMLTYPE; ACSTRING_OUT_CLOB CLOB; ACSTRING_OUT_CLOB2 CLOB; DOC DBMS_XMLDOM.DOMDOCUMENT; NDOC DBMS_XMLDOM.DOMNODE; BUF CLOB; DOCELEM DBMS_XMLDOM.DOMELEMENT; XMLELEM DBMS_XMLDOM.DOMELEMENT; NODE DBMS_XMLDOM.DOMNODE; NODELIST DBMS_XMLDOM.DOMNODELIST; LEN NUMBER; Standard_NUM NUMBER; Coord_NUM NUMBER; N DBMS_XMLDOM.DOMNODE; TESTR VARCHAR2(2000); NODEN VARCHAR2(2000); T DBMS_XMLDOM.DOMNODE; CURNODE DBMS_XMLDOM.DOMNODE; BEGIN --DBMS_OUTPUT.put_line('*****auto = N **'); ACSTRING_XML := XMLTYPE(''||ACSTRING||''); DOC := DBMS_XMLDOM.NEWDOMDOCUMENT(ACSTRING_XML); NDOC := DBMS_XMLDOM.MAKENODE(DOC); DBMS_LOB.CreateTemporary(BUF, TRUE); DBMS_XMLDOM.MAKENODE(xmlElem)); DOCELEM := DBMS_XMLDOM.GETDOCUMENTELEMENT(DOC); NODELIST := DBMS_XMLDOM.GETELEMENTSBYTAGNAME(DOC, '*'); LEN := DBMS_XMLDOM.GETLENGTH(NODELIST); STANDARD_NUM :=0; COORD_NUM:=0; FOR I IN 0 .. LEN - 1 LOOP N := DBMS_XMLDOM.ITEM(NODELIST, I); NODEN := DBMS_XMLDOM.GETNODENAME(N); DBMS_OUTPUT.PUT_LINE (NODEN ||' '|| I ); IF NODEN = 'StandardizedStateProv' AND STANDARD_NUM = 1 THEN T := DBMS_XMLDOM.REMOVECHILD(NDOC,N); END IF; IF noden = 'StandardizedStateProv' and STANDARD_NUM = 0 THEN STANDARD_NUM := 1; END IF; IF NODEN = 'Coordinates' AND COORD_NUM = 1 THEN T := DBMS_XMLDOM.REMOVECHILD(NDOC,N); DBMS_OUTPUT.PUT_LINE ('Remove' || ' ' || I); END IF; IF noden = 'Coordinates' and COORD_NUM = 0 THEN COORD_NUM := 1; END IF; TESTR := DBMS_XMLDOM.GETNODENAME(N) || ' ' || DBMS_XMLDOM.GETNODEVALUE(DBMS_XMLDOM.GETFIRSTCHILD(N)); --DBMS_OUTPUT.PUT_LINE (TESTR); END LOOP; DBMS_XMLDOM.FREEDOCUMENT(DOC); <ACSTRING_OUT_CLOB := ACSTRING_XML.GETCLOBVAL(); RETURN ACSTRING_OUT_CLOB; END REMOVEDUPLICATETAGS;