• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 769
  • Last Modified:

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

How to retrieve data from xml code using oracle sql query.
0
AJITPADHY
Asked:
AJITPADHY
  • 4
  • 4
  • 3
3 Solutions
 
sdstuberCommented:
use EXTRACT or EXTACTVALUE with xpath


or XMLQUERY if you know FLWOR syntax
0
 
slightwv (䄆 Netminder) Commented:
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
 
AJITPADHYAuthor Commented:
please provide me step by step procedure & pdf documents for the entire process.
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
slightwv (䄆 Netminder) Commented:
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
 
AJITPADHYAuthor Commented:
retrieve objective id,group code from the attachment xml code using sql query.
xmlquery.txt
0
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:



SELECT EXTRACTVALUE(VALUE(o), '//ObjectiveEORow/ObjectiveId'),
       EXTRACTVALUE(VALUE(o), '//ObjectiveEORow/GroupCode')
  FROM yourtable,
       XMLTABLE('//ObjectiveEORow'
                PASSING xmltype(yourxmlstring)
               ) o
0
 
sdstuberCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
AJITPADHYAuthor Commented:
Thanks
0
 
sdstuberCommented:
why the B?

You know that's a penalty, right?  What else did you want?
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.

Join & Write a Comment

Featured Post

Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

  • 4
  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now