?
Solved

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

Posted on 2006-07-12
5
Medium Priority
?
6,900 Views
Last Modified: 2013-12-12
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 >
0
Comment
Question by:shan_sai
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 14

Accepted Solution

by:
sathyagiri earned 336 total points
ID: 17094881
Oracle has a set of XML functions which you can use
refer to
http://www.psoug.org/reference/xml_functions.html
0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 332 total points
ID: 17098135
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
 
LVL 27

Assisted Solution

by:sujith80
sujith80 earned 332 total points
ID: 17098377
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
HTML5 has deprecated a few of the older ways of showing media as well as offering up a new way to create games and animations. Audio, video, and canvas are just a few of the adjustments made between XHTML and HTML5. As we learned in our last micr…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question