Solved

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

Posted on 2006-07-12
5
6,890 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 84 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 83 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 83 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

Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
capture vmstat info and insert it into an oracle table 31 62
oracle date format checking 7 33
oracle forms question 22 48
error starting form builder in 11g 2 27
Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

726 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