Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2006-07-12
5
Medium Priority
?
6,903 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article covers the basics of the Sass, which is a CSS extension language. You will learn about variables, mixins, and nesting.
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

618 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