Oracle XML transformations

SELECT * from Pdpla_approve_proj;

Create or Replace procedure Proj_xml (st_id varchar2)

Is

Pro_rows pdpla_approve_proj%rowtype;--Row cursor

Tempsql varchar2 (MB): = ' select * from pdpla_approve_proj where st_id= ' | | st_id| | '; --

Type cur is ref cursor;--custom cursor

Proj_cur cur;--Custom cursor instance

Doc Xmldom. DOMDocument: = xmldom.newdomdocument;--Document Object

Doc_node xmldom. Domnode: = Xmldom.makenode (DOC);--Document node

Root_ele xmldom. DomElement: = xmldom.createelement (doc, ' Root ');--Document root element

St_pro_ele xmldom. DomElement;

Project_ele xmldom. DomElement;

St_pro_node xmldom. Domnode;

Root_node xmldom. Domnode;

Project_node xmldom. Domnode;

T_node xmldom. Domnode;

CNT number: = 1;

BUF varchar2 (1000);

Begin

Dbms_output.put_line (' Tempsql ' | | TEMPSQL);

Root_node: = Xmldom.appendchild (Doc_node,xmldom.makenode (Root_ele));

Xmldom.setattribute (Root_ele, ' id ', st_id);

Open Proj_cur for tempsql;--cursor

Loop

Fetch proj_cur into pro_rows;

Exit when Proj_cur%notfound;

Project_ele: = xmldom.createelement (Doc, ' project ');

Project_node: = Xmldom.appendchild (Root_node,xmldom.makenode (Project_ele));

St_pro_ele: = xmldom.createelement (Doc, ' st_pro_id ');

St_pro_node: = Xmldom.appendchild (Project_node,xmldom.makenode (St_pro_ele));

T_node: = Xmldom.appendchild (St_pro_node,xmldom.makenode (Xmldom.createtextnode (doc,pro_rows.st_project_id)));

St_pro_ele: = xmldom.createelement (Doc, ' St_info_type ');

St_pro_node: = Xmldom.appendchild (Project_node,xmldom.makenode (St_pro_ele));

T_node: = Xmldom.appendchild (St_pro_node,xmldom.makenode (Xmldom.createtextnode (Doc,pro_rows.st_info_type)));

CNT: =cnt+1;

End Loop;

Close proj_cur;

Xmldom.writetobuffer (DOC,BUF);

Xmldom.freedocument (DOC);

Dbms_output.put_line (' cnt ' | | CNT);

Dbms_output.put_line (' Doc ' | | BUF);

exception

When others then

Dbms_output.put_line (' Sqlerrm ' | | SQLERRM);

End

The

Create or replace procedure P_parse (ID in varchar2) is--creates an XML parser instance Xmlparser. Parserxmlpar Xmlparser. Parser: =xmlparser.newparser;--defines the DOM document Xdoc XMLDOM. domdocument;--defines the number of data child nodes Lenitme integer;--defines a list of nodes, storing data nodes Itemnodes xmldom. domnodelist;--defines the list of nodes and stores the data nodes ChildNodes XMLDOM. domnodelist;--defines a node that holds a single data node Itemnode xmldom. domnode;--defines attribute variables, and holds node properties Itemarrmap xmldom. domnamednodemap;--defines other variables, holds the value of the child node name varchar (m), value varchar (m), newid number (10), defines the CLOB variable, and holds the XML string Para_ Namexmlstr clob;begin for cur in (select T.xml_data para_name, t.id from XMLData t where t.id=id)--queries the data from the XMLData table, the ID is not the only Identification, an ID can detect multiple data (multiple data in XML), so use loop loop loops--Get the XML string Xmlstr:=cur.para_name in Para_name; Xmlpar: =xmlparser.newparser; --parsing XML strings in Xmlstr and depositing them in Xmlpar Xmlparser.parseclob (Xmlpar, XMLSTR); --Dump the data in the Xmlpar into the DOM document Xdoc:=xmlparser.getdocument (XMLPAR); Xmlparser.freeparser (Xmlpar); --Releasing the parser instance--Gets all data nodes Itemnodes:=xmldom.getelementsbytagname (xdoc, ' data '); --Get the number of data nodes Lenitme: =xmldom.getlength (itemnodes);--Iterate through all the data nodes for the I in 0..lenitme-1 Loop Begin-Get the first data node in the node list itemnode:=xmldom.item (itemnodes,i); --Gets all the child nodes Childnodes:=xmldom.getchildnodes (Itemnode) of the First Data node; --Gets the value of all child nodes Newid:=xmldom.getnodevalue (Xmldom.getfirstchild (Xmldom.item (childnodes,0))); Name:=xmldom.getnodevalue (Xmldom.getfirstchild (Xmldom.item (childnodes,1))); Value:=xmldom.getnodevalue (Xmldom.getfirstchild (Xmldom.item (childnodes,2))); --Storing the value newid,name,value of the child nodes to the Xmltest insert into xmltest values (newid,name,value); Commit End End Loop; End Loop; --whether DOM document Xmldom.freedocument (Xdoc); --Exception and error handling EXCEPTION when others THEN dbms_output. Put_Line (SQLERRM); end P_parse;