parsing xml using pl/sql which is stored as clob object in database table.

Below is my XML, I need to read this xml using PL/SQL which is stored in oracle database as a clob object.  I need to find out the attributes for the tags for example <workordertype>?

<?xml version="1.0" encoding="UTF-8" ?><WORKORDER ID="16" NAME="SONET Section_7 [Circuit Wizard] [Created]" RESULT="CREATE" ><WORKORDERTYPE ID="4" NAME="Circuit" ></WORKORDERTYPE ><EDITWIZARD ID="1003" NAME="Circuit" ></EDITWIZARD ><CIRCUIT ID="2" NAME="SONET Section_7" ALIAS1="" ALIAS2="" DESCRIPTION="" FULLNAME="" MARKEDFORDELETE="" NOTES="" OBJECTID="" PROVISIONSTATUS="1900000005" PSNAME="Forecasted" RELATIVENAME="" SUBSTATUS="" SUBTYPE="" ><CIRCUITTYPE ID="1900000011" NAME="SONET Section" ></CIRCUITTYPE ><CIRCUITDEF ID="1900000030" NAME="OC-3" ></CIRCUITDEF ></WORKORDER >
shan_saiAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
sathyagiriConnect With a Mentor Commented:
Oracle has a set of XML functions which you can use
refer to
http://www.psoug.org/reference/xml_functions.html
0
 
MohanKNairConnect With a Mentor Commented:
1) First option is to user insertxml function in dbms_xmlslave package. In the sample code l_clob holds the XML data. The tablename is passed to dbms_xmlsave.newContext. This table will have the parsed data.

Run the SQL script to create dbms_xmlsave package
$ORACLE_HOME/rdbms/admin/dbmsxml.sql


DECLARE
  l_clob  CLOB := '<xml string>';
  l_ctx   dbms_xmlsave.ctxType;
  l_rows  NUMBER;
BEGIN
l_ctx := dbms_xmlsave.newContext('MK_XML_TEST');
dbms_xmlsave.setrowtag(l_ctx,'ORDER');
l_rows := dbms_xmlsave.insertxml(l_ctx,l_clob);
dbms_xmlsave.closeContext(l_ctx);
DBMS_OUTPUT.PUT_LINE(l_rows || ' rows inserted...');
END;
/

2) Another option is to use DBMS_XMLGEN. It converts the results of a SQL query to a canonical XML format.

See this link for examples
http://www.psoug.org/reference/dbms_xmlgen.html

0
 
SujithConnect With a Mentor Data ArchitectCommented:
Shan,

The xml that you have given is not wellformed. The closing tag of <CIRCUIT> is missing.
And the tags doesnt have any values.

If you want to extract the values of any specific tags you can code something like the piece of code below.

declare
 l_clob clob;
 l_str varchar2(3000);
begin
 select val into l_clob
 from x1 where id = 1;

 l_str := xmltype(l_clob).extract('//WORKORDERTYPE').getStringval() ;

 dbms_output.put_line(l_str);
end;
/

As far as I know there are no methods to extract the attribute values of tags. You might have to parse the extracted string to get the attribute values, using combinations of substr and instrs.

Hope this helps, let me know.

Rgds,
Sujith.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.