Solved

How to retrieve data from xml code using oracle sql query.

Posted on 2011-02-23
11
765 Views
Last Modified: 2013-12-25
How to retrieve data from xml code using oracle sql query.
0
Comment
Question by:AJITPADHY
[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
  • 4
  • 4
  • 3
11 Comments
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 34962521
use EXTRACT or EXTACTVALUE with xpath


or XMLQUERY if you know FLWOR syntax
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34962563
I agree but wanted to add:

EXTRACT returns the 'raw' encoded XML values (ex/ Can't).
EXTRACTVALUE returns the decoded values  (ex/ Can't).

Sample data and expected results would help a lot.
0
 

Author Comment

by:AJITPADHY
ID: 34962867
please provide me step by step procedure & pdf documents for the entire process.
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34962900
Please provide your requirements.  Before we can provide the steps we need to know what you want.

I asked for some sample data and expected results.

Otherwise, it's the docs:
EXTRACT:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions060.htm#SQLRF00640

EXTRACTVALUE:
http://download.oracle.com/docs/cd/E11882_01/server.112/e17118/functions061.htm#SQLRF06173
0
 

Author Comment

by:AJITPADHY
ID: 34963225
retrieve objective id,group code from the attachment xml code using sql query.
xmlquery.txt
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34963416
Will you only ever have a single objectiveid and groupcode in the XML?

As long as the XML is in an Oracle XMLTYPE column try this:

select extract(col1,'//ObjectiveId/text()') objectiveid,
      extract(col1,'//GroupCode/text()') groupcode
from tab1;
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 34963418



SELECT EXTRACTVALUE(VALUE(o), '//ObjectiveEORow/ObjectiveId'),
       EXTRACTVALUE(VALUE(o), '//ObjectiveEORow/GroupCode')
  FROM yourtable,
       XMLTABLE('//ObjectiveEORow'
                PASSING xmltype(yourxmlstring)
               ) o
0
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 500 total points
ID: 34963447
if "yourxmlstring"  is already an XMLTYPE

then you can reference that column directly rather than using the XMLTYPE constructor
otherwise the query would be the same


PASSING yourxmltypecolumn

instead of

PASSING xmltype(yourxmlstring)

0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 34963475
Looks like the objective ids repeat and there is potential for the groupcodes to repeat as well.

What are you wanting to do with the repeating values once you retrieve them?  Maybe loop though the values and insert into a table?

If so, we would need to know what values go with what in the table.

Does each id and code for every repeating ObjectiveEORow become it's own row?
0
 

Author Closing Comment

by:AJITPADHY
ID: 35125651
Thanks
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 35127160
why the B?

You know that's a penalty, right?  What else did you want?
0

Featured Post

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Lotus Notes has been used since a very long time as an e-mail client and is very popular because of it's unmatched security. In this article we are going to learn about  RRV Bucket corruption and understand various methods to Fix "RRV Bucket Corrupt…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to recover a database from a user managed backup

624 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